Excel format data

[tyutghf]

I have a spreadsheet that has an address column, the address is of the format

1 Street Name, Town, City, Postcode, Country

I need to create a new sheet with the original data but split into seperate columns

i.e.

address1 address2 address3 address4 address5
1 Street name Town City Postcode Country


Is this possible?

Thanks

 

[GlennUK]

I'd copy the column to a blank sheet and use Data/Text To Columns, Delimited/Comma.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[tyutghf]

Hi Glenn, where is that option? I am on Excel 2003 if that makes a difference?!??!

 

[xlbo]

Bear in mind that text to columns (or indeed any processing of this type of data) will be reliant on the onsistency of format of your data. Extra spaces / commas etc may throw your results out

1 Bow Street Manchester M33 1HP England

will process differently than

1 Bow Tree Lane Manchester M33 1HP England

as there are extra spaces in the 2nd example





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

 

[SkipVought]

where is that option?

Menu item...
[tt]
Data > Text to columns -- DELIMITED -- COMMA[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tyutghf]

Just looking through (there are a few thousand rows) some are of the format

1 Street name, , , postcode

and some are split by linebreak with a box at the end of each line

1 Street name[box]
Town[box]
City[box] etc

but the vast majority are of the same format I think.

 

[xlbo]

They may be of the same format but when you split based on a delimeter (space) for instance, extra spaces in town names or street names will cerate extra columns for some rows vs others

I'm not saying don;t do it but be prepared to have to sift through the data and make a number of manual corrections

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