How to join text from two or more cells into one cell separated by commas, space

This post explains that how to join multiple cells into one cell with commas or space character in excel.  How to combine text from two or more cells into one cell with space, commas or others specific characters as delimiter.

Join text from two or more cells with commas

You can merge text from two or more cells into one cell using a combination of the SUBSTITUTE function, the TRIM function and concatenation operator to create an excel formula.

For example, if you just want to combine cells from a range A1:C1 to a cell with commas, just use concatenation operator as follows:

=A1&", "&B1&", "&C1

join cells with commas1

If some of values in Cells are empty, you can use the concatenation operator to join text from all cells, and then you need to use TRIM function to remove all space character from text string except for single space between

All words. Then you can use the SUBSTITUTE function to replace all empty strings with comma.

So you can use the following excel formula:

=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1))

Let’s see how this formula works:

= A1&” “&B1&” “&C1

join cells with commas2

You will see that there are multiple empty string between words.

 

=TRIM(A1&” “&B1&” “&C1)

join cells with commas2

This formula removes empty strings from the text string and just leaves one space between all words.

 

=SUBSTITUTE(TRIM(A1&” “&B1&” “&C1),” “,”,”)

join cells with commas4

The SUBSTITUTE function replaces all space characters with commas.


Related Formulas

  • Split Multiple Lines from a Cell into Rows
    If you have multiple lines in a cell and each line is separated by line break character or press “alt + enter” key while you entering the text string into cells, and you need to extract the multiple lines into the separated rows or columns, you can use a combination with the TRIM function, the MID function, the SUBSTITUTE function, the REPT function, the LEN function to create a complex excel formula..…
  • The difference between Replace function and Substitute function in Excel
    There are two similar functions to replace text string in Excel. They are REPLACE and SUBSTITUTE functions. What’s the difference between REPLACE function and SUBSTITUTE function in excel 2013? .…

Related Functions

  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • Excel TRIM function
    The Excel TRIM function removes all spaces from text string except for single spaces between words.  You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)….

 

Comments

So empty here ... leave a comment!

Leave a Reply

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

Sidebar