Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text to columns (Excel 2002)

    I know how to use the text to columns data feature but I can't remember how to use it when the text is delimited by spaces but, in some cases, a cell contains 2 delimiters, e.g., New York City and I want the text New York to be placed in one single cell and City in another cell. Help me please.

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

    Re: Text to columns (Excel 2002)

    You can specify fixed width fields, and drag the first column break off the record in the dialog box. However, that only works if all of the names break at the same place. It would not work if you had New York NY and Los Angeles CA. If you have that situation, you would probably have to write a macro to do the job. Something like this should do the job:

    <pre>Public Sub SplitCells()
    Dim iLoc As Integer
    Dim oCell As Range
    Application.ScreenUpdating = False
    For Each oCell In Selection
    iLoc = InStrRev(oCell.Value, " ")
    oCell.Offset(0, 1).Value = Trim(Right(oCell.Value, Len(oCell.Value) - iLoc))
    oCell.Value = Trim(Left(oCell.Value, iLoc))
    Next oCell
    Application.ScreenUpdating = True
    End Sub
    </pre>


    Select the cells you want split and run the macro.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text to columns (Excel 2002)

    You guessed my situation correctly - school names such as Randall Pepper and H. S. Truman - the macro worked great - thanks again for all your help.

Posting Permissions

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