Merge Data Into One Cell

 admin

To merge data in Excel using this method, open your Excel files and, in the target Excel file for merging data, create a new worksheet by pressing the + (plus) button next to the sheet tabs at the bottom of the window. In your new worksheet, press Data Consolidate. To combine text from two or more cells into a single cell, use the CONCATENATE function, which you may enter in Google Sheets on the web (Figure D) as well as the mobile apps. Run Excel Consolidate. In the master worksheet, click the upper-left cell where you want the consolidated data to appear, go to the Data tab and click Consolidate.

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)...

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

Or
ASAP Utilities » Columns & Rows » Merge row data, separated 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?

Merge Data Into One CellMerge Data Into One Cell
  • 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?

Merge Data Into One Cell

Merge Data From Two Cells Into One

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.

Merge Cells And Keep Data

Download

How To Merge Two Cells Into One

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