List of Addresses

  • Thread starter Thread starter JC in HFX
  • Start date Start date
J

JC in HFX

I have one column that contains Names and addresses. I
want the information converted into colums that contain
the
Name, Address, City Postal code etc. in separate
columns. I have don this before but so long ago that I
have forgotten the method.

Please Help.
 
I'd start with selecting the column and going to Data >
Text to columns.

HTH
Jason
Atlanta, GA
 
The process depends very much on the format of the data

John Doe, 124 Main St., Somewhere, NY 10048

Can be separated easily used Data/Text To Columns with the "," as the
delimiter, Then use a second iteration on the last column to separate out
the Zip Code

Other formats may not be quite that simple and it is most likely that
whatever the format, there will be a few "errors" that have to be handled
manually.
 
Is the format consistent? For example, does each address occupy the
same number of cells? If so, how many? And is there a blank cell
between addresses?
 
-----Original Message-----
Is the format consistent? For example, does each address occupy the
same number of cells? If so, how many? And is there a blank cell
between addresses?


.
Click to expand...

Example
Mr. John Doe
123 Main Street
City
Postal Code

Most are in 4 rows (one column0, some have 5, 6 and
seven rows.
 
-----Original Message-----
Is the format consistent? For example, does each address occupy the
same number of cells? If so, how many? And is there a blank cell
between addresses?


.
Click to expand...

Example
Mr. John Doe
123 Main Street
City
Postal Code

Most are in 4 rows (one column0, some have 5, 6 and
seven rows.
 
Unfortunately, since the format is not consistent, I won't be able to
help. I think your problem may need a VBA solution, something I'm not
familiar with. Hopefully, someone else will be able to help.

Cheers!
 
A real example would have been better, in your new
example you don't indicate what separates the rows
if everything is 8 rows as you might actually have if
you were printing labels, or if a single empty row separates.
You will need a VBA solution and you have to be able to
figure out how to solve the problem manually, in order to
have a program do the work.

There are several solutions on the following page, see
if that helps.
http://www.mvps.org/dmcritchie/excel/snakecols.htm
and specifically the code which assumes a blank row in-between
if you reply with 0 for the number of rows:
http://www.mvps.org/dmcritchie/excel/code/naddr3ss.txt

Instructions to install a macro can be found on my getstarted.htm page.
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top