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])


When you needed to replace a specific text in each word, and there is a pattern, Excel has just the formula for you.

The SUBSTITUTE formula in Excel can help you replace one specific text with another easily.

In our example below, we have a list of part numbers.

We want to replace the second dash  –  with the number sign  # . This formula is able to do this for us.

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

 

Substitute 01

 

STEP 2: The Substitute arguments:

text

Which text do we want to change?

Reference the cell that contains the text or value:

=SUBSTITUTE(C9,

 

Substitute 02

 

 old_text

Which text / characters do we want to replace?

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

=SUBSTITUTE(C9, “-“,

 

Substitute 03

 

new_text

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

We want to change it into the number sign  #  so type it in with double quotations:

=SUBSTITUTE(C9, “-“, “#”,

 

Substitute 04

 

[instance num]

Which specific instance are we targeting the substitution on?

This parameter is optional. In our scenario, we want the second dash    only to be substituted. So place in the number 2:

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

 

Substitute 05

 

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:

Substitute 06

Substitute

 

HELPFUL RESOURCE:

data cleansing course

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Division Formula in Excel What does it do?Divides two numbersFormula breakdown:=number1 / number2What it means:=the number being divided / the number you are dividing byIn Excel dividing numbers together is really easy!The Division Formula is done through the use of the div...
Cleaning Data with Excel’s TRIM Formula What does it do?Removes unneeded spaces in your text, except single spaces in between wordsFormula breakdown:=TRIM(text)What it means:=TRIM(text that you want extra spaces to be removed)In the quest for cleaner data, one of the common scenarios is remo...
Count Formula in Excel What does it do?Counts the number of cells that contain numbersFormula 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 ...
Extract First Name From Full Name in Excel There were countless times when I had a list of full names, and all I needed was the First Name. It would be time-consuming to manually get the first names one by one. Thank goodness there are formulas to make my life easier!In Excel, it's very easy to do that with the LEFT a...