How to extract text after a character in Excel

Microsoft Excel provides us with a variety of built-in formulas to manipulate text data. These formulas help us to extract a part of the data, starting from LEFT, MID or RIGHT of the text. There are some functions to join text data from multiple cells as well.

However, sometimes we have to extract the text data after a special character. Excel provides two methods to achieve the goal

• Using the Flash Fill (CTRL+E) trick
• Using built-in Data Tool “Text to Columns”

Suppose we have a data set containing First Names and Email Addresses separated by “,”. We wish to extract the email addresses only from the data. 

Method 1 – Using the Flash Fill Trick

In this method we are going to use Excel’s magical shortcut CTRL+E trick to flash fill the required data. Flash fill basically works using Excel’s built-in AI Engine, which recognizes the pattern in the data sets and then fills the whole data range using the same pattern. However, for this to work the data needs to be consistent in the pattern.

Step 1 – Create one sample of data to be extracted

• We’ll create one example of the data point to be extracted so Excel can recognize that we wish to extract the email addresses after comma “,” from our data set.

Step 2 – Use CTRL + E to extract the required data

• After creating one example of the data point to be extracted, Excel is now ready to recognize the pattern and can extract required data from our data set by just using one simple command i.e. CTRL+E (Flash Fill). In this method the original data will remain intact.

Method 2 – Using the Data Tools “Text to Columns”

This method is also very handy and it can split the text data into different columns and we can choose any special character from “Tab”, “Semi colon”, “Comma”, “Space” and any other special character of our choice as well. Let’s dive into it and see how it can be done.

Step 1 – Select all the data set

• We’ll use the same data set as before, so just select all the data containing names and email addresses.

Step 2 – Locate the Text to Columns Option on Data Tools

• On the Data tab, go to the Data Tools group and locate the Text to Columns option.

Step 3 – Choose appropriate data type

• Click on the “Convert to Columns” option and a new dialog box will open up. It will ask you to choose the data type. Since our data is delimited with comma, so we’ll select the “delimited” option.

Step 4 – Choose the Data Delimiter

• Click on the “Next” option and we’ll reach 2 out of 3 to split up the data. 
• It will ask you to choose the data delimiter. Since our data is delimited with commas, we’ll select the “Comma” option.

Step 5 – Choose Output Format and Destination

• In the last step 3 of this wizard to split up the data choose the output format and destination’s first column. 

Step 6 – Finally Extract Data

• As the final step, just click the Finish button and you will get the data starting from the column that you chose in the last step.