To join multiple cell values with a comma, you can use a formula based on the SUBSTITUTE and TRIM functions. You can use this same approach to concatenate values in cells with any delimiter you like. In the example shown, the formula in G5 is:
Working from the inside out, the formula first joins the values the 5 cells to the left using the concatenation operator (&) and a single space between each value:
B5&" "&C5&" "&D5&" "&E5&" "&F5
This part of the formula is annoyingly manual. To speed things up, copy &" "& to the clipboard before you start. Then follow this pattern:
[click] [paste] [click] [paste] [click] [paste]
until you get to the last cell reference. It actually goes pretty past.
The result of this concatenation (before TRIM and SUBSTITUTE run) is a string like this:
"figs apples "
Next, the TRIM function us used to "normalize" all spacing. TRIM automatically strips space at the start and end of a given string, and leaves just one space between all words inside the string. This takes care of extra spaces causes by empty cells.
Finally, SUBSTITUTE is used to replace each space (" ") with a comma and space (", "), returning text like this:
Joining cells with other delimiters
To join cells with another delimiter (separator), just adapt the "new_text" argument inside SUBSTITUTE. For example, to join cells with a forward slash, use:
To join a date with text, you can use concatenation with the TEXT function to control the date format. In the example shown, the formula in E4 is: = "The date is " & TEXT ( B4 , "dddd, mmmm yyyy" ) How this formula works The...
To add a date stamp in a workbook to indicate a "date last updated", you can use the TEXT function. In the example shown, the formula in C5 is: = "Last update: " & TEXT ( B5 , "ddd, mmmm d, yyyy" ) How this formula works The...
To add a line break with a formula, you can use the concatenation operator (&) along with the CHAR function. In the example shown the formula in E4 is: = B4 & CHAR ( 10 ) & C4 & CHAR ( 10 ) & D4 How this formula works This formula "...
The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards.
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.