Excel Combine Rows

Excel is good for handling data, but there are times when you want to combine cells into one value or to split a string to make the data more manageable.

Select the rows you will combine, and click Kutools Content Advanced Combine Rows.

On the Data tab, in the Data Tools group, click Consolidate. In the Function box, click the function that you want Excel to use to consolidate the data. In each source sheet. Select the cells to merge. Select Merge & Center. Important: When you merge multiple cells, the contents of only one cell (the upper-left cell for left-to-right languages, or the upper-right cell for right-to-left languages) appear in the merged cell. The contents of the other cells that you merge are deleted.

Excel has several features which give us the ability to merge and split cells in multiple ways. Here’s how to merge cells in Excel and how to split cells in Excel.

How to merge two or more cells

Every now and then in Excel, you'll need to merge two or more cells in a range. One example may be in the case where a dataset is spread across several columns, and for the sake of presentation, you may want to merge the cells containing the heading or title of that dataset to make them behave like one cell.

At other times, ‘merging a cell’ may mean extracting and actually combining data that are located across several rows or columns and placing them in another row or column.

Let’s talk about how to do both.

Download your free merge/split practice file!

Use this free Excel merge/split file to practice along with the tutorial.

Merge & Center

A quick and common way to merge cells is to use the Merge & Center command in the Home tab.

Simply highlight all the cells to be merged, then click the Merge & Center icon.

In the example above, cells A1 and B1 were highlighted then the Merge & Center command was selected. The result is that both cells now behave like a single cell, which is given center alignment. The cell alignment may be changed if desired.

Limitations of Merge & Center

  • It should be noted that Merge & Center is only a formatting command. Data from multiple cells will not be combined when using Merge & Center.
  • If there is data in the cells being merged, Excel will only keep what is in the upper leftmost cell. If you attempt to merge cells where data is in any other cell, Excel will return the error message, “Merging cells only keeps the upper-left value and discards other values.” In other words, you will lose any data that isn’t in the first cell.
  • Click OK to delete all other data and merge the highlighted cells.
  • Another limitation when you use the Merge & Center option to merge cells is that it also prevents you from being able to sort any data that is located in cells that have been merged.
  • Yet another issue is that highlighting a column that contains merged cells will result in highlighting all the columns spanning the merged cells, not just the column that was selected.
Combine

Alternative to Merge & Center

If you want to merge cells in different columns in a single row, the Center Across Selection offers a good solution. The command is not as easy to find in Excel as the Merge & Center option, which is a pity, but it does allow sorting and highlighting without any issues.

To use Center Across Selection:

Excel
  • Select the cells that you want to merge.
  • Press Control + 1 to open the Format Cells dialog box.
  • In the Alignment tab, from the Horizontal drop-down, select Center Across Selection.
  • Click OK.

Using this method will display the text as though it has been merged and centered, but each cell can still be selected one by one, separate from each other. Cells can also be sorted and highlighted as normal.

If data is in any cell other than the upper leftmost cell, there will be no loss of data. Instead, cells will appear as normal, as though they have not been merged.

Combine text from two or more cells into one cell

Excel combine rows

There are other times when “merging cells” refers to combining the actual data that is in multiple cells into one cell.

This can be accomplished through concatenation.

Three simple methods to concatenate or join values in Excel are shown below. They are:

  1. Using the concatenation operator (& symbol)
  2. Using the CONCAT function
  3. Using the TEXTJOIN function

Each of these methods is designed to join two or more text strings into one string.

Merge cells using the concatenation operator

Using the ampersand (&) symbol between values will join them in a string.

For example, in the dataset below, let’s say we want to have the full name of each individual shown in a single column, column C. Using the & symbol as a concatenation operator is a popular choice because knowledge of function formats is not required although this method is, technically speaking, a formula.

As with all Excel formulas, we would begin with an equal sign. Next, separate each value with the & symbol. Since a space is also likely desired between the first and last names, we would also enter a space within double-quotes.

Merge cells using CONCAT function

The CONCAT function allows the selection of a range and is therefore potentially quicker than using the & symbol, especially when no additional characters are required between the cell values being joined.

This would work well in the case of the dataset below:

