Pinterest Pixel

Cleaning Data with Excel’s SUBSTITUTE Formula

Bryan
What does it do?
Cleaning Data with Excel's SUBSTITUTE Formula | MyExcelOnline Cleaning Data with Excel's SUBSTITUTE Formula | MyExcelOnline

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:

Cleaning Data with Excel's SUBSTITUTE Formula | MyExcelOnline

download excel workbook Substitute.xlsx

 

STEP 1: We need to enter the Substitute function next to the cell that we want to clean the data from:

=SUBSTITUTE

 

Cleaning Data with Excel's SUBSTITUTE Formula

 

STEP 2: The Substitute arguments:

text

Which text do we want to change?

Reference the cell that contains the text or value:

=SUBSTITUTE(C9,

 

Cleaning Data with Excel's SUBSTITUTE Formula

 

 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, “-“,

 

Cleaning Data with Excel's SUBSTITUTE Formula

 

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, “-“, “#”,

 

Cleaning Data with Excel's SUBSTITUTE Formula

 

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

 

Cleaning Data with Excel's SUBSTITUTE Formula

 

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:

Cleaning Data with Excel's SUBSTITUTE Formula

 

 

 

If you like this Excel tip, please share it
Cleaning Data with Excel's SUBSTITUTE Formula | MyExcelOnline
Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Quick Guide to Convert Years to Days or Months to Days in Excel

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...