Easy ways to combine the contents of multiple cells

Save 5 minutes a day by using ASAP Utilities to quickly combine the contents of multiple cells into once cell.

“Is there a quick way to merge the contents of multiple cells?”

We were recently asked: “Does ASAP have a quick alternative for merging the contents of many cells into a single one? Using ampersands or Excel’s =concatenate() formula can become cumbersome when it involves a lot of cells.”

Yes we have a few tools available to make it easier to merge the contents of many cells at once.
For example, you can use the following tool to merged the contents of the cells side by side:
ASAP Utilities » Columns & Rows » Merge column data (join cells)…

Merge column data (join cells)

To merge the data from cells from top to bottom, you can use:
ASAP Utilities » Columns & Rows » Merge row data (selected cells)…

Combine the data from the rows in your selection

Or
ASAP Utilities » Columns & Rows » Merge row data, separated cells by a line break

Merge row data, separate cells by a line break

And, for maximum flexibility in the specified range, you can use the function =ASAPMERGECELLS() from the ASAP Utilities functions library.
For example =ASAPMERGECELLS(C3:AD3;”-“), merges the values of every cell in range C3:AD3 and separates the values with a – character.
Compared to Excel’s =CONCATENATE() function this new function has the benefit that you can specify an entire range, and not just the values of the cells are used, but the values as you see them (cell’s number formatting).

Do you recognize any of these situations?

  • I was working today on an excel file and came across a requirement which is not (I think) in ASAP utilities arsenal: a command which can connect text strings from different cells.
  • I do a lot of data manipulation which involves “stacking” columns of data sometimes. It would be very handy if you could include a command that can perform such manipulation in your next edition of ASAP. Thanks
  • Is there an existing ASAP function that will take data from multiple columns and combine it all into a single column? The only alternative I know of is to sort each row to the top and then manually combine, which is very time-consuming.
  • I have an Excel worksheet which contains numbers in Cell A1 to X1. I need to merge all the numbers in those cells into one single cell but each number need to be separated by the “|” sign.
    I know the Concatenate formula (=concatenate(A1,”|”,B1,”|”,C1…..) can do this but it will be tedious to write, given I am merging a large number of cells. Is there a variation to this formula that can perform this task? Or maybe a different formula?
  • I have a file in Excel where the zip code is broken into 2 cells. The 5 digit part of the zip code is in column D, the 4 digit part of the zip code is in column E. I need to join them into a single cell.
  • I have a spreadsheet that has text in multiple cells across a row. I need to have a space between each character in the row. I thought I could merge the cells in each row into one cell, but Excel only wants to keep the contents of the first cell if I click on the ‘merge and center’ button.
  • I know Excel its Data > Text to Columns, but I need to do the reverse, merge data from the columns back into a single column. Does that exist?

Such tasks can be easily accomplished with one the following tools:
ASAP Utilities » Columns & Rows » Merge column data (join cells)…
ASAP Utilities » Columns & Rows » Merge row data (selected cells)…
ASAP Utilities » Columns & Rows » Merge row data, separated cells by a line break
or the function =ASAPMERGECELLS() from the ASAP Utilities functions library.

This often a quicker alternative than using Excel’s =CONCATENATE() function or using the ampersand (&) character in a formula.

How much time will it save?

It’s guaranteed that you’ll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working with and how often you use this particular tool.
You can easily see how much time ASAP Utilities has saved you so far.

Download

In case you don’t have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial.
Download page

Please wait...

Subscribe to ExcelMOOC posts

Want to be notified when our Excel article is published? Enter your email address and name below to know all new Excel features and cool tips