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.
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.
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.
- 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.
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.
- 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.
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.
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)
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.
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)
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.
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)
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.
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)
๐จ 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.
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))
๐จ 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.
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)
๐จ 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.
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.
- 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)
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.