9 Ways to Extract Text from a Cell in Excel

Excel frequently allows users to extract text from a cell. Depending on our needs, we can extract cells using a variety of Excel functions, formulas, and features. We could need to remove cells from the text’s left, right, or center. But distinct functions extract texts in various ways. So, we’ll show you 9 diverse ways to extract text from a cell in excel in this article.

📁 Download Excel File

You can download the Excel file used for the demonstration from the link below.

Learn to Extract Text from a Cell in Excel with These 9 Methods

This article will show you nine distinct ways to extract text from a cell in excel. Among them, the first two methods use built-in features of Excel, and the following six methods use different functions and formulas to extract text according to your desire. The last method shows the use of Microsoft Visual Basic Application for extracting texts from a sentence of a group of words. So, now let us dig into the article to find out how all these methods work.

First, we will use the built-in features of Find & Replace and Text to Columns for extracting text from a cell in Excel. We have used a dataset containing email addresses from which we will extract usernames and domain names.

Firstly, we will use Excel’s Find & Replace feature to extract a specific part of a text string from a cell. Here, we will demonstrate how to extract a domain name and username from an email address. Now, let us go through the steps.

Extracting Names from Email

The email contains two parts username and domain name. The username is the part before the @ sign. Now, we will use Find and Replace to find this @ sign and replace it with blank for extracting the username.

⬇️⬇️ STEPS ⬇️⬇️

• At first, select cell B6:B9 and copy them by pressing Ctrl+C and pasting them by pressing Ctrl+V in cell C6.

• Now select the pasted cells C6:C9 and press Ctrl+H.

• A dialog box named Find and Replace will appear, and select Replace from there. To extract the username, type @* into Find what, keep Replace with option blank, and click on Replace All.

• Hence, we will have the usernames extracted.

Extracting Domains from Email

The email contains two parts username and domain name. The domain name is the part after the @ sign. Now, we will use Find and Replace to find this @ sign and replace it with blank for extracting the username.

⬇️⬇️ STEPS ⬇️⬇️

• Similarly, to extract the domain names, copy cell B6:B9 and paste them into cells C6:C9.

• Then select the pasted cells C6:C9 and press Ctrl+H.

• Next, to extract the domain name, type *@ into Find what, keep Replace with option blank, and click on Replace All.

• Finally, we will have the domain names extracted.

We can extract usernames and domain names using Excel’s Text to Column feature. Therefore, we will show how to use this feature to extract text from a cell in Excel. Let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select cells B6:B9.

• After that, go to the Data tab in Ribbon. From the Data Tools, select Text to Column.

• Then, a box named Convert Text to Columns Wizard will appear. Select Delimited from the original data type and press Next.

• Next, select Other as a delimiter, type @, and click Next.

• Then, type $C$6 in the destination option and click on Finish.

• Finally, we will have user and domain names extracted.

Now, we will use different functions and formulas to extract text from a cell in Excel. Here, we will use a different dataset. We have some postcodes containing hyphens. In this section, we will extract texts from the left, right, and middle of these texts and show how to extract text from before, after, and in between two particular characters. The dataset is as follows.

In this method, we will extract a particular length from the left side of a text. We have used the LEFT function to do this. The LEFT function usually returns the first character or characters in the text string from the left side of the defined cell, depending on the number of characters you choose. Now let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the cell C6 and insert the formula.

Here, cell B6 contains the value we want to extract, and 4 is the number of characters that we want to extract.

• Then copy and paste this formula into all other cells. For that, select cell C6 again and click & drag the Fill Handle down, or Double-click on the plus that appeared to Autofill the cells after selecting. Lastly, the outcome looks like this.

Now, we will extract a particular length from the right side of a text. We have used the RIGHT function to do this. The RIGHT function usually returns the first character or characters in the text string from the right side of the defined cell, depending on the number of characters you choose. Now let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️ 

• First, select the cell C6 and insert the formula.

Here, cell B6 contains the value we want to extract, and 3 is the number of characters that we want to extract.

