How to extract words before and after a comma

Today, I encountered a situation where I had to separate the full names of our customers into the first and last names. The names are comma separated, so I was looking for an Excel formula that can help me extract the last name before the comma and the first name after the comma.

For example, you have the following text in a cell “A1″ and you’d like to extract the text before the comma (the last name):

“Doe, John”

To extract the last name before the comma “Doe” from A1, use one of the following formula:

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

The result: Doe

To extract the first name after the comma from A1, use one of the following formula:

=MID(A1,FIND(",",A1)+2,256)

The result: John

Not working for you?

Remember to replace A1 with the cell that contains the string of words you want to parse out.

This entry was posted in Extracting words. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

23 Comments

  1. taylor
    Posted October 18, 2014 at 12:42 am | Permalink

    How would I extract the last name from a cell that looks like this: 2 BAZZARI, KHALDOUN F.

    Also, how would I create a formula that also extracts the last name from a cell that has entered double digits like: 21 BAZZARI, KHALDOUN

    • karen
      Posted November 5, 2014 at 2:06 pm | Permalink

      Hi Taylor, I understand that you have a unique ID before the last name and you want to extract the last name that sits between the unique ID and the comma. Is that correct? If that’s the case, try the formula below:

      =MID(LEFT(A1,(FIND(“,”,A1,1)-1)),FIND(” “,LEFT(A1,(FIND(“,”,A1,1)-1)))+1,256)

      Hope this helps!

  2. APRIL
    Posted October 3, 2014 at 10:39 am | Permalink

    THIS WORKED FOR ME! JUST HAD TO CHANGE THE COMMA TO A SEMICOLON.

    • karen
      Posted November 5, 2014 at 1:58 pm | Permalink

      Yes, you can definitely easily change the comma to a semi-colon or any other character :)

  3. ASIF
    Posted June 13, 2014 at 10:10 am | Permalink

    works perfect for me, thanks alot

    • karen
      Posted August 8, 2014 at 9:54 am | Permalink

      Glad it worked for you, Asif!

  4. Lourdes
    Posted June 2, 2014 at 2:58 pm | Permalink

    Awesome! Thank you very much!

  5. Khwaja Mazharuddin
    Posted May 8, 2014 at 4:54 pm | Permalink

    Hey…

    Thanks a lot …

    I was searching for this formula for long time. This saves a lot of time and embarrassment while working on Excel.

    Thanks,
    Khwaja

  6. Andrew
    Posted May 6, 2014 at 5:31 pm | Permalink

    Thanks for sharing this! It just saved me literally HOURS of manual data entry between excel and access.

  7. Kory
    Posted March 29, 2014 at 4:17 pm | Permalink

    This was very helpful….it worked perfectly. In my case, I wanted everything before the “@”. I simply replaced the comma value “,” with this value “@”. Perfecto. Thanks soooooo much.

    • karen
      Posted April 7, 2014 at 4:43 pm | Permalink

      That’s right Kory! You can easily replace the comma with any other symbol. Glad you figured out how to easily get that done!

  8. Elaine
    Posted January 19, 2014 at 7:43 am | Permalink

    Thanks for these, it helps me a lot and saved time as well.
    But same as shirleyshirley here, i want to understand the formula so it will be easy for me to remember, if i know the logic of the formula then i will not forget it. I cannot get the logic for 1 after A1, and what is -1 for? Also in the second formula, why it is MID, i thought it would be RIGHT? What is +2 for also? why there is no ,1 or ,2 after A1?

    Thanks a lot again.

  9. Lucy
    Posted November 20, 2013 at 12:14 pm | Permalink

    Hi Karen,
    My situation is one step further, where I have a cell which contains Surname, Firstname pseud: xxxxxx. I only want “Surname, Firstname”, and I don’t want anything that comes after that. However the number of characters in the first name will therefore vary, so how do I change the 256 character search to instead search up to the next ” ” (space) instead?
    With kindest thanks for you help so far!
    Lucy

  10. Danielle Williams
    Posted November 4, 2013 at 5:07 pm | Permalink

    I love this! It works perfectly and saved me hours of time

    • karen
      Posted November 20, 2013 at 7:38 am | Permalink

      Hi Danielle! So happy that I was able to help save you time :)

      • Sammer
        Posted February 4, 2014 at 12:33 am | Permalink

        thnksssss for the help karen

        • karen
          Posted February 4, 2014 at 12:37 am | Permalink

          You are very welcome, Sammer!

  11. Ct2k 12830
    Posted December 19, 2012 at 7:11 pm | Permalink

    thank you for this quick tip – it worked great

    • karen
      Posted January 10, 2013 at 6:27 pm | Permalink

      You’re welcome! Glad I was able to help you save time :)

      • shirleyshirley
        Posted July 8, 2013 at 5:27 am | Permalink

        can you explain what those codes mean? :-) thank you

        • shirleyshirley
          Posted July 8, 2013 at 5:35 am | Permalink

          i.e. can you tell me what “,” means? why did you place 3 values separated by comma within ()? –>(x,x,x)=(“,”,A1). how does it work? what’s the 256 for? thank you! :-)

          • karen
            Posted October 17, 2013 at 1:40 pm | Permalink

            Hi Shirley! The “,” just means that the formula should find the comma in the string. The “256” indicates the number of characters that you wish to extract after the comma. You can put any number here, as long as it’s high enough to cover the number of characters after the comma. The higher the number, the better :)

        • karen
          Posted October 17, 2013 at 1:35 pm | Permalink

          Hi Shirley! You can use these functions if you encounter a situation where you have to separate comma-separated strings (for example, if you want to separate the last name from the first name and there’s a comma in between the two).

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>