Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Hidden Apostrophe

Hidden Apostrophe

Hidden Apostrophe

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
    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,

RE: Hidden Apostrophe

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.


RE: Hidden Apostrophe

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,

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close