• Then copy and paste this formula into all other cells. For that, select cell C6 again and click & drag the Fill Handle down, or Double-click on the plus that appeared to Autofill the cells after selecting. Lastly, the outcome looks like this.

In this next method, we will extract a particular length from in-between text strings. We have used the MID function to do this. The MID function usually returns a particular number of characters from the text string depending on the number of characters you choose. Now let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the cell C6 and insert the formula.

Here, cell B6 contains the value we want to extract, here, 6 is the starting number of the character and 3 is the number of characters that we want to extract.

• Then, select cell C6 again and click & drag the Fill Handle down, or Double-click on the plus that appeared to Autofill the cells after selecting. Finally, the outcome looks like this.

Next, we will uproot text from before a particular character in a text string. We have used LEFT and SEARCH functions to do this. The LEFT function extracts from a text string according to the specified characters. On the other hand, the SEARCH function returns the starting number of the character that this function searched. Now let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, select cell C6 and insert the formula.

• Then, select cell C6 again and click & drag the Fill Handle down, or Double-click on the plus that appeared to Autofill the cells after selecting. Finally, the outcome looks like this.

Next, we will extract text after a particular character in a text string. We have used the RIGHT, LEN, and SEARCH functions. The RIGHT function extracts from the right side of a text string according to the specified characters, and the LEN function returns the number of characters in a text string. On the other hand, the SEARCH function returns the starting number of the character that this function searched. Now let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, insert the formula in cell C6.

• Then, select cell C6 again and click & drag the Fill Handle down, or Double-click on the plus that appeared to Autofill the cells after selecting. Finally, the outcome looks like this.

Now, we will use the MID and SEARCH functions to extract text between two particular characters in a text string. The MID function usually returns a particular number of characters from the text string depending on the number of characters you choose. On the other hand, the SEARCH function returns the starting number of the character that this function searched. Now let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, insert the formula in cell C6.

• Then, select cell C6 again and click & drag the Fill Handle down, or Double-click on the plus that appeared to Autofill the cells after selecting. Finally, the outcome looks like this.

This method will demonstrate how to extract text from a cell in excel using Microsoft Visual Basic Application (VBA). This VBA code extracts texts from sentences or a group of words. Hence, we used a new data set containing sentences.

Also, ensure you have the Developer tab in the ribbon before applying this code. If you do not have the Developer tab, click here to see how to enable the Developer tab on your ribbon.

Now, let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

• At first, go to the Developer tab and select Visual Basic from the code group.

• Then, Microsoft Visual Basic for Application named box will appear. Go to the Insert tab and select the Module.

• Enter the code and save it.

Function ExtrText(CellVal As String, Pos As Integer)

Dim StrArr() As String

StrArr = VBA.Split(CellVal, " ")

SCount = UBound(StrArr)

If SCount < 1 Or (Pos - 1) > SCount Or Pos < 0 Then

ExtrText = ""

Else

ExtrText = StrArr(Pos - 1)

End If

End Function

• Now, to run the code, select cell C6 and insert the formula.

Here, cell B6 contains the value we want to extract, and 3 is the position of the text that we want to extract.

• Lastly, drag the Fill Handle and AutoFill the rest of the cells, and we will have our desired output.

📄 Important Notes

🖊️ To apply VBA codes, ensure you have the Developer tab in the ribbon before the application.

🖊️ The VBA code works for only text format and groups of words.

📝 Takeaways from This Article

📌 This article demonstrated nine ways to extract text from a cell in Excel.

📌 In the first two methods, we showed Find & Replace and Text to Columns built-in features for extracting text from a cell in Excel.

📌 Then, we demonstrated how to use distinct functions, LEFT, RIGHT and MID, to extract text according to your desire in Excel in the following three methods.

📌 Next, we demonstrated formulas combining LEFT, RIGHT, MID, SEARCH, and LEN functions to extract text according to your desire in Excel in the following three methods.

📌  Lastly, we showed how to embed VBA code to extract text from a cell in Excel.

Conclusion

This article has demonstrated nine ways to extract text from a cell in excel. All these methods are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit Excelden.com.