Data Parsing in Excel Using LEN and SUBSTITUTE function – HOT Tips

Analyzing and parsing data in Excel is really a tedious task. If it is not done smartly, then we end up in doing lot of mess. For example, say you have list of email addresses in Excel and you want to extract the extensions of those email addresses. Most of us think of using Text to Columns option and if we proceed using that, then it creates lot of mess. But, Excel expert Oz has a different approach to make it easy for us.

Data Parsing in Excel Using LEN and SUBSTITUTE Functions

Excel MVP Oz explained us the easy way to extract extensions of emails addresses is by using the technique of data parsing using LEN and SUBSTITUTE functions. Let us see, how it can be done step by step.

1 – Finding the Number of Periods

As we all know, LEN () is used to count the number of characters in a cell and SUBSTITUTE () is used to substitute the occurrence of a specific text with the other text you pass to the function as per following syntax,

SUBSTITUTE (text, old_text, new_text, [instance_num])

Text – Text where the substitution should take place

old_text – Text which needs to be replaced

new_text – Text which needs to be placed in the place of old_text

[instance_num] – It is optional. If specified, that instance of old_text will be replaced. If not specified, all occurrences of old_text will be replaced

To find the number of periods present in email addresses, select the cell B2 and use the formula =LEN(A2)-LEN(SUBSTITUTE (A2,”.”,””)) and hit enter. Now, you can see the number of periods present in email addresses.

Value in cell ‘B2’ specifies the position of last occurrence of period.1

2 – Substitute the last occurrence of period (.) with “^”

In this step, we try to substitute the last occurrence of period (.) with “^”. This is also done using the SUBSTITUTE (), but this time we need to specify the last occurrence (Value in ‘B2’) of period to get it substituted.

Now, select the cell D2, paste the formula =SUBSTITUTE(A2,”.”,”^”,B2) and hit enter. You can see the formatted text in D2 and drag below to get below cells filled in the same manner.2

3 – Find the Position of character “^”

In this step, we will use the FIND () to find the position of the character “^” which we used to substitute period in the previous step.

So, select ‘E2’ and use the formula =FIND(“^”,D2) and hit enter. This would give the position of “^” for the text present in column ‘D’.3

4 – Extract the extension

This is the final step of extracting the extensions from the email addresses. We use RIGHT () here which meets our requirement.

RIGHT () Syntax,

RIGHT (Text, [num_chars])

Text – string containing the text from which you want to extract the characters

num_chars – Optional. Specifies the number of characters you want to extract from text from the right end.

Select cell ‘F2’, use the formula =RIGHT(D2,LEN(D2)-E2) and hit enter. You could see the extensions being extracted.


LEN(D2)-E2 gives the number of characters which we want to extract from the right end.4

 What’s next?

Hope you liked it! Let us know your opinion about this tip.This is really the easy way to parse data in Excel using LEN and SUBSTITUTE functions. If you have anything to add, please do share with us through comments.


You may also like

December 20, 2013

100+ Best Excel Resources And Websites

August 6, 2019

How to Create a Relative File Path in Power Query

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

September 17, 2012

The Excel Rollover Mini FAQ

February 17, 2014

It’s time to say “Goodbye,” to Hungarian Notation
  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free Power User Quick Guide

    Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

    With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.