What does it do?

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, By this text character, To this text character, [In the 1st, 2nd…instance it occurs])


There are times where I needed to remove the nth occurrence from my text.  For example I would like to remove the second hyphen in my part numbers.

Normally I would have to find the second hyphen, split the text based on the hyphen’s location, merge the remaining parts together….pfff….it’s just a headache and prone to errors!!

The SUBSTITUTE formula in Excel can actually do this for you! You might be thinking, “How can substitute remove characters?”

The secret is, we use SUBSTITUTE to replace our target character with an empty string!

In our example below, we have a list of part numbers and we want to remove the second dash.

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 enter the Substitute function next to the cell that we want to clean the data from:

=SUBSTITUTE

 

STEP 2: The Substitute arguments:

text

Which text do we want to change?

Reference the cell that contains the text or value:

=SUBSTITUTE(C9,

 old_text

Which text / characters do we want to replace?

We want to remove the dash    so type it in with double quotations:

=SUBSTITUTE(C9, “-“,

new_text

Which text / characters do we want to replace it with?

We want to remove this, so just type in a blank value:

=SUBSTITUTE(C9, “-“, ,

[instance num]

Which specific instance are we targeting the substitution on?

This parameter is optional.  In our scenario, we only want the second dash to be removed.  So place in the number 2 (as it is the 2nd instance the dash is located):

=SUBSTITUTE(C9, “-“, , 2)

 

STEP 3: 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 parts are now changed to your new part values:

HELPFUL RESOURCE:

data cleansing course

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

Cleaning Data with Excel’s SUBSTITUTE Formul... What does it do? Substitutes new_text for old_text in a text string. Formula breakdown: =SUBSTITUTE(text, old_text, new_text, ) What it means: =SUBSTITUTE(This cell, By this text character, To this text character, ) When you needed to replace a specific text...
CountIfs Formula in Excel What does it do? Counts the number of cells that matches your specified conditions Formula breakdown: =COUNTIFS(range1, criteria1, , , ...) What it means: =COUNTIFS(range of cells to check1, condition to check against1, , , ...) Do you have a scenario where ...
Add Comma After the Surname with Excel’s SUB... 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, ) What it means: =SUBSTITUTE(This cell, From this TEXT character, To this TEXT character, )...
Count Formula in Excel What does it do? Counts the number of cells that contain numbers Formula breakdown: =COUNT(value1, ...) What it means: =COUNT(range of cells to check, ...) Ever had a column of data and wanted to check if all of the values contain valid numbers? It would ...