In an Excel worksheet, you may encounter situations where the last name and first are not separated by commas, and manually adding commas can be a tedious task. Fortunately, this tutorial will teach you how to add commas to names in Excel using the SUBSTITUTE function. The SUBSTITUTE function is a text function used to search and replace text strings in Excel. This is a very useful function if you want to replace old text with new text. Let's start exploring the functions of this function right now.

Key Points

• Simplified List Format – Use the SUBSTITUTE function to easily insert commas between names without manually editing each entry.
• Suitable for batch changes – You can quickly apply formulas to multiple cells to clean or format lists more efficiently.
• Flexible alternative options – SUBSTITUTE can replace spaces, semicolons, or other separators with commas according to your needs.
• Improve data consistency – Automatically insert commas to ensure consistency and appropriate formatting of name lists for further use.
• Easy to combine with other functions – You can nest SUBSTITUTE with TRIM or CLEAN functions to get better results when dealing with chaotic data.

Table of contents

Introduction to SUBSTITUTE function

What does it do?

It replaces specific text in a text string, i.e., replaces old text with new text.

Formula decomposition:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

what does that mean:

The SUBSTITUTE function has four parameters – text, old_text, new_text and instance_num. The first three parameters are required, and the fourth is optional.

• text – The original text you want to replace the text. Can be the result of a cell reference, a text string, or another formula.
• old_text – the text you want to replace
• new_text – the text you want to replace
• instance_num – The number of occurrences of the old text you want to replace, i.e. the first occurrence, the second occurrence, etc. If left blank, replace all occurrences of instances.

Let's use an example to understand how to use the SUBSTITUTE function and how to use this function to add commas to names in Excel.

Add commas to names in Excel using SUBSTITUTE function

I remember countless times when I received a name list from HR, there was no comma separation between last name and first name.

This gives me a headache! ! !

I had to manually edit each name so that it looks like , .

As a result, the SUBSTITUTE formula in Excel can easily accomplish this task!

In the following example, we have a list of names. We want to replace spaces with commas and spaces. The SUBSTITUTE formula can do this for us!

Follow these steps to learn how to add commas to names in Excel:

Step 1: We need to enter the SUBSTITUTE function next to the cell where we want to clean up the data:

=SUBSTITUTE

Step 2: SUBSTITUTE Parameters:

text

Which cell do we want to change?

References to a cell containing text or values:

=SUBSTITUTE(C9,

old_text

What text/characters do we want to replace?

We want to change the space , so enter it:

=SUBSTITUTE(C9, ” “,

new_text

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

We want to replace it with commas and spaces , so enter it:

=SUBSTITUTE(C9, ", ", ")

Step 3: Do the same for the remaining cells, drag the SUBSTITUTE formula down by using the left mouse button.

Note that all names have now been changed to have the comma you need:

This will easily replace the space between the name with commas and spaces.

Now that you have confidently added commas to names in Excel using the SUBSTITUTE function, let's continue to understand how to use the instance_num parameter in the formula.

Use instance_num parameter in SUBSTITUTE function

Let's take an example to get a deeper understanding of this function.

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 functions

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

Suppose, you want to replace only the first 1 and 2, i.e. replace Sales for Quarter 2 Product 1 with Sales for Quarter 2 Product 1. You can use functions

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

By adding the instance_num parameter, you have restricted Excel to replace only the first 1's occurrence.

Again, you can replace only the second 1 and 2 with the formula

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

Comparison between SUBSTITUTE and REPLACE

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

• When you know which word to change, you should use the SUBSTITUTE function
• When you know the position and length of the character you want to change, you should use the REPLACE function.

Let's learn more about the REPLACE function to learn more about the differences!

Formula decomposition:

Syntax of REPLACE function:

=REPLACE (old_text, start_num, num_chars, new_text)

what does that mean:

• old_text – The text string you want to find the character you want to replace.
• start_num – where you want the replacement function to occur
• num_chars – the number of characters you want to replace
• new_text – replace text

example:

Suppose you have a text "Excel 2020" in cell A2 that you want to change 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 this using the REPLACE function:

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

Since you also know the text you want to replace, using the SUBSTITUTE function can also do the job.

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

Frequently Asked Questions

What does the SUBSTITUTE function do in Excel?
The SUBSTITUTE function replaces specific text in a string with new text, such as replacing spaces with commas between names.

Can SUBSTITUTE handle multiple types of separators at once?
SUBSTITUTE can only be replaced one at a time, but you can nest multiple SUBSTITUTE functions to handle different separators.

How to replace spaces between names with commas?
You can insert commas and spaces between names in cell A1 using the formula =SUBSTITUTE(A1, " ", ", ") .

Does the SUBSTITUTE function change all spaces, including spaces between first and last name?
Yes, it replaces all instances, so use it with caution if you want to replace only certain spaces (in this case you may need a more advanced formula).

Is there a way to do this automatically for the entire column?
Yes, after writing the SUBSTITUTE formula for one cell, you can drag it down to apply to all other cells.

in conclusion

In this tutorial, you have learned how to add commas to names in Excel using the SUBSTITUTE function. You can learn more about this function by reading this blog on how to clean up data using the SUBSTITUTE function .

The above is the detailed content of Add Comma in Excel between Names with SUBSTITUTE Formula. For more information, please follow other related articles on the PHP Chinese website!