Results 1 to 3 of 3

Thread: Text to Column

  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there a way to programatically set the "Column Data Format" to text for specific columns? I have used the "record macro" feature to capture the keystrokes but it does not provide any reference to the "Column Data Formats".

    Regards,
    John
    Attached Images Attached Images
    • File Type: png x.png (18.3 KB, 1 views)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Here is the instruction from a recorded macro:

    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
    FieldInfo:=Array(Array(1, 3), Array(2, 1), Array(3, 2)), _
    TrailingMinusNumbers:=True

    The part in bold specifies the column format.
    Array(1, 3) means that the 1st column uses the 3rd format, i.e. date as MDY.
    Array(2, 1) means that the 2nd column uses the 1st format, i.e. general.
    Array(3, 2) means that the 3rd column uses the 2nd format, i.e. text.

    Although the macro recorder doesn't capture them, you can use symbolic constants:

    FieldInfo:=Array(Array(1, xlMDYFormat), Array(2, xlGeneralFormat), Array(3, xlTextFormat))

    See the vBA help or TextToColumns Method for more info.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans,

    Thank you for pointing it out to me as I overlooked it.

    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •