MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Combining data from two fields into one

Posted by Brian Griebel on February 13, 2001 9:13 AM

We have recently purchased a mail machine that allows us to import Excel files. Our problem is that the requirement for the street address is that it is all in one cell. For instance "123 Happy St.". Our existing database only allows us to export it as two seperate fields, for instance "123" and "Happy St.". We are trying to merge the data together with out going through the 10,000 addresses that we have on file manually by exporting to a tab delimited text document & manually deleting the spaces in between, then importing the file back in to Excel. The only thing that I have found that seems to apply to our problem is the cell merge feature which gives us the message of "The selection contains multiple data Values. Merging into one cell will keep the upper-left most data only". If I proceed, it deletes all of the data. Please help me. Thank You.

Posted by Aladin Akyurek on February 13, 2001 9:43 AM

If you get street numbers (first part of the addres), say, in column A from A1 to A10000 and street names in B1:B10000 when you directly import the address data to Excel, type in C1

=A1:A10000&" "&B1:B10000

And while still in C1, select C2:C10000, then go to the formula bar, hit control+shift+enter at the same time.

This will give you 10000 addresses each one in one piece in column C.


Posted by Dave Hawley on February 14, 2001 12:44 AM

Hmmm, I would enter: =A1 & " " & B1 in C1, double click the fill handle and Copy then PasteSpecial as Values.


OzGrid Business Applications