Addresses in Excel

[tell-me-more]

Me again.

Does anyone know a shortcut way of separating addresses that are in one cell into two cells - one identifying the building(s), the other the street or whatever?

It's not as simple as using left or text to columns to remove the first two characters because there are house names etc. to contend with.

I don't imagine there's anything in Excel to deal with addresses specifically, but if anyone's created their own way round the problem, would be useful to know about it, and I imagine I'm not the only one who could benefit.

Cheers. Have a good day.

TMM

[EuanC]

Try posting your question on here.

http://www.mrexcel.com/board2/login.php

I use it sometimes and the folks there are real helpful.

[brachiopod]

Depends on your data, but if all the addresses are in the format;

52, CREDIBILITY STREET
VERYTWEE COTTAGE, LEAFY LANE
DUNANSWERBANKIN', NOWHERE AVENUE

etc. etc., then you could try Text-to-columns using a comma " , " as the delimiter. It does, of course, require that each address first-line has a comma between the number / house name / building and the next part of the address.

You could just try it, and have a quick scan for any failed results and correct those manually - though this, of course, depends on the size of your database !!

[alfiebrady]

Sorry to be negative, but I wouldn't put too much effort into this; unless all your addresses follow a very strict and simple format*, it will be very hard to get a formula that splits all of them as you want.

* take a look in your phone book - won't take you long to find an address that'll give you formatting headaches.

[tell-me-more]

Cheers for all the answers. I expected as much Alfie; I just wondered if someone had managed to find time and put enough effort in to find a better solution to the ones I've already got.

Brachiopod, no such luck unfortunately.

Thanks for the link, Euan.