Hidden Apostrophe

[PWD]

Good afternoon, we have data that comes from a 3rd party, attached, and there appears to be hidden apostrophes in the output. I just did a macro record to try to do a Text-to-Columns but, as you can see, the result is patchy. Can you suggest how to get rid of them before I run this simple code?

Many thanks.

Sub TextToColumns()
'
' TextToColumns Macro
'

Dim Counter As Integer
Dim MyString As String
Dim WS As Worksheet

    For Each WS In ActiveWorkbook.Worksheets
    
    With WS
    WSName = .Name
        .Activate
    End With
 
MyString = "IJK" 'define string
 
    For Counter = 1 To Len(MyString)
    
        x = Mid(MyString, Counter, 1)
        
        Columns(x & ":" & x).Select
        Selection.TextToColumns Destination:=Range(x & "1"), DataType:=xlFixedWidth, _
            FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
    
    Next Counter
  
Next WS

End Sub

Many thanks,
D€$

 

[combo]

You may have a problem with proper conversion of date string to date. Input data has DMR order that is not recognised as date if first part is greater than 12. In text to columns set output column as date and select DMY order in input.

combo

 

[PWD]

Thanks, I just re-recorded it and have changed it to

FieldInfo:=Array(0, 4)

and that seems to have set these output columns, excepting the header row, to Date. Looks OK to me now!

Many thanks,
D€$