How to combine cells into a cell with space, commas and semicolon in Excel?

When you combine the cells of a range into a single cell, the combined data may be separated by nothing. But if you want to separate them with specified marks, such as space, commas, semicolon or others, how can you do? This tutorial will introduce some cuts for you.

Merge row / column of cells into one cell with specified marks by formula

Merge row / column of cells into one cell with specified marks by Function

Merge row / column / range of cells into one cell with specified marks by Kutools for Excel


arrow blue right bubble Merge row / column of cells into one cell with specified marks by formula


In Excel, you can use a formula to combine a row of cells or a column of cells into a single cell. Please do as follows:

Combine cells based on row or column into one with space

Step 1. Typing the formula = A1&" "&B1&" "&C1 into a blank cell, and press Enter button on the keyboard. See screenshot:

Step 2. Dragging the fill handle over the range that you want to apply this formula, all of the cells have been combined into a cell based on row values with spaces. See screenshot:

Tip: If you want to merge the cell values based on column, you just need to enter this formula = A1&" "&A2&" "&A3&" "&A4 in adjacent blank cell that you want to get the result. Then do as the above steps, you will get the following results:

Combine cells based on row or column into one with commas

Step 1. Typing the formula =A1&","&B1&","&C1 into a blank cell, and press Enter button on the keyboard. See screenshot:

Step 2. Dragging the fill handle over the range that you want to apply this formula, all of the cells in a row will be combined into a cell with commas. See screenshot:

Tip: You can also merge the column of cell values into one cell with commas, just type this formula =A1&","&A2&","&A3&””&A4.Then do with above steps.

Note: you can change "'," to meet your needs. For example, if you want to separate the data with semicolons, you can change "," to ";".


arrow blue right bubble Merge row / column of cells into one cell with specified marks by Function

In Excel, you can also use function to combine cells of row or column into a single cell with spaces or specified marks.

1. Hold ALT button and press F11 on the keyboard to open a Microsoft Visual Basic for Application window.

2. Click Insert > Module, and copy the VBA into the module.

VBA: Combine cells based on row or column into one with commas:

Function Combine(WorkRng As Range, Optional Sign As String = ",") As String
'Update 20130815
Dim Rng As Range
Dim OutStr As String
For Each Rng In WorkRng
    If Rng.Text <> "," Then
        OutStr = OutStr & Rng.Text & Sign
    End If
Next
Combine = Left(OutStr, Len(OutStr) - 1)
End Function

3. Typing formula =Combine(A1:C1) in a blank cell, and press Enter button in the keyboard, and the data in cell A1 to C1 will be merged into a cell and separated by commas. See screenshot:

4. Dragging the fill handle over the range that you want to apply this formula, all of the cells in the row will be combined into a cell with commas. See screenshot:

Tip: With above User Defined Function, you can also combine cell values based on column, you just need to enter this formula =Combine(A1:A4) after copying above code into the Module.

Note: You can change "," to meet your need. For example, if you want to merge cells into a cell with space, you can change "," to" ".


arrow blue right bubble Merge row / column / range of cells into one cell with specified marks by Kutools for Excel

If formula and function are difficult for you, you can quickly combine cell values by row, column or a range into one single cell with Kutools for Excel's Combine function.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Read More   Free Download Now

1. Select a range cell values that you want to combine into a cell.

2. Please apply Combine function by clicking Kutools > Combine. See screenshot:

doc combine cells with commas12

3. And a dialog will be displayed, and then do as follows:

(1.) If you need to combine a range of cell data into one single cell, please do as this:

Check Combine into single cell in To combine selected cells according to following options section;

Select the separator you want to separate the combined data in Specify a separator section.

Then click OK or Apply button, and you will get the following result, see screenshot:

doc combine cells with commas13
doc-arrow-down

(2.) If you select Combine columns in the To combine selected cells according to following options section in the Combine Columns or Rows dialog, the data in each row will be merged into a single cell, do as follows:

