Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Separating text in a column (XP)

    Hi,

    I have a column of names that includes the middle initial and the last name without a comma, space etc. inbetween, looking similar to this FMartin - where "F" is the middle initial and Martin is the last name. I need to separate the last name from the middle initial into two separate columns. The excel wizard won't do it (that I can tell) without a comma, space, tab etc. in place. Is there a formula that I can use to separate them.

    Thanks,
    Leesha

  2. #2
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating text in a column (XP)

    Suggest use a formula like this to create a space between the F and the Martin, your example (with FMartin in A1)
    =LEFT(A1,1)&" "&RIGHT(A1,(LEN(A1)-1))
    The character between " and the " is a space.
    Then do the text to columns trick to separate into columns (may need to paste special | values the result of the formula.)

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Separating text in a column (XP)

    That worked beautifully!! Thanks so much. I'll have to save that one for future use!

    Leesha

  4. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating text in a column (XP)

    As you no doubt discovered, Leesha, you do need to paste speceial | values the result of the formula.
    For your homework < grin >, what about situations where there are lots of 'middle' initials - QJFotherington-Farqharson, for example?
    I suspect it's to do with counting upper case characters and proceeding from there.
    Happy days...

Posting Permissions

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