Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to count table columns in Excel
  • How To Filter Data in Excel
  • How to combine 2 or more chart types in a single chart in Excel
  • Randomize/ Shuffle List in Excel
  • How To Perform and Interpret Regression Analysis in Excel

References

  • How to get address of named range in Excel
  • How to use Excel TRANSPOSE Function
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to get last row in mixed data with blanks in Excel

Data Validations

  • Excel Data validation specific characters only
  • Excel Data validation number multiple 100
  • Excel Data validation only dates between
  • Prevent invalid data entering in specific cells
  • Excel Data validation exists in list

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:   How to count line breaks in cell in Excel worksheet
[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)

Post navigation

Previous Post:

Put names into proper case — Manipulating NAMES in Excel

Next Post:

Create One-dimensional and Two-dimensional Array

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

Logical Functions

  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • How to return blank in place of #DIV/0! error in Excel
  • Not Equal To ‘<>‘ operator in Excel
  • How to use Excel XOR Function
  • How to use Excel TRUE Function

Date Time

  • Calculate total hours that fall between two times in Excel
  • How to get same date next year or previous year in Excel
  • Convert Excel time to decimal seconds
  • Sum race time splits in Excel
  • DAY function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Map text to numbers in Excel
  • Group times into unequal buckets in Excel
  • How to randomly assign people to groups in Excel
  • Group times into 3 hour buckets in Excel
  • Calculate conditional mode with criteria in Excel

General

  • Convert column number to letter in Excel
  • How to calculate percent sold in Excel
  • Excel Operators
  • How to create dynamic named range with INDEX in Excel
  • List sheet names with formula in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning