Tip: Other languages are Google-Translated. You can visit the English version of this link.
or

Register

or
0
0
0
s2smodern

## 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 separator by formula

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

 Combine multiple columns, rows or cells with sepcific separator: If there are multiple rows or columns or cells of values needed to be merged, the normal formula will be troublesome for dealing with it. But, with Kutools for Excel's powerful feature - Combine, you can quickly combine the values based on columns, rows or cells as you need. Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.

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

###### Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

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

Typing the formula = A1&" "&B1&" "&C1 into a blank cell, and then drag the fill handle down to apply this formula, all of the row cells have been combined into a cell 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&" "&A5&" "&A6  in adjacent blank cell that you want to get the result. And you will get the following result:

Combine cells based on row or column into one with commas

Typing the formula =A1&","&B1&","&C1 into a blank cell, then drag 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&","&A5&","&A6 to get the following result as you need.

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

#### Merge row / column of cells into one cell with specified Separator 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 + F11 keys 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 specific separator:

```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 then drag 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 dashes. 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:A6) to get the merged data as you need, see screenshot:

Note: In the above code "Function Combine(WorkRng As Range, Optional Sign As String = "-") As String", you can change the separator "-" to meet your need.

#### Merge row / column / range of cells into one cell with specified separator 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.

 : with more than 120 handy Excel add-ins, free to try with no limitation in 60 days.

After installing Kutools for Excel, please do as below:

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:

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

(1.) 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 cell, do as follows:

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

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

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

D. Select one type from the Options section.

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

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

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

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

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

D. 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 screenshot:

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

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

B. 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:

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.

### Recommended Productivity Tools

#### Office Tab

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

#### Kutools for Excel

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

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,

Say something here...
symbols left.
###### or post as a guest, but your post won't be published automatically.
People in conversation:
• To post as a guest, your comment is unpublished.
· 4 months ago
I would like to know if I have a list of people with their information in different cells in one column but different row how to combine them in one cell For e,g I have one person teaching English, math and science and his name is repeated in one column but the courses are in different rows so I want to combine all the courses in one cell for that teacher. if I have 50 teachers I need to have all the courses that the teacher is teaching in one cell.
• To post as a guest, your comment is unpublished.
· 4 months ago
Hi, Lavina,
Do you mean to combine multiple cell values in a column based on duplicate names in another column as below screenshot shown:

You can insert a screenshot to make your problem more intuitive.
• To post as a guest, your comment is unpublished.
· 4 months ago
VBA doesn't work for me, excel 2016.
• To post as a guest, your comment is unpublished.
· 9 months ago
Working on trying to get your VBA code to work however I keep getting a #NAME? error. How do I fix this? I've used this code before and it worked wonderfully but now it gives me an error.
• To post as a guest, your comment is unpublished.
· 1 years ago
But it is not working for large data. I have around 50000 rows to combine in single cell.
• To post as a guest, your comment is unpublished.
· 1 years ago
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