Extract text in Excel the Easy way

Here are 5 quick formulas for you to extract text from cells in Excel.

Have you ever had a problem where you need to get a specific word from a string in another cell? This type of work is referred to as Data Manipulation, and is a very important skills to learn for anyone using MS Excel. (This can help a lot when creating Pivot Tables!)

Extract text before first space (or comma or other character)

=LEFT(A1,(FIND(" ",A1,1)-1))
Code language: JavaScript (javascript)

Note: if you need a comma or some other character instead of a space, replace the ” “ part with the character you need wrapped in double quotes.

Extract text before first comma
=LEFT(A1,(FIND(“,”,A1,1)-1))

Extract text before first ???
=LEFT(A1,(FIND(“???”,A1,1)-1))

This formula will extract the all the text from cell A1 that occurs before the first space. A great example of this is when you need to extract the first names from a column of full names.

---

---

---

---

Extract text after first space (or comma or other character)

=MID(A2,FIND(" ",A2)+1,LEN(A2))
Code language: JavaScript (javascript)

Note: if you need a comma or some other character instead of a space, replace the ” “ part with the character you need wrapped in double quotes.

Extract text after first comma
=MID(A2,FIND(“,”,A2)+1,LEN(A2))

Extract text after first ???
=MID(A2,FIND(“???”,A2)+1,LEN(A2))

This formula will extract the ALL the text from cell A1 that occurs after the first space. A great example of this is when you need to extract the last names from a column of full names.

---

---

---

Extract text before second space (or comma or other character)

=LEFT(A2, SEARCH(" ", A2, SEARCH(" ", A2) + 1))
Code language: JavaScript (javascript)

That complex formula above will get ALL the text from cell A2 that occurs before the second space.

If you need to get a specific word from a string with commas instead, then just replace ” “ with “,”. Like this formula below:

=LEFT(A2, SEARCH(",", A2, SEARCH(",", A2) + 1))
Code language: JavaScript (javascript)

Note: the code above will include the trailing comma. If you need to strip off the last comma, then use this instead:

=LEFT(A2, SEARCH(",", A2, SEARCH(",", A2) + 1)-1)
Code language: JavaScript (javascript)

Pro Tip: Once you have the formula entered in properly, you can very quickly apply it to the entire column: (scroll up to the previous sections for “How To” screenshots)

• Move cursor to bottom right hand corner of cell (with formula already entered)
• When you see the little + symbol, double click

Extract text after second space (or comma or other character)

=RIGHT(A2, LEN(A2) - (SEARCH(" ", A2, SEARCH(" ", A2) + 1)))
Code language: JavaScript (javascript)

The code above will extract ALL text after the second space from cell A2.

If you need to get a specific word from a string with commas instead, then just replace ” ” with “,”. Like this formula below:

=RIGHT(A2, LEN(A2) - (SEARCH(",", A2, SEARCH(",", A2) + 1)))
Code language: JavaScript (javascript)

Pro Tip: Once you have the formula entered in properly, you can very quickly apply it to the entire column: (scroll up to the previous sections for “How To” screenshots)

• Move cursor to bottom right hand corner of cell (with formula already entered)
• When you see the little + symbol, double click

Extract text between two spaces (or two commas or two characters)

=MID(A2,FIND(" ",A2)+1,FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A2))
Code language: JavaScript (javascript)

The code above will extract the string in A2 that is between two spaces. This is ideal when you have a Full Name column (firstName MiddleName lastName) and need to extract only the middle name.

If you need to extract a string from a comma delimited cell, then you could use this formula instead:

=MID(A2,FIND(",",A2)+1,FIND(",",A2,FIND(",",A2)+1)-FIND(",",A2)-1)
Code language: JavaScript (javascript)

Pro Tip: Once you have the formula entered in properly, you can very quickly apply it to the entire column: (scroll up to the previous sections for “How To” screenshots)

• Move cursor to bottom right hand corner of cell (with formula already entered)
• When you see the little + symbol, double click

---

Anything I missed?

Leave a comment below if there’s anything you wanted to see but didn’t. If you found this article useful, bookmark it for future reference. (And don’t forget to share it with your friends, social media, etc.)

If you want more useful Excel tips coming to your inbox (I promise not to spam you!) then you should signup for the free newsletter!

[sibwp_form id=2]