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 can recall the countless times when I was given a list of names from the HR department and the last name was not separated from the first name by a comma.

It drove me nuts!!!

I had to manually edit the names one by one, so that it would look like <last name>, <first name>.

Turns out, the SUBSTITUTE formula in Excel would have done this task very easily!

In our example below, we have a list of names.  We want to replace the space with a comma followed by the space.  The SUBSTITUTE 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 - Add Comma 01

 

STEP 2: The Substitute arguments:

text

Which cell do we want to make changes to?

Reference the cell that contains the text or value:

=SUBSTITUTE(C9,

Substitute - Add Comma 02

 old_text

Which text / characters do we want to replace?

We want to change the space so type it in:

=SUBSTITUTE(C9, ” “,

Substitute - Add Comma 03

new_text

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

We want to replace it into the comma followed by a space, so type it in:

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

Substitute - Add Comma 04

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 names are now changed to having your needed commas:

Substitute - Add Comma 05

Substitute - Add Comma

 

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

Change Phone Area Codes with Excel’s REPLACE Formu... What does it do? Replaces part of a text string, based on the number of characters you specify, with a different text string Formula breakdown: =REPLACE(old_text, start_num, num_chars, new_text) What it means: =REPLACE(this cell, starting from this number, all the ...
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...
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 ...
Concatenate With A Line Break   What does it do? Joins two or more text strings into one string. The item can be a text value, number, or cell reference. Formula breakdown: =CONCATENATE(text1, , , ...) What it means: =CONCATENATE(the first text, the second text, and so on...) ...