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.


Approach

1. Using Built-in Features

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.

Built-in Feature

1.1 Find & Replace

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.

Copy & paste to extract text from a cell in excel

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

Find and replace to extract text from a cell in excel

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

Find and replace to extract text from a cell in excel

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

Built-in Feature

1.2 Text to Column

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.

Select cells to extract text from a cell in excel

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

Convert text to columns wizard to extract text from a cell in excel

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


Approach

2. Applying Formulas

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.

Applying Formulas

2.1 Extracting Particular Length from Left

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.

=LEFT(B6,4)

LEFT function to extract text from a cell in excel

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.

Applying Formulas

2.2 Withdrawing Particular Length from Right

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.

=RIGHT(B6,3)

RIGHT function to extract text from a cell in excel

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.

Applying Formulas

2.3 Extracting Particular Length in Between Strings

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.

=MID(B6,6,3)

MID function to extract text from a cell in excel

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.

Applying Formulas

2.4 Uproot Before Particular Character

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.

=LEFT(B6,SEARCH(β€œ-β€œ,B6)-1)

LEFT and SEARCH functions to extract text from a cell in excel

πŸ”¨ Formula Breakdown

Here, the formula we inserted in cell C6 is:

LEFT(B6,SEARCH(β€œ-β€œ,B6)-1)

πŸ‘‰ In this function, SEARCH(β€œ-β€œ,B6) looks into cell B6 and searches for β€œ-” and returns 5 as this the position of the character.

πŸ‘‰ Then, SEARCH(β€œ-β€œ,B6)-1 returns 4 by subtracting the result of the SEARCH function.

πŸ‘‰ Lastly, the LEFT function returns left most 4 characters.

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

Applying Formulas

2.5 Pull-Out Text After Particular Character

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.

=RIGHT(B6,LEN(B6)-SEARCH(β€œ-β€œ,B6))

RIGHT, LEN and SEARCH functions to extract text from a cell in excel

πŸ”¨ Formula Breakdown

Here, the formula we inserted in cell C6 is:

RIGHT(B6,LEN(B6)-SEARCH(β€œ-β€œ,B6))

πŸ‘‰ In this function, SEARCH(β€œ-β€œ,B6) looks into cell B6 and searches for β€œ-” and returns 5 as this the position of the character.

πŸ‘‰ LEN(B6) returns the number of characters of the text string in a cell.Here, this function returns 12.

πŸ‘‰ Then, LEN(B6)-SEARCH(β€œ-β€œ,B6) returns 7

πŸ‘‰ Lastly, the RIGHT function returns the rightmost 7 characters.

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

Applying Formulas

2.6 Extract Text in Between Two Particular Characters

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.

=MID(B6,SEARCH(β€œ-β€œ,B6)+1,SEARCH(β€œ-β€œ,B6,SEARCH(β€œ-β€œ,B6)+1)-SEARCH(β€œ-β€œ,B6)-1)

MID and SEARCH functions to extract text from a cell in excel

πŸ”¨ Formula Breakdown

Here, the formula we inserted in cell C6 is:

MID(B6,SEARCH(β€œ-β€œ,B6)+1,SEARCH(β€œ-β€œ,B6,SEARCH(β€œ-β€œ,B6)+1)-SEARCH(β€œ-β€œ,B6)-1)

πŸ‘‰ In this function, SEARCH(β€œ-β€œ,B6) looks into cell B6 and searches for β€œ-” and returns 5 as this the position of the character.

πŸ‘‰ SEARCH(β€œ-β€œ,B6)+1 returns 6 and SEARCH(β€œ-β€œ,B6,SEARCH(β€œ-β€œ,B6)+1)-SEARCH(β€œ-β€œ,B6)-1 returns 3.

πŸ‘‰ Lastly, cell B6 contains the value we want to extract in the MID function. Here, 6 is the starting number of the character, and 3 is the number of characters 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.


Approach

3. Using VBA Codes

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.

Embedding VBA to extract text from a cell in excel

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

=ExtrText(B6,3)

Applying VBA to extract text from a cell in excel

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.

(Visited 52 times, 1 visits today)
Priti

Priti

Hello! Welcome to my Profile. I am Priti. I'm now working and researching Microsoft Excel, and I'll share articles on it here. I am passionate about problem-solving, and I hope these articles will help you. Please leave a remark if you have any queries, and I will do my best to find an appropriate answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo