This article will explain how to add a comma in Excel using the ampersand (&) operator or built-in functions.
Before diving deep into the details, download the practice Workbook, which contains all demonstrated examples.
Add a single comma to join two values
In the first example, you want to join two separate cells containing values. Then, add a comma to concatenate the two words using a delimiter.
In cell E2, use the following formula and the ampersand operator to merge two values::
=B2&","&C2
Good to know that you are joining text values; you should have to use quotations mark from both sides of the separator “,”.
Insert a comma to combine a list or range
In the first example, we added a comma to join two or more values without using formulas. If you have a list in Excel, you can use the TEXTJOIN function to perform the task. TEXTJOIN is more simple than the ampersand-based expression.
=TEXTJOIN(delimiter, skip_blank, range)The TEXTJOIN function can combine multiple cells. The main point is that you need to use the delimiter (in the example, it is a comma) once; as a first argument. The second argument is TRUE because we want to ignore the blank cells. Finally, select the range of cells that you want to join.
In the example, use the following formula:
=TEXTJOIN(",",TRUE,B2:B5)
Replace space or other characters with comma
This part of the tutorial will be on how to use the SUBSTITUTE function to replace one or more characters with another.
In the example, we have a unique delimiter, question mark (?), between words. To replace the question marks with commas, use the formula:
=SUBSTITUTE(B2,"?",",")
You can also replace other characters, for example, asterisk:
=SUBSTITUTE(B3,"*",",")Build a running list using a comma
To create an increasing array or list in Excel, use the following formula in cell D3, then copy the formula down.
=D2&","&B3
The formula above uses the ampersand character to add a next larger value and uses a comma delimiter between values.
Additional resources:
