How to Extract Text from a Cell in Excel (5 Easy Methods)

When working with text strings in Excel, you may often need to extract only a part of the text from a cell. Whether you want to get the first name, last name, middle initial or extract text before or after a specific character, Excel provides easy ways to do it.

In this comprehensive guide, you will learn 5 simple methods to extract text from cells in Excel:

• Using the LEFT, RIGHT, and MID functions
• Extracting text before or after a character
• Getting text from the middle of a string
• Extracting text with additional functions like TRIM and SEARCH
• Using Text to Columns to Split Text

So let’s get started extracting text from cells in Excel!

1. Using LEFT, RIGHT and MID functions

Excel has three text functions that make extracting parts of a text string easy.

LEFT function

The LEFT function extracts a specified number of characters from the left of a text string.

The syntax is:

=LEFT(text, number_of_chars)

For example, =LEFT(A2, 5) returns the first 5 characters in cell A2.

RIGHT function

The RIGHT function extracts characters from the right side of a text string.

The syntax is:

==RIGHT(C3,LEN(C3)-n)

You can replace the range number with the one you’re working on:

MID function

The MID function returns text from any position in a string for a specified number of characters.

The syntax is:

=MID(text, start_position, number_of_chars)

Using a combination of these functions, you can extract text from the left, right, or middle of cells.

2. Extract Text Before or After Certain Character

You can also extract text before or after a specific character using LEFT, RIGHT, and SEARCH functions.

For example, to separate “John, Doe” into first and last names:

• Use SEARCH to find the position of the space:

=SEARCH(” “, B3)

• Use LEFT to extract text before comma:

=LEFT(B3, SEARCH(“,”,B3)-1)

• Use RIGHT to extract text after comma:

=RIGHT(B3,LEN(B3)-SEARCH(“,”,B3)-1)

This technique works great for extracting text or numbers before or after any special character.

3. Extract Text From the Middle of the String

To extract text from the middle of a string, you can combine MID, SEARCH, and LEN functions in Excel.

For example, to extract the middle name “C” from “John C Doe”:

Enter the formula:

=RIGHT(B3,LEN(B3)-SEARCH(” “,B3)-LEN(TRIM(MID(B3,SEARCH(” “,B3,1)+1,
SEARCH(” “,B3,SEARCH(” “,B3,1)+1)-SEARCH(” “,B3,1))))-1)

This gives you the middle text/initial even if there are multiple words in the cell.

4. Extract Text with TRIM, IFERROR, MIN etc.

You can incorporate additional useful text functions to handle all cases:

• TRIM – Remove extra spaces from the text
• IFERROR – Return alternative result if an error
• MIN – Extract text before the minimum position
• MAX – Extract text before the maximum position

For example, this complex formula extracts the last name even if there is no middle name/initial:

=IFERROR(RIGHT(B3,LEN(B3)-SEARCH(” “,B3)-LEN(TRIM(MID(B3,SEARCH(” “,B3,1)+1,
SEARCH(” “,B3,SEARCH(” “,B3,1)+1)-SEARCH(” “,B3,1))))-1),MID(B3,SEARCH(” “,B3)+1,999))

Don’t worry if you don’t understand it fully. The key takeaway is that combining multiple text functions lets you extract text dynamically.

5. Using Text to Columns to Extract Text

Finally, if you want to split text from cells into separate cells, use the Text to Columns feature.

For example, to extract first and last names from a list:

1. Select the column with the names
2. Go to Data > Text to Columns
3. Choose Delimited > Next
4. Select Space as the delimiter
5. Click Finish

This splits the text into different cells quickly without formulas.

Key Takeaways

The key points to remember are:

• Use LEFT, RIGHT, and MID functions to extract parts of text strings
• Combine with SEARCH and other functions for dynamic results
• Extract text before/after certain characters with SEARCH
• Text to Columns splits text into separate cells easily

Practice these methods, and you’ll be able to extract any part of text from cells in Excel with confidence!

Conclusion

Excel provides a variety of functions to extract text from cells powerfully and flexibly.

Use the RIGHT, LEFT, and MID functions for basic text extraction. Combine with SEARCH, TRIM, etc. for advanced results. And Text to Columns can separate text quickly.

Next time you need to split names, addresses, product codes, or any text in Excel, use these 5 simple techniques. Become a pro at extracting text from cells!

So now you have the complete guide to extracting any text from Excel cells. Go ahead and practice these lessons, and extract text like a champion.