Merging cells in Excel or Google Drive / Docs without losing data!

Last Updated on August 19, 2023

I’m currently compiling information gathered from business cards collected at a trade fair into a spreadsheet so that I can import them into Google Contacts (because I use Gmail and Google Drive for CRM) and hit a snag.

My colleague kindly typed all the contact information from the many business cards I collected into a Google Spreadsheet for me, but for the postal address field, my colleague entered each line of the address into a different column on the spreadsheet; this is a problem because to import the contact data into Google Contacts (by first saving / downloading the spreadsheet as a CSV file) the entire address needs to be in a single column.  So the information currently spread across three columns needs to be merged into one column.  The problem being that when you merge cells in a spreadsheet, the software only retains the information you have in the left hand column of the selection.

How to merge cells/columns in a Excel or Google Drive spreadsheet without loosing data.

1. Create/insert an additional column after the columns you wish to merge
2. In the top cell of the new column, write the following formula making sure to list each of the cells you wish to merge =concatenate(A1,B1,C1) 
3. When you hit ‘enter’ you should see the data from each of the cells in the formula, if you’re happy with the way the data is displayed then use the ‘fill down’ function to spread the love throughout your column (fnar fnar)

Formatting concatenated data.

To insert spacing (or other characters) into a cell that contains concatenated (merged) data you need to insert some speech marks into your formula between the cells that you have listed in the formula.   So the formula mentioned above with spaces inserted between the merged cell data would become achieved with the following formula =concatenate(A1,” “,B1,” “,C1)

So anything you place between the speech marks will be added to your merged data cell.