Results 1 to 7 of 7
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: Splitting 1 Column Of Data Into 2

    I have a spreadsheet which has a column of names eg John Smith, Samuel Saunders etc. I need to split the names over two columns so I end up with a first name column and a last Name column. Can you help me?

  2. #2
    Join Date
    Feb 2007
    Posts
    348
    Cell A1= "John Smith"
    Cell B1 = "=Search( ,A1,1)" *Before the first comma is a space*
    Cell C1 = "=LEFT(A1,B1)"
    Cell D1 = "=RIGHT(A1,B1-1)"

  3. #3
    Join Date
    May 2002
    Posts
    157
    Thanks heaps for your answer.
    I had some problems getting Cell D1 to work
    In the end and thanks to your example, I was able to try a few things out and came up with the following (which works):

    Cell A1 insert: John Smith
    "Cell B1 insert: =LEFT(A1,SEARCH(" ",A1)-1) *Between the quotes is a space*
    *-1 gets rid of the space after the first name*"
    Cell C1 insert: =RIGHT(A1,LEN(A1)-SEARCH(" ",A1))

    Thanks once again for your help

  4. #4
    Join Date
    Feb 2007
    Posts
    348
    oh, nice improvement. Glad to help.

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Data->Text to columns
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Feb 2007
    Posts
    348
    Well played
    I learned something today, woohoo

  7. #7
    Join Date
    May 2002
    Posts
    157
    Well done, I have not seen that before. Shows that I need to investigate my menus more.

    Thanks heaps

Posting Permissions

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