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.

34 Comments

  1. John
    Posted January 4, 2015 at 12:44 pm | Permalink

    Hi! Thanks for this formula. I do have a question about using a similar formula. Maybe someone can help me out here. I have a list of names which is separated by comma’s. I want to extract ONLY the last name in the list. To identify my problem I use a list with the same last names in the list.

    Column A (each line represents a different row, A1:A3)
    McPherson DB, Bennett J.
    Relay RM, Bennett J.
    Worth TH, Bennett J.

    I have tried using the following formula in Column B to extract the last name only:
    RIGHT(A1;(FIND(“,”;A1)-1))
    RIGHT(A2;(FIND(“,”;A2)-1))
    RIGHT(A3;(FIND(“,”;A3)-1))

    This is what the formula returns in the 3 different rows:
    ennett J.
    , Bennett J.
    Bennett J.

    The problem here is that in the first row it left out the ‘B’. In the second row, also the ‘, ‘ are returned. Only row 3 returns what I want and was expecting to return.

    Can someone explain me why using this formula gives me this inconsistency?

    Thanks!

    • karen
      Posted January 22, 2015 at 5:12 pm | Permalink

      Hi John, if you’re looking to extract the string AFTER the comma, you can use this formula: =MID(A1,FIND(",",A1)+2,256)

  2. andre
    Posted November 25, 2014 at 7:58 am | Permalink

    and if on top of the formula, I want to put an “if” condition to return the value of the cell if it doesn’t find any comma, how would that be?

    thanks in advance
    Andre

    • karen
      Posted January 22, 2015 at 5:02 pm | Permalink

      Hi Andre! That’s easy! You can just use this formula: IF(ISNUMBER(SEARCH(",",A1)),LEFT(A1,(FIND(",",A1,1)-1)),A1) (if you want to extract the text BEFORE the comma) or IF(ISNUMBER(SEARCH(",",A1)),MID(A1,FIND(",",A1)+2,256),A1) (if you want to extract the text AFTER the comma).

  3. carmen
    Posted November 19, 2014 at 2:49 am | Permalink

    hi need help,

    how do i extract the word BLUE_ELEPHANT from the string below :

    ANIMAL::BLUE_ELEPHANT_BABY_MAX_0000

    thanks alot

    • karen
      Posted January 22, 2015 at 5:03 pm | Permalink

      Hi Carmen, are you always looking to extract the first two words after the colon marks?

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

      • nikki
        Posted December 5, 2014 at 2:44 am | Permalink

        I need this formula, but when i enter it it says I have too many arguments

        • karen
          Posted January 22, 2015 at 4:57 pm | Permalink

          Hi Nikki, there seems to be something wrong with your formula. Do you mind copying and pasting it here, so I can take a look and give you feedback on what could be wrong?

  5. 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 :)

  6. Timothy
    Posted June 27, 2014 at 10:33 pm | Permalink

    this doesnt work at all, it says “Find” is not defined, and does not read it. also can you do right function until comma with cells(#,#) instead of using a range?

    • karen
      Posted January 22, 2015 at 5:24 pm | Permalink

      Hi Timothy, I am sorry to hear that the formula did not work for you! Feel free to add a link to the file if you want me to help debug your formula! I’ve used this formula a number of times and it has worked for me every time!

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

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

    Awesome! Thank you very much!

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

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

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

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

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

    • Funny Man
      Posted February 24, 2015 at 5:01 am | Permalink

      Hi Lucy,

      Hope you may have got the solution. If not, following is one solution!

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

      Assuming that A1 holds “Funny Man From India”

      The formula reads for first blank space and adds one to that. Then search for the next one!

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

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