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

Extracting text from a cell is one of the most common uses of Microsoft Excel. You may need to extract text from the beginning, middle, or from a specific portion of a cell. In this tutorial, we will show you how to extract text from a cell in Excel. By using these simple but effective formulas, you can easily find and extract any part of a string from a cell.

Download Practice Workbook

Download this practice workbook.

5 Ways on How to Extract Text from a Cell in Excel

1.  Using the LEFT Function to Extract Text from a Cell

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

Syntax of the LEFT Function:

Take a look at this dataset:

Now, using the LEFT function we are going to extract the first 4 characters from the cell.

Step 1:

• Type the following formula in Cell C5.

Step 2:

• Then press Enter.

After that, you will see the extracted text.

Step 3:

• Next, drag the  Fill Handle over the range of cells C6:C9.

Thus, we have extracted all the text from the left.

Read More: How to Extract Text after a Specific Text in Excel

2. Using the RIGHT Function to Extract Text

The RIGHT function extracts a specific number of characters from the end of a string.

Syntax of the RIGHT Function:

We are using the same dataset we used for the LEFT function. But, at this time we are going to extract the 4 characters from the right.

Step 1:

• Now, type the following formula in Cell C5.

Step 2:

• Then press Enter 

Our text will be clipped from the right.

Step 3:

• Next, drag the Fill Handle over the range of Cells C6:C9.

As you can see, all the cells are containing extracted text from the right

3. Using the MID Function to Extract Text from a Cell in Excel

Now you want a specific portion of a text from the middle of a text. In that case, you can use the MID function to perform this. You have to give a starting number and the number of characters you want to extract.

Syntax of the MID Function:

=MID(text, start_num, num_chars)

Have a look at this dataset. We have some codes divided into 3 parts. In this situation, we are going to extract the middle 4 characters.

Step 1:

• Firstly, type this formula in Cell C5.

Step 2:

• Next, press Enter.

Step 3:

• Then, drag the Fill Handle over the range of cells C6:C9.

Ultimately, all the text is clipped from the middle successfully.

Read More: How to Extract Certain Text from a Cell in Excel VBA

4. Extract Text from a Cell Using Formulas

Now, we are going to use some functions to create a formula to extract values from a cell. We are giving three examples of these particular problems.

4.1 Extract Text before a Particular Character

If we want to get a specific substring from a text before a character then we have to first find the character by which we want to extract. For this reason, we are going to use the SEARCH and LEFT functions together.

The Generic Formula:

We have a dataset consisting of some codes separated by a hyphen, “-”. Now, we are going to implement the formula to extract the text before the hyphen.

Step 1:

• To begin with, type the following formula in cell C5.

Step 2:

Then, press Enter.

Step 3:

• After that, drag the Fill Handle over the range of cells C6:C9

In the end, we have found all the text before the hyphen.

Read More: Extract Text Before Character in Excel

4.2 Extract Text after a Particular Character

In this formula, we are going to use the RIGHT function along with the LEN and the SEARCH functions.

The Generic Formula:

Take a look at this dataset:

Now, we want to pick the characters after the “-” character.

Step 1:

• Type the following formula in Cell C5:

Step 2:

• Then, press Enter.

Step 3:

• Now, drag the Fill Handle over the range of cells C6:C9.

As you can see, we have successfully extracted our desired text from the cell.

Read More: Extract Text After a Character in Excel

4.3  Extract Text between Two Specific Characters from a Cell Using the MID and SEARCH Functions

Sometimes, we may need to pick a substring that is situated between two specific characters.  First of all, we have to specify the two specific occurrences by applying a formula. After that, the MID function will extract the text between those two characters.

Now, we have a dataset of the Full names of some people. In this case, we are going to extract the middle name of the person.

Step 1:

• Type the formula in the Cell C5:

Step 2:

• After that, press Enter. You will see the middle name extracted.

Step 3:

• Lastly, drag the Fill Handle over the range of Cells C6:C9.

In the end, we are successful in extracting all those middle names.

Read More: How to Extract Text Between Two Commas in Excel

5. Using Find and Replace to Pick Text from a Cell

Now, this method is going to find a specific portion of a text and replace them with no values. To understand this method, often you have to create a new column.

Firstly, take a look at this dataset :

Now, we are going to use this method to find both username and domain name.

 5.1 Extracting Username from the Email

Step 1:

• Copy the Text column values and paste them on the Extracted Text column.

Step 2:

• Now, select all those values.

Step 3:

• Then, press Ctrl+F on the keyboard. You will find the Find and Replace dialog box.

Step 4:

• Here, in the Find What box type “@*”.  It will select all the characters starting from the @.
• Keep the Replace With box blank.
• Click on Replace All.

Step 5:

• Now, you will see that 5 replacements have been made. Click OK.

As you can see, we have successfully extracted all those usernames.

5.2 Extracting the Domain Name

Step 1:

• Similar to the previous method, copy those emails and paste them on Extracted Text Highlight those and press Ctrl+F.

Step 2:

• Now, in the Find What box, type “*@”. It will find all the characters from the beginning along with @.
• Keep the Replace With box blank.
• Click on Replace All.

In the end, all the domain names are successfully extracted.

Conclusion

To conclude, I hope these formulas will definitely help you extract specific characters. Download the practice workbook and practice all of these methods. I hope that it will enrich your knowledge.

Extract Text in Excel: Knowledge Hub

• How to Extract Text after Second Comma in Excel
• Extract Text After First Space in Excel
• Extract Text between Two Spaces in Excel
• How to Extract Text after Second Space in Excel
• Extract Text After Last Space in Excel

<< Go Back to String Manipulation | Learn Excel