Check Combine columns in the To combine selected cells according to following options section;

Select the separator you want to separate the combined data in Specify a separator section;

Select the place you want to put the result in from the drop-down box;

Select one type from the Options section.

Then click OK or Apply button, the values in each row will be merged. See screenshots:

doc combine cells with commas14
doc-arrow-down

(3.) And if you want to merge each column values to one cell with specific separator, please do as this:

Check Combine rows in the To combine selected cells according to following options section;

Specify a separator you want to separate the combined data in Specify a separator section; 

Select the place you want to put the result in from the drop-down box;

Select one type from the Options section. 

Then click OK or Apply button, the values in each column will be merged and they are separated by the specific marks. See screenshots:

doc combine cells with commas15
doc-arrow-down

Kutools for Excel's Combine function also can merge cells base on columns. Click here to know more about Combine. Free Download Kutools for Excel Now


Relative articles:

 

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more btn download btn purchase

Comments  

Permalink 0 Raghad
Thank you a lot. I found this very useful.
Regards,
Raghad
2014-04-03 06:34 Reply Reply with quote Quote
Permalink 0 Charuta
awesome: helped with my project and saved many hours of work...thanks much.
2014-04-14 01:23 Reply Reply with quote Quote
Permalink +1 fmanjeim
Great, limpidly clear
2014-04-25 20:53 Reply Reply with quote Quote
Permalink +1 Zygis
Thank you. This was really a savior! I have one question. I want to merge EAN codes. It works like a charm in the same sheet. But when I take EAN codes from another sheet, I get the result:

#######,########,#######,#######

Where might be the problem?

many thanks
2014-09-23 20:05 Reply Reply with quote Quote
Permalink 0 admin_jay
Hello, please be more specific about your issue.

Try to contact me at jaychivo#extend office.com. Please replace # with @.

:-)
2014-09-24 02:22 Reply Reply with quote Quote
Permalink 0 Kristi
Hi!

I have a spreadsheet of over 23,000 serial numbers in Column A, then each serial number has a one-digit Assign number in Column B, and finally an individualized Application number that belongs to both the serial number and assigned number in Column C.

Looks like this:

A B C
123456 1 1212121
987655 2 5656565
606060 1 4343434
606060 1 8989898

How would I combine just the rows of data that have the SAME Serial number and Assign number but different application numbers using a comma?

Such as, I need:

606060 1 4343434, 8989898

Any help would be super appreciated!!!
2017-01-27 15:35 Reply Reply with quote Quote
Permalink +1 Annette
I used your VBA formula a few days ago and it worked beautifully. I'm using it again today and I keep getting a #NAME? error. I've tried all sorts of things and can't get it to work. What am I doing wrong?
2014-10-31 13:22 Reply Reply with quote Quote
Permalink 0 Srina
Hi,
thanks so much for the helpful resource!!!
May I ask how to modify the VBA Module to have both "," and " "?
Thank you so much!
2015-01-09 06:30 Reply Reply with quote Quote
Permalink 0 Srina
Quoting Srina:
Hi,
thanks so much for the helpful resource!!!
May I ask how to modify the VBA Module to have both "," and " "?
Thank you so much!


Most of all, how to end the list with no space and no comma? So to select entire rows of a spreadsheet as resource, which might include a different number of columns?

Please help, as Kutools is not compatible with Mac.

