Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Text to Columns (Excel 2002)

    Hi,
    I'm using a macro (see below) to change the data that is in ONE cell into THREE cells by using the "Data", "Text to Columns" function. An example of the data located in one cell is the following:

    CONTRACT-JAN08-12

    Using the text to columns function the word "CONTRACT" is placed in one cell, then the date of "JAN08" is placed in a second cell, and the the "12" is in a thrid cell. My problem is a format problem... I need the "JAN08" to remain as text... instead it shows up as a date format "8-Jan". I recorded the macro below for the function of text to columns... is there a way to add the text function inside the macro so the "JAN08" doesn't revert to a date format?
    Thanks!!
    Lana

    Sub Macro6()
    Columns("BD:BD").Select
    Selection.Copy
    Columns("BG:BG").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("BG1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
    TrailingMinusNumbers:=True
    End Sub

  2. #2
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Text to Columns (Excel 2002)

    Nevermind... I finally figured it out... I changed the Array(2,1) to Array(2,2) and it changed it to be text!!
    Lana

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Text to Columns (Excel 2002)

    Thank You Lana
    You just enlightened me.
    Regards
    Don

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text to Columns (Excel 2002)

    You can also use symbolic constants instead of 1, 2 etc. - look up TextToColumns in the Excel VBA help and expand xlColumnDataType. You can also look up xlColumnDataType in the Object Browser (press F2 in the Visual Basic Editor to activate the Object Browser).

    Instead of Array(2, 1) you can use Array(2, xlGeneralFormat)
    Instead of Array(2, 2) you can use Array(2, xlTextFormat)

    This will make the code longer, but easier to understand.

  5. #5
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text to Columns (Excel 2002)

    I am also trying to do a text to columns on data that is dates without the "/" separator. I can convert all of the data to text yet when I do the text to columns, i end up with a number and not a date format.
    This is the code I have :

    Sub DateSeparators()
    Dim ThisCell As Range
    Application.ScreenUpdating = False
    'Make sure format is text
    Selection.NumberFormat = "@"
    For Each ThisCell In Selection
    'Strip the leading apostrophe, if any
    If Left(ThisCell, 1) = “’” Then
    ThisCell = Mid(ThisCell, 2, 99)
    End If
    'It’s a 9 digit cell
    If Len(ThisCell) <= 9 Then
    ThisCell = “’” & Right(“000000000” & ThisCell, 9)
    End If
    Next ThisCell
    Selection.TextToColumns Destination:=ActiveCell.Offset(0, 1), DataType:=xlFixedWidth, _
    FieldInfo:=Array(0, 6), TrailingMinusNumbers:=True
    Application.ScreenUpdating = True
    End Sub

    How do I get date in the MM/DD/YYYY format.? I thought the FieldInfo=Array(0,6) would do it
    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text to Columns (Excel 2002)

    The TextToColumn method cannot handle dates without a separator. You'll have to process the column yourself, parsing the values into a month, day and year part and then assembling a date from them.

  7. #7
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text to Columns (Excel 2002)

    Thanks Hans

Posting Permissions

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