How to Extract a Substring in Excel (Using TEXT Formulas)

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. One of the common tasks for people working with text data is to extract a substring in Excel. This could be done using formulas as well as some other in-built excel features.

In this tutorial, you’ll learn:

  • How to Use Text Functions to Extract a Substring in Excel.
  • How to Use Text to Columns Feature to Extract a Substring in Excel.
  • How to Use Find and Replace Feature to Extract a Substring in Excel.

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:

Extract Substring in Excel Using Formulas Email Data

These are some random (but superheroish) 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:

=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 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:

=RIGHT(A2,LEN(A2)-FIND("@",A2))

Extract Substring in Excel Using Formulas domain name

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:

=MID(A2,FIND("@",A2)+1,FIND(".",A2)-FIND("@",A2)-1) 

Extract Substring in Excel Using Formulas domian without com

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:

=MID(A1,FIND("@",A1)+1,FIND(".",A1,FIND("@",A1))-FIND("@",A1)-1)

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.Extract Substring in Excel Using Formulas select data
  • Go to Data –> Data Tools –> Text to Columns.Extract Substring in Excel Using Formulas text to columns
  • In the Text to Column Wizard Step 1, select Delimited and press Next.Extract Substring in Excel Using Formulas Text to Columns1
  • 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.Extract Substring in Excel Using Formulas Text to Columns2
  • 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.Extract Substring in Excel Using Formulas Text to Columns3
  • Click on Finish.

This will instantly give you two sets of substrings for each email id used in this example.Extract Substring in Excel Using Formulas Text to Columns result

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 a Substring 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.

See Also: Learn All about Wildcard Characters 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.Extract Substring in Excel Using Formulas Find and Replace 1
  • Select the data and go to Home –> Editing –> Find & Select –> Replace (or use the keyboard shortcut Ctrl + H).Extract Substring in Excel Using Formulas Find and Replace 2
  •  In the Find and Replace dialogue box, enter the following:
    • Find what: @*
    • Replace with: (leave this blank)Extract Substring in Excel Using Formulas Find and Replace 3
  • Click on Replace All.Extract Substring in Excel Using Formulas Find and Replace 4

This will instantly remove all the text before the @ in the email ids. You’ll have the result as shown below:

Extract Substring in Excel Using Formulas Find and Replace 8

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.
    Extract Substring in Excel Using Formulas Find and Replace 1
  • Go to Home –> Editing –> Find & Select –> Replace (or use the keyboard shortcut Ctrl + H).Extract Substring in Excel Using Formulas Find and Replace 2
  •  In the Find and Replace dialogue box, enter the following:
    • Find what: *@
    • Replace with: (leave this blank)Extract Substring in Excel Using Formulas Find and Replace 5
  • Click on Replace All.Extract Substring in Excel Using Formulas Find and Replace 6

This will instantly remove all the text before the @ in the email ids. You’ll have the result as shown below:

Extract Substring in Excel Using Formulas Find and Replace 7

You May Also Like the Following Excel Tutorials:

  • Thiyagarajan Devarajan says:

    I am using Text to column menu for this, But I don’t know using the formula, it is very tricky,Thanks Sumit, Its very useful.

  • Anand Kumar says:

    Nice Trick………………

  • Rudra says:

    Sumit,
    I use these tricks almost everyday. Glad that you share with the world.

  • william19 says:

    Excellent. Add a note that “Example 3 – Extracting the Domain Name from Email Ids (without .com)” will not work if the email address has a dot (.) in it.

    • Sumit Bansal says:

      Thanks for pointing this out William.. I have updated the article with a note and the relevant formula.

  • Hemang Patel says:

    Excellent………….thanks a million

  • >