One of the common tasks for people working with text data is to extract a substring in Excel (i.e., get psrt of the text from a cell). Unfortunately, there is no substring function in Excel that can do this easily. However, this could still be done using text formulas as well as some other in-built Excel features. Let’s first have a look at some of the text functions we will be using in this tutorial.
Excel TEXT Functions
Excel has a range of text functions that would make it really easy to extract a substring from the original text in Excel. Here are the Excel Text functions that we will use in this tutorial:
RIGHT function: Extracts the specified numbers of characters from the right of the text string. LEFT function: Extracts the specified numbers of characters from the left of the text string. MID function: Extracts the specified numbers of characters from the specified starting position in a text string. FIND function: Finds the starting position of the specified text in the text string. LEN function: Returns the number of characters in the text string.
Extract a Substring in Excel Using Functions
Suppose you have a dataset as shown below:
These are some random (but superhero-ish) email ids (except mine), and in the examples below, I’ll show you how to extract the username and domain name using the Text functions in Excel.
Example 1 – Extracting Usernames from Email Ids
While using Text functions, it is important to identify a pattern (if any). That makes it really easy to construct a formula. In the above case, the pattern is the @ sign between the username and the domain name, and we will use it as a reference to get the usernames. Here is the formula to get the username: The above formula uses the LEFT function to extract the username by identifying the position of the @ sign in the id. This is done using the FIND function, which returns the position of the @. For example, in the case of brucewayne@batman.com, FIND(“@”,A2) would return 11, which is its position in the text string. Now we use the LEFT function to extract 10 characters from the left of the string (one less than the value returned by the LEFT function).
Example 2 – Extracting the Domain Name from Email Ids
The same logic used in the above example can be used to get the domain name. A minor difference here is that we need to extract the characters from the right of the text string. Here is the formula that will do this:
In the above formula, we use the same logic, but adjust it to make sure we are getting the correct string. Let’s again take the example of brucewayne@batman.com. The FIND function returns the position of the @ sign, which is 11 in this case. Now, we need to extract all the characters after the @. So we identify the total length of the string and subtract the number of characters till the @. It gives us the number of characters that cover the domain name on the right. Now we can simply use the RIGHT function to get the domain name.
Example 3 – Extracting the Domain Name from Email Ids (without .com)
To extract a substring from the middle of a text string, you need to identify the position of the marker right before and after the substring. For example, in the example below, to get the domain name without the .com part, the marker would be @ (which is right before the domain name) and . (which is right after it). Here is the formula that will extract the domain name only:
Excel MID function extracts the specified number of characters from the specified starting position. In this example above, FIND(“@”,A2)+1 specifies the starting position (which is right after the @), and FIND(“.”,A2)-FIND(“@”,A2)-1 identifies the number of characters between the ‘@‘ and the ‘.‘ Update: One of the readers William19 mentioned that the above formula wouldn’t work in case there is a dot(.) in the email id (for example, bruce.wayne@batman.com). So here is the formula to deal with such cases:
Using Text to Columns to Extract a Substring in Excel
Using functions to extract a substring in Excel has the advantage of being dynamic. If you change the original text, the formula would automatically update the results. If this is something you may not need, then using the Text to Columns feature could be a quick and easy way to split the text into substrings based on specified markers. Here is how to do this:
Select the cells where you have the text. Go to Data –> Data Tools –> Text to Columns. In the Text to Column Wizard Step 1, select Delimited and press Next. In Step 2, check the Other option and enter @ in the box right to it. This will be our delimiter that Excel would use to split the text into substrings. You can see the Data preview below. Click on Next. In Step 3, General setting works fine in this case. You can however, choose a different format if you are splitting numbers/dates. By default, the destination cell is where you have the original data. If you want to keep the original data intact, change this to some other cell. Click on Finish.
This will instantly give you two sets of substrings for each email id used in this example. If you want to further split the text (for example, split batman.com to batman and com), repeat the same process with it.
Using FIND and REPLACE to Extract Text from a Cell in Excel
FIND and REPLACE can be a powerful technique when you are working with text in Excel. In the examples below, you’ll learn how to use FIND and REPLACE with wildcard characters to do amazing things in Excel. Let’s take the same Email ids examples.
Example 1 – Extracting Usernames from Email Ids
Here are the steps to extract usernames from Email Ids using the Find and Replace functionality:
Copy and paste the original data. Since Find and Replace works and alters the data on which it is applied, it is best to have a backup of the original data. Select the data and go to Home –> Editing –> Find & Select –> Replace (or use the keyboard shortcut Ctrl + H). In the Find and Replace dialogue box, enter the following: Find what: @* Replace with: (leave this blank) Click on Replace All.
This will instantly remove all the text before the @ in the email ids. You’ll have the result as shown below:
How this works?? – In the above example, we have used a combination of @ and . An asterisk () is a wildcard character that represents any number of characters. Hence, @* would mean, a text string that starts with @ and can have any number of characters after it. For example in brucewayne@batman.com, @* would be @batman.com. When we replace @* with blank, it removes all the characters after @ (including @).
Example 2 – Extracting the Domain Name from Email Ids
Using the same logic, you can modify the ‘Find what’ criteria to get the domain name. Here are the steps:
Select the data. Go to Home –> Editing –> Find & Select –> Replace (or use the keyboard shortcut Ctrl + H). In the Find and Replace dialogue box, enter the following: Find what: *@ Replace with: (leave this blank) Click on Replace All.
This will instantly remove all the text before the @ in the email ids. You’ll have the result as shown below:
How to Count Cells that Contain Text Strings. Extract Usernames from Email Ids in Excel [2 Methods]. Excel Functions (Examples + Videos). Get More Out of Find and Replace in Excel. How to Capitalize First Letter of a Text String in Excel How to Extract the First Word from a Text String in Excel Separate Text and Numbers in Excel
Inv No. PC How Can I get the answer on column B by using Excel Formula in Column C 18JH42A330EN00710076 A330 = What will be the formula?? 18CH2S011S0047 S011 18JK2T007G0111 T007 18TN2O004G0280 O004 18PM080154WB0648 PM08
