Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    London
    Posts
    76

    Unanswered: Seperating text in a column to new columns

    Hi,

    Is it possible and if so, how would I seperate text in a column to new columns using spaces, for example I have a field 'Contact Name' with names in the following format:
    Mr John Barnes
    Mr Kenny Daglish

    I want to seperate these names into separte columns i.e.
    Salutation First Name Last Name
    Mr John Barnes
    Mr Kenny Daglish

    Any help would be great, thanks!

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Data -> Text to Columns (Make sure there are blank columns behind the inital column)
    then in the Wizard choose
    Delimited
    Next page choose other and in the small box put a space,
    then finish

    HTH

    Dave

    I take it your a liverpool fan then?

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    Text-to-Column works fine. You can also use formulas:

    Assume your cell is A2,

    Title, put in cell B2:

    =LEFT(A2,FIND(" ",A2)-1)

    First Name, put in C2:

    =IF(ISERR(MID(A2,FIND(" ",A2)+1,IF(ISERR(FIND(" ",A2,FIND(" ",A2)+1)),FIND(" ",A2),FIND(" ",A2,FIND(" ",A2)+1))-FIND(" ",A2)-1)),"",MID(A2,FIND(" ",A2)+1,IF(ISERR(FIND(" ",A2,FIND(" ",A2)+1)),FIND(" ",A2),FIND(" ",A2,FIND(" ",A2)+1))-FIND(" ",A2)-1))

    Last Name, put in D2:

    =RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-
    LEN(SUBSTITUTE(A2," ","")))))
    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

  4. #4
    Join Date
    Feb 2004
    Location
    London
    Posts
    76

    Cheers!

    Thanks all...such a long way round to do this...thought Excel would have this function in a similar format!!! and yes I am a Liverpool fan

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    I suppose it could of been newcastle or celtic as well,
    If you want to you could write a function yourself to do this using VBA

Posting Permissions

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