Add Comma in Excel between Names with SUBSTITUTE Formula

While working in an Excel Worksheet, you will come across scenarios where the last name is not separated from the first name by a comma, and doing that manually could be a daunting task. Good thing that this tutorial teaches you how to add comma in Excel between names using SUBSTITUTE function.

In this tutorial on how to insert comma between words in excel, you will learn:

• Introduction to SUBSTITUTE function
• Add comma in excel between names using SUBSTITUTE function
• Using instance_num argument in SUBSTITUTE function
• Substitute vs Replace

SUBSTITUTE function is a type of TEXT function and is used to search and replace text strings in Excel. It is a very useful function if you wish to replace an old text with new text.

Let’s jump right in and start exploring the features of this function.

Introduction to SUBSTITUTE function

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 function has four arguments – text, old_text, new_text and instamce_num. The first 3 arguments are required and the fourth one is optional.

• text – The original text in which you want to replace the text. It can be a cell reference, a text string, or a result of another formula.
• old_text – The text you want to replace
• new_text – The text you want to replace with
• instance_num – The occurrence of the old text you want to substitute i.e. 1st occurrence, 2nd occurrence, etc. If it is left blank, all the occurrences are replaced.

---

Let’s work with an example to know how to use SUBSTITUTE function and how to add a comma in excel between names using this function.

Add comma in excel between names using SUBSTITUTE function

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 space.  The SUBSTITUTE formula is able to do this for us!

I explain how you can do this and please go this tutorial on how to add comma in Excel between names:

Follow the steps below to understand how to add comma in Excel between names:

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 cell do we want to make changes to?

Reference the cell that contains the text or value:

=SUBSTITUTE(C9,

 old_text

Which text/characters do we want to replace?

We want to change the space so type it in:

=SUBSTITUTE(C9, ” “,

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

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:

This will easily substitute a space between names with a comma followed by space.

Now that you are confident in using the SUBSTITUTE function to add comma in Excel between names, let’s move forward and understand how to use instance_num argument in your formula.

Using instance_num argument in SUBSTITUTE function

Let’s work on an example to understand this function in depth.

 

In cell A2, you have the text – “Sales for Quarter 1 Product 1” and you want to replace it with the text “Sales for Quarter 2 Product 2”. You can use the function

=SUBSTITUTE(A2,”1″,”2″)

 

 

Say, you want to replace only the first occurrence of 1 with 2 i.e. replace Sales for Quarter 2 Product 1 to Sales for Quarter 2 Product 1. You can use the function

=SUBSTITUTE(A3,”1″,”2″,1)

By adding the instance_num argument, you have restricted Excel to substitute only the first occurrence of 1.

Similarly, you can substitute only the second occurrence of 1 with 2 using the formula

=SUBSTITUTE(A4,”1″,”2″,2)

 

Substitute vs Replace

Both these functions are used to replace an old text with the new text. The main difference between the two is that:

• You should use the SUBSTITUTE function when you know which word you want to change
• You should use REPLACE function when you know the position and length of the characters you want to change.

Let’s learn more about the REPLACE function to understand the difference in detail!

Formula breakdown:

The syntax of the REPLACE function:

=REPLACE (old_text, start_num, num_chars, new_text)

What it means:

• old_text – The text string in which you want to find the characters to replace.
• start_num – The starting position where you want the replace function to take place
• num_chars – The number of characters you want to replace
• new_text – The replacement text

Example :

Say, you have a text “Excel 2020” in cell A2 and you want to change it to “Excel 2021”. You want to replace 4 characters starting from the 7th character and you want the replacement text to be “2021”. You can do that using the REPLACE function:

=REPLACE (A2, 7, 4,”2021″)

Since you also know the text you want to replace using SUBSTITUTE function would also get the work done.

=SUBSTITUTE(A3,”2020″,”2021″)

Conclusion

In this tutorial, you have learned how to use the SUBSTITUTE function to add comma in Excel between names. You can learn more about this function by going through this blog on How to clean data using the SUBSTITUTE function.

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

You can learn more about how to use Excel by viewing our FREE Excel webinar training on Formulas, Pivot Tables, and Macros & VBA!