Pinterest Pixel

Cleaning Data with Excel’s SUBSTITUTE Formula

What does it do? Substitutes new_text for old_text in a text string. Formula breakdown: =SUBSTITUTE(text, old_text, new_text,... read more

Download Excel Workbook
Bryan
Posted on

Steps To Follow

Overview

Cleaning Data with Excel's SUBSTITUTE Formula | MyExcelOnline Cleaning Data with Excel's SUBSTITUTE Formula | MyExcelOnline

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:

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

 

See also  AND Formula in Excel

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.

See also  New Excel Formulas in OFFICE 365: FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY and UNIQUE!

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 Cleaning Data with Excel's SUBSTITUTE Formula | MyExcelOnline
Cleaning Data with Excel's SUBSTITUTE Formula | MyExcelOnline
Bryan

Bryan is a best-selling book author of the 101 Excel Series paperback books.

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...