Which function would you use to find the column location of specified text?
|
Excel has a set of TEXT Functions that can do wonders. You can do all kinds of text slice and dice operations using these functions. Show
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. This Tutorial Covers: Let’s first have a look at some of the text functions we will be using in this tutorial. Excel TEXT FunctionsExcel 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:
Extract a Substring in Excel Using FunctionsSuppose 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 IdsWhile 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: =LEFT(A2,FIND("@",A2)-1)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 [email protected], 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 IdsThe 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: =RIGHT(A2,LEN(A2)-FIND("@",A2))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 [email protected]. 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: =MID(A2,FIND("@",A2)+1,FIND(".",A2)-FIND("@",A2)-1) 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 mentioned that the above formula wouldn’t work in case there is a dot(.) in the email id (for example, [email protected]). So here is the formula to deal with such cases: =MID(A1,FIND("@",A1)+1,FIND(".",A1,FIND("@",A1))-FIND("@",A1)-1)Using Text to Columns to Extract a Substring in ExcelUsing 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:
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 ExcelFIND 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. See Also: Learn All about Wildcard Characters in Excel. Let’s take the same Email ids examples. Example 1 – Extracting Usernames from Email IdsHere are the steps to extract usernames from Email Ids using the Find and Replace functionality:
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 [email protected], @* would be @batman.com. When we replace @* with blank, it removes all the characters after @ (including @). Example 2 – Extracting the Domain Name from Email IdsUsing the same logic, you can modify the ‘Find what’ criteria to get the domain name. Here are the steps:
This will instantly remove all the text before the @ in the email ids. You’ll have the result as shown below: You May Also Like the Following Excel Tutorials:
FREE EXCEL BOOK Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster
Name YES - SEND ME THE EBOOK 14 thoughts on “How to Extract a Substring in Excel (Using TEXT Formulas)”
|
