Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to retrieve first match between two ranges in Excel
  • How to get first row number in range in Excel
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Multi-criteria lookup and transpose in Excel
  • How to use Excel TRANSPOSE Function

Data Analysis Examples

  • Filter Data Based on Date in Excel
  • How to Create Gantt Chart in Excel
  • Everything about Charts in Excel
  • How To Insert and Customize Sparklines in Excel
  • How To Create Pareto Chart in Excel

Data Validation Examples

  • Excel Data validation require unique number
  • Excel Data validation only dates between
  • Excel Data validation must begin with
  • Excel Data validation must not contain
  • Excel Data validation must contain specific text

Join cells with comma in Excel

by

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.

Formula

=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1)," ",", ")

Explanation

In the example shown, the formula in G5 is:

=SUBSTITUTE(TRIM(B5&" "&C5&" "&D5&" "&E5&" "&F5)," ",", ")

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:

Worked Example:   Count Number of Words in Excel
[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.

"figs apples"

Finally, SUBSTITUTE is used to replace each space (” “) with a comma and space (“, “), returning text like this:

"figs, apples"

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:

=SUBSTITUTE(TRIM(B7&" "&C7&" "&D7&" "&E7&" "&F7)," ","/")

The output will look like this:

limes/apricots/apricots/limes/figs

USING TEXTJOIN Function

New in Excel 2016 is the TEXTJOIN function, which allows you to concatenate a range of cells with a delimiter. With, TEXTJOIN, the example above would look like this:

=TEXTJOIN(", ",TRUE,B5:F5)
Worked Example:   SUBSTITUTE function: Description, Usage, Syntax, Examples and Explanation

Post navigation

Previous Post:

How to extract text between parentheses in Excel

Next Post:

Extract most frequently occurring text in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Remove text by position in a cell in Excel
  • How to Remove Leading Spaces in Excel
  • How to strip html from text or numbers in Excel
  • How to abbreviate names or words in Excel
  • How to get first word in Excel
  • Create date range from two dates in Excel
  • How to calculate project start date based on end date in Excel
  • ISOWEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • How to get same date next year or previous year in Excel
  • DATE function: Description, Usage, Syntax, Examples and Explanation
  • SYD function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest for given period in Excel
  • Example of payment for annuity in Excel
  • XIRR function: Description, Usage, Syntax, Examples and Explanation
  • FV function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles