Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    May 2007
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Split text in to columns (2003)

    I have attached an xls with lots of information in one cell. I would like to split up all the information into five columns. I think you can use a wizard of some sort. Please can you help thanks.
    Attached Files Attached Files

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

    Re: Split text in to columns (2003)

    Select the cells with data.
    Select Data | Text to Columns to start the Wizard.
    In the first step, select Delimited, then click Next.
    In the second step, specify Comma as delimiter, then click Finish.
    The data will be split into columns A to D.
    You'll want to split the data in column D, so select the cells in column D.
    Select Data | Text to Columns again.
    This time, select Fixed Width, then click Next.
    Read the instructions on how to indicate where you want to split the data. You probably want a split at position 21.
    Click Next.
    Select the second column and specify that you want to convert it as Text.
    Click Finish.

  3. #3
    New Lounger
    Join Date
    May 2007
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split text in to columns (2003)

    Hi Hans that worked for the first columns but need one more split, but it does not have commas to separate, there is only space separating the text. If you could help that would be great. I have attached an update xls.

    Thanks for help.
    Attached Files Attached Files

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

    Re: Split text in to columns (2003)

    Please read my previous reply again - I described the extra steps you have to take.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split text in to columns (2003)

    The code below should split column D as you specified. Make sure to use it on a copy of your workbook so you can recover if it does not work like you expect. Also, the macro will store values like 0000000531 as a numeric value after the split and it will display as 531. If you want these values stored as text with the leading zeros, then format column E as text BEFORE running the macro.

    <code>
    Public Sub SplitD()
    Dim I As Long, IMax As Long
    IMax = Range("D65536").End(xlUp).Row - 1
    For I = 0 To IMax
    With Range("D1").Offset(I, 0)
    .Offset(0, 1).Value = Trim(Right(.Value, Len(.Value) - InStr(.Value, " ")))
    .Value = Trim(Left(.Value, InStr(.Value, " ")))
    End With
    Next I
    End Sub
    </code>
    Legare Coleman

Posting Permissions

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