What does it do?

It replaces specific text in a text string i.e. Substitutes new text for old text in a text string.

Formula breakdown:

=SUBSTITUTE(text, old_text, new_text, [instance num])

What it means:

=SUBSTITUTE(This cell, From this TEXT character, To this TEXT character, [In the 1st, 2nd…instance it occurs])


I had a scenario where I had to count the number of occurrences of a specific character in a cell.

For example, I had to check the number of asterisks * in a cell.  Why you ask?

Well it was because the asterisks corresponded to the number of times the employee was away that year.  Who would’ve thought of putting asterisks, but there you go!

At first, I had no clue on how to do this and it was very cumbersome to do this manually!

Turns out, the SUBSTITUTE formula in Excel can be used creatively to extract the count easily!

Here’s an example:

substitute-count-text-occurrences-ex

We want to return the count of asterisks here, which is 7. We will use a combination of the LEN formula and the  SUBSTITUTE formula.

We will have 3 main steps:

  • Get the length of the entire string – in this case, the length of “Bradley Strickland *******” would be 26.
  • Get the length of the text without the asterisks – in this case, the length of “Bradley Strickland” would be 19.
  • Subtract the two lengths – this will give us 7, which is the number of asterisks.

I explain how you can do this and please go to the bottom of the page to see the animated gif tutorial:

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: We need to use the SUBSTITUTE function to remove all of the asterisks from the text. We will replace the asterisk “*” with an empty string “”.

=SUBSTITUTE(C9, “*”, “”)

substitute-count-text-occurrences-01

You can see now that the asterisks are removed:

substitute-count-text-occurrences-02

 

STEP 2: With this result, we can get the length of the text without asterisks using the LEN function:

=LEN(SUBSTITUTE(C9, “*”, “”))

substitute-count-text-occurrences-03

The length of “Bradley Strickland” is 19:

substitute-count-text-occurrences-04

 

STEP 3: Now here is the cool part!

Since we have the length of the text without asterisks, we now get the length of the complete text with asterisks, then we subtract them together. This will give us the number of asterisk occurrences in the text!

=LEN(C9) – LEN(SUBSTITUTE(C9, “*”, “”))

substitute-count-text-occurrences-05

 

This formula gives us 26 – 19, wherein 26 is the length of the whole text, while 19 is the length of the text without asterisks.

This will give us 7, which is the exact number of asterisks in the text!

substitute-count-text-occurrences-06

 

STEP 4: Do the same for the rest of the cells by dragging the SUBSTITUTE formula all the way down using the left mouse button.

Note that all of the counts are automatically calculated:

substitute-count-text-occurrences-07

substitute-count-text-occurrences

 

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Getting the remainder with Excel’s MOD Formu... What does it do? Gives you the remainder after dividing one number with another Formula breakdown: =MOD(number1, number2) What it means: =MOD(a number, divided by this number) In Excel it is very easy to divide two numbers. But how about if you need to ge...
Extracting Data with Excel’s RIGHT Formula What does it do? It returns the last character or characters in a text string, based on the number of characters you specify. Formula breakdown: =RIGHT(text, ) What it means: =RIGHT(look in this cell, extract X characters) There are times when you will need ...
Cleaning Data with Excel’s CLEAN Formula   What does it do? Removes all nonprintable characters from text Formula breakdown: =CLEAN(text) What it means: =CLEAN(this dirty text cell) There are times when imported text from other applications contain characters that are unprintable. The CLEAN...
VLOOKUP with Multiple Criteria in Excel ‘VLOOKUP’ is now so common that everyone who has ever been working with Excel has heard of it. Not necessarily because they know how to use it, but because some savvy Excel-user always talks about it at the office. Advanced users have seen the use of adding more than one...