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
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
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
You will see that there are multiple empty string between words.
=TRIM(A1&” “&B1&” “&C1)
This formula removes empty strings from the text string and just leaves one space between all words.
The SUBSTITUTE function replaces all space characters with commas.
- 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? .…
- 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)….