How to Add Comma in Excel

How-to-Add-Comma-in-Excel

This article will explain how to add a comma in Excel using the ampersand (&) operator or built-in functions.

Add a single comma to join two values

In this first example, you want to join two cells containing text values and then insert a comma between them to create a properly delimited result. This is done using the ampersand operator (&), commonly used for concatenating text in Excel.

To join the values of B2 and C2 with a comma in between, type the following formula into cell E2:

=B2&”,”&C2

Add-a-single-comma-to-join-two-values

Explanation:

  1. B2 refers to the first value or text you want to join.
  2. &”,”& adds the comma delimiter between the two values. Notice the comma is wrapped in quotation marks (“,”), which is necessary when including text in formulas.
  3. C2 refers to the second value or text.

Excel will output a combined text string like “apple, banana” in the target cell E2. It is good to know that you are joining text values; you should use quotation marks 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 must use the delimiter (in the example, it is a comma) once as the 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)

how-to-use-the-textjoin-function-to-merge-list-items

Replace space or other characters with comma

This part of the tutorial will 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,”?”,”,”)

use-the-SUBSTITUTE-function-to-add-comma-in-Excel

You can also replace other characters, for example, asterisk:

=SUBSTITUTE(B3,”*”,”,”)

Build a running list

To create an increasing array or list in Excel, use the formula in cell D3 and copy it down.

=D2&”,”&B3

create-an-increasing-list

The formula above uses the ampersand character to add a next larger value and uses a comma delimiter between values.