The syntax of the CONCAT function is:

We can merge the contents of cells A2 to C2 in cell D2 with the entry:

If additional characters are required between cell values, these would be entered within double quotes and separated by commas as is usually the case with function arguments.

Merge cells using TEXTJOIN function

TEXTJOIN can be considered an improvement in the efficiency of CONCAT since it allows repetitive delimiters (characters between values) to be entered once.

Combine

The syntax of TEXTJOIN is:

The ignore_empty argument is required and is a setting that tells Excel what to do if empty cells occur within the range. If set to TRUE, empty cells are ignored. If set to FALSE, the delimiter is returned nonetheless, resulting in consecutive delimiters with no values in-between.

We can insert dashes between each cell value with the entry:

Click here to learn more about using formulas to merge cells in Excel.

Split one cell into two or more

For those times when you’d like to split a single cell with data into two or more columns, the Text to Columns command may be just the thing you need.

For example, let’s say we have the following name list, and we want the names to be split across two columns.

Method 1 - Split cell using Text to Columns command

We would do the following:

  • Select the cells which contain the text to be split (A1:A15).
  • Click on the Data tab.
  • In the ‘Data Tools’ group, click the ‘Text to Columns’ command.
  • In the Convert Text to Columns Wizard:
    • Step 1 of 3: Select the Delimited radio button. This allows you to use a specified character to determine where the column break(s) should be.
  • Click Next.
  • Step 2 of 3: Select Space as your delimiter. A preview of what your data will look like is shown in the Data Preview section at the bottom of the dialog box.
  • Click Next.
  • Step 3 of 3: The Column data format section allows you to specify the format of each column as General, Text, or Date format. If there is a particular column that you do not want to be imported, select it in the Data preview pane and click ‘Do not import column (skip)’ in the Column data format section. The destination field tells Excel where to place the first cell in your new dataset. The default will always be the first cell of your original data. This, of course, means that your original data will be replaced. If you want to be able to compare your new dataset with the original, choose another cell as your destination. In this case, we will choose cell B1.
  • Click Finish.

The result is that the text in column A has been split across columns B and C, using the spaces as delimiters.

Method 2 - Split cell using Flash Fill command

With Flash Fill, you can teach Excel what you want your data to look like by entering the first two or three rows with the data in the desired format.

Next, click on the last value you entered, then click the Flash Fill icon from the Data tab in the Data Tools command group.

Repeat for each column, and voila! Your one-column data has been split across two columns.

Method 3 - Split cell using a formula

Finally, you can also use the LEFT, MID, and RIGHT formulas to break up the values in cells according to their position within the string.

For example, let us assume that we have the following dataset containing telephone numbers, which we would like to split into three separate columns as follows:

  • The area code, consisting of the first three numbers.
  • The prefix, consisting of the next three numbers.
  • The line number, consisting of the final four numbers.

The syntax of the LEFT function is:

Num_chars is the number of characters in text to return, starting with the leftmost character. If omitted, only the leftmost character is returned.

This formula asks Excel to extract the three leftmost characters from the string in cell A2.

The syntax of the MID function is:

Start_num is the position number of the first character to be returned, counting from the leftmost character in text.

Num_chars is the number of characters in text to return, starting with the leftmost character.

This formula asks Excel to extract three characters from the string in cell A2, starting with the fourth character from the left.

The syntax of the RIGHT function is:

Num_chars is the number of characters in text to return, starting with the rightmost character. If omitted, only the rightmost character is returned.

This formula asks Excel to extract the four rightmost characters from the string in cell A2.

Now we have successfully split the text in one cell into three cells using a formula.

Learn more

With all these different ways to merge and split cells in Excel, including the data in those cells, you’re bound to find one that suits your needs. Have you found any other methods useful? Let us know in the comments below.

You can also check out our course library to learn some other useful techniques in Excel. You can start with our free Excel in an Hour course to cover some basics. Then upgrade your Excel skills with our comprehensive Basic to Advanced Excel course.

Learn Excel for free

Start learning formulas, functions, and time-saving hacks today with this free course!

Start free course

Quickly combine (merge) multiple columns or rows in Excel

Kutools for Excel