Thanks in advance!
2015-01-13 18:47 Reply Reply with quote Quote
Permalink +1 Pradeep
Thanks for the VBA code!!!!It saved my time......... :-)
2015-01-09 19:29 Reply Reply with quote Quote
Permalink 0 sandeep melwan
i want to merage two cells with the following results
A and B = A ,B
means A column space comma B column
2015-04-24 04:34 Reply Reply with quote Quote
Permalink 0 R Hammer
Thank you. That is the best explanation I have found yet! I am having trouble expanding this over hundreds of columns though. Can you clarify the point where you say "Dragging the fill handle over the range that you want to apply this formula, all of the cells in a row will be combined into a cell with commas" I am not sure how to do this.
2015-05-08 14:02 Reply Reply with quote Quote
Permalink 0 Subbu
Thanks a lot.. this info is very helpful.
2015-05-13 14:06 Reply Reply with quote Quote
Permalink +1 cara
I am having issues, the cells G2-N2 (down thousands of rows) need to be merged to one column using a comma but no space between them. However, if the cell is EMPTY, it needs to SKIP the cell, with no input of a comma. I cannot find any HELP menu for this and I am up against a deadline and need this shortcut for thousands of products I am importing. Any and all help appreciated.
2015-06-24 15:50 Reply Reply with quote Quote
Permalink 0 Oscar Bolanos
Thanks for the macro, it worked great!!
2015-12-29 22:25 Reply Reply with quote Quote
Permalink 0 Sawan Sharma
Thanks a ton VBA save my hours....!!!
2016-04-22 05:57 Reply Reply with quote Quote
Permalink 0 Mark
How do i get results show the following values:
A B C
10 10 =CONCATENATE(A1 ,",",B1,",",C1) RESULTS 10,,10
10 20 30 =CONCATENATE(A1 ,",",B1,",",C1) RESULTS 10,20,10
10 30 =CONCATENATE(A1 ,",",B1,",",C1) RESULTS 10,30,

ABOVE IT IS SHOWING COMMA IN BETWEEN TWO VALUES OR AFTER. I DONT WANT THE COMMA WHERE THE VALUE IS NOT THERE
2016-04-30 10:03 Reply Reply with quote Quote
Permalink 0 Rob
Try this.

=SUBSTITUTE(IF(A1="","",A1&",") & IF(B1="","",B1) & IF(C1="","","," & C1),",,",",")
2017-02-22 02:41 Reply Reply with quote Quote
Permalink 0 laura
the merge formula was great, except one of my columns of data were dates, and the dates had to be formatted MM/DD/YY - but once merged, the date converted to a decimal figure. I couldn't use the formula.
2016-09-22 04:01 Reply Reply with quote Quote
Permalink 0 Charles
The VBA macro worked very well after I adjusted for the my cell locations and I was able to add a space after the comma for a better display of the data.
2016-12-05 19:53 Reply Reply with quote Quote
Permalink 0 Jake
The VBA code worked really well thanks.

My only issue is that it is including blanks so that my combined output ends up looking like this: "test, test, , , , , , test"

How could I get it to exclude blank cells within the range?
2016-12-20 11:38 Reply Reply with quote Quote
Permalink 0 Sandeep K
how to combine 2 cell with space in between with 2 independent cell format. Eg. If one cell is Red digits & other cell has digits in green, it should combine with Red & green digits.
2017-01-16 06:48 Reply Reply with quote Quote
Permalink 0 Smithc114
I genuinely enjoy studying on this website, it holds good content. Never fight an inanimate object. by P. J. O'Rourke. dfdkbafbadfkagd d
2017-02-04 07:40 Reply Reply with quote Quote
Permalink 0 Joe
The VBA code does not work

I get an error Microsoft Visual Basic for Applications - Compile error: Syntax error

then the line "If Rng.Text "," Then" is highlighted in blue and on the top highlighted in yellow it says this "function combine(workrng as range, optional sign as string = ",") as string

I am using excel 2016 on a pc
2017-02-27 20:17 Reply Reply with quote Quote
Permalink 0 susan
415xxe 5687sdc sa4567
415xxe 5687sdc sa4567
415xxe 5687sdc sa4567
415xxe 5687sdc sa4567

I need to separate these in different columns and then put a comma behind the last column
2017-03-13 19:11 Reply Reply with quote Quote

Add comment


Security code
Refresh