Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    36

    Unanswered: Help needed separating data

    Is there some coding that will help me separate some data I'm working on.

    Within a field titled 'Customer Surname' there is a reference of either AM or SM after the surname.

    I.e. Smith SM
    Jones AM

    How do I separate the data so that I have the surname in one field and the reference in the next?

    Thanks

  2. #2
    Join Date
    Mar 2004
    Location
    Minnesota, USA, Earth
    Posts
    65
    Hmmmm...

    If you're only dealing with "AM" and "SM" I would suggest to get the surname.

    If(Right(A1,2)="AM",LEFT(A1,FIND("AM",A1)-1),if(right(A1,2)="SM",left(a1,FIND("SM",A1)-1)))

    To get the reference of "AM" or "SM" and if it's always at the end, then =Right(A1,2)

  3. #3
    Join Date
    Mar 2004
    Posts
    36
    Excellent, thanks for your help!

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Or if that is exactly how all the cells are, you can use Data > Text to Column, and choose "space" as the separator.
    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

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by shades
    Or if that is exactly how all the cells are, you can use Data > Text to Column, and choose "space" as the separator.
    Hey there Shades,

    Wow, I just tried the Text to Column you suggested with the Comma delimiter and man was that awesome. Great tip.

    BUD

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    Indeed, it saves considerable time, and avoids unnecessary formulas!
    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

Posting Permissions

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