Normally to combine / merge columns or rows in Excel, only the contents of first column or row will not be cleared after combining. There is no option for you to do so, if you want to keep all the data (contents) after combining. But with Kutools for Excel’s Combine Rows, Columns or Cells without Losing Data feature, you can quickly process following operations in Excel:

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Click Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data. See screenshot:

Merge or combine Columns without losing data / Merge or combine multiple columns into one column

1. Please select cells which you want to merge based on columns as follows (see screenshot), and then apply the utility (Click Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data).

2. Please specify the settings as follows (see screenshot).

  • Select the Combine columns option in the To combine selected cells according to following options section.
  • Specify a separator to delimit the combined contents.
  • Choose a place from the Place the results to drop-down list;
  • Keep contents of combined cells: it won't delete the contents from the original cells.
    Delete contents of combined cells: it will delete the contents from the original cells.
    Merge the combined cells: it will delete the contents from the original cells and merge them.

3. Click OK. You will see the results according to your specific settings such as follows. See screenshots:

Merge or combine Rows without losting data / Merge or combine multiple rows into one row

1. Please select cells which you want to merge based on rows as follows (see screenshot), and then apply the utility (Click Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data).

2. Please specify the settings as follows (see screenshot).

  • Select the Combine rows option in the To combine selected cells according to following options section.
  • Specify a separator to delimit the combined contents.
  • Choose a place from the Place the results to drop-down list;
  • Keep contents of combined cells: it won't delete the contents from the original cells.
    Delete contents of combined cells: it will delete the contents from the original cells.
    Merge the combined cells: it will delete the contents from the original cells and merge them.

3. Click OK. You will see the results according to your specific settings such as follows. See screenshots:

Merge or combine selected cells (multiple columns and rows) into single cell

1. Please select cells which you want to merge into single as follows (see screenshot), and then apply the utility (Click Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data)..

2. Please specify the settings as follows (see screenshot).

  • Select the Combine into single cell option in the To combine selected cells according to following options section.
  • Specify a separator to delimit the combined contents.
  • Click the OK button.

3. You will see the results according to your specific settings such as follows. See screenshots:

Notes:

This utility supports CTRL+Z undo.

Excel combine rows into one cell

Use formatted values: it will keep the formatting of the values after merging the cells. For example, if the value in cell is '100', then you apply cell formatting to this cell and change the '100' into '$100.00 USD', so the shown value in cell is '$100.00 USD'. If you checked this 'Use formatted values' option, the merged content will be '$100.00 USD'; if unchecked this option, the merged content will be '100'.

Demo: combine (merge) multiple columns or rows in Excel


Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!
Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

Excel Combine Rows With Same Value

or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Excel Combine Rows To Cell

  • To post as a guest, your comment is unpublished.
    i have an excel file with table of values. I need to find all cells with name and lower marks in row and other rows with the same name and higher marks must be removed. Can i use bulid in excel vba or i can use kutool to solve this problem? it is not combine but how do we call this operation?
  • To post as a guest, your comment is unpublished.
    Can I set this up to combine automatically?
    Meaning - if I add a row and input date into Column A and Column B - can I set it up to automatically combine into Column C without running the combine utility every time?
    • To post as a guest, your comment is unpublished.
      Did this ever get answered. I do 20 to 50 'Advance Combine Rows' per worksheet. There should be a way to save the settings. (IE Column B = Primary Key, Column C = Primary Key, Column D = Primary Key, Column E = Sum) Simple formula but it should be 1 click 30 times instead of 11 Click 30 Times. ' Macros do not work.
  • To post as a guest, your comment is unpublished.
    Can this be set up to automatically combine? If I add more rows and put data into column a and column b - can I set it up to automatically combine into column c without having to run the combine utility every time?
  • To post as a guest, your comment is unpublished.
    Hi; I have 10,000 rows of data. In column A there are product numbers. Each product has anywhere from 3 to 13 rows of data all for one product. I have about 35 columns I need to get all the rows of data into the first row of that product number. I am losing my mind! Can you help?
    • To post as a guest, your comment is unpublished.
      Have you received an answer on this yet?
  • To post as a guest, your comment is unpublished.
    i have to lines of data where i need to merge certain cell to create 1 line