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.

13 Comments

  1. 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!

  2. 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

  3. 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!

  4. 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>