Address over many columns?

Microsoft Office excel 2007 home & stude...
June 14, 2010 at 06:41:31
Specs: Windows Vista
I have the addresses of 300 clients in one
column in excel.
(They are in separate rows)
I need to separate the address into various
different columns - street, City, Post/Zip Code
Is there any way to do this automatically??
Can i email the data to anyone who can

Thank you sooo much in advance!

See More: Address over many columns?

Report •

June 15, 2010 at 13:54:10
Are the Address's uniform and consistent?

If you give some examples we may be able to help better.


Report •

June 15, 2010 at 15:16:02
Under the Data menu, look at Text To Columns.

You might be able to use a Space as the delimiter, or you might be able to use Fixed Width.

As Mike hinted at, it all depends on how your data is laid out in the cells.

You might have to separate items out into multiple cells, and then put certain cells back together with the Concatenation operator. &

Again the current layout of the data is key.

Report •

June 16, 2010 at 00:15:44
Thanks Mike and DerbyDad!
The problem is the addresses are not separated by comma's
or anything consistent really! They are not of fixed width
I have been putting commas in each record and then using
the text to columns option in MS Excel.
I'll give you an example of the data:
'152 HighStreet Colchester South East - Essex CO11PN
I tried to separate it by spaces, but that did not work either as
some of the records do not have spaces between them.

Report •

Related Solutions

June 16, 2010 at 09:10:25
some of the records do not have spaces between them

Without some type of delimiter, either a space or a comma or some other character, your going to have a difficult time of it.

The best I can think of would be to
put the whole list in Word,
and start adding spaces where necessary
then import it into Excel
and use the Text To Columns to break the address into its component parts
and reassemble in the order you want.


Report •

June 16, 2010 at 09:35:54
re: '152 HighStreet Colchester South East - Essex CO11PN

In cases where you don't have spaces, I can think of 2 possible solutions:

1 - Use Substitute to add a space, e.g.

=SUBSTITUTE(A1,"Street"," Street") then use Text to Columns

2 - Use VBA to search the string, character by character for upper case letters and split the cells when it finds one that is not preceded by a space.

Keep in mind that Excel was not designed nor intended to be used as a "text editor". It's happiest when playing with numbers. The designers were nice enough to supply some text-related functions, but it's easy to understand why they are limited in scope.

Report •

Ask Question