Address information in Excel

[Matt27]

I have a spreadsheet with two columns of information. The first column contains an account number which is fine. The second column contains an address, which is unformatted. Some of the addresses start with a comma, so for example it may start ", 25 Harlow Avenue, ". Other addresses may start as I would want them to start, for example "Flat 42, ". Some of the addresses also have 2 or 3 commas in the middle of the address. Is there any simple way I can split the address out, so I have the house number and road in one cell (e.g. B1), the town in the next cell (e.g. C1), the county in the following cell (e.g. D1) and the postcode in the final cell (e.g. E1) AND remove the commas at the same time??

 

[SkipVought]

Hi,

Is there any way??????

What you have is a mess. Is this a one shot deal or something that you get on a periodiodic basis?

This sort of mess, takes alot of analysis in order to 1) find the problems and then 2) caregorize the problems and then 3) fix the problems. It really depends on how many diffeent types of problems there are, how many there are (how many rows) and how often you have to do it.

So, is there a way? Maybe, probably, but who know how much it will take without knowing the answers to the questions.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[xlbo]

If this is a one shot job there are things you can do to make it a bit easier but unless there is some sort of logic involved, it's going to be a very manual process.

Formulae can only work on logical progression so unless there is a pattern (or patterns), it's going to be difficult to do anything than manually weed out the errors

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Matt27]

Thanks for your replies, I didn't think it was going to be straightforward having spent some time trying to think of a solution!!

This is going to be a file we will receive periodically, hence the need to automate it as much as possible. In the file I have at the moment there are just over 600 rows - this may vary each time I receive a file but shouldn't be more than a few hundred rows every time. Any help would be much appreciated!!

 

[SkipVought]

If this will be ongoing, I'd suggest posting the question in Forum707 because, you'll need a macro to repeat the process.

Be preapred to post samples of data.

Be prepared to do alot of this yourself, using the macro recorder and then getting help customizing your recorded code.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[Matt27]

Ok I'll do that, thanks for your help.

 

[1726]

If I read it correctly, it sounds as if you have the addresses in comma delimited format, where some of the fields are blank.

You might experiment with separating the data into multiple columns using:

Data > Text to Columns

The quality of the results will depend upon the field structure of the source data!

 

[AnotherHiggins]

Is there any way you can have the source cleaned up? What application is this data coming out of? Is it from someone in your company, or from another company?



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Matt27]

I believe this is how we're going to receive the information and it's being left to myself to clean it up. I'm not sure what application it's coming from, but it is coming from another company.

 

[SkipVought]

It sure looks like a Comma Separated Values file (.csv)

Macro record Data > Get External Data > IMPORT using a COMMA delimiter.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]