Opposite of CONCATENATE

[mrbeanyuk]

Hi Guys

Slight problem......

Column A has an address, and I would like a quick method of seperating each word so that the road name appears in column B, town in C and postcode in D.

Easy task?! Thanks in advance as always

 

[Joe4]

If you highlight a column and do Data | Text To Columns, you can split up a single column into multiple ones by selecting a delimiter (space, tab, comma, etc), or fixed width.

That is pretty easy to do, if the different address lines are delimited by some delimiter than isn't also as text in the address line (if you have a space delimiter, how can it tell the different between a space delimiter and a valid space in your address, i.e.

1234 West Main Street Dallas, TX 55555

How does it know which spaces are spaces and which are delimiters?

 

[isis]

Is there another way to do this besides using text to columns?

I have a similar problem in that I have one column containing comma separated addresses that tend to follow this format:

House Name, Street name, Town Name, Postcode

There are around 800 records altogether so doing it manually is going to be a bit of a pain. In an ideal world I'd like each field to appear in the correct column, eg House Name in column A, Street Name in column B and so on, but I'm aware I'm probably asking the impossible there, but I'm hoping that as the comma is acting as a separator there might be a possibility of separating the data in some way.

I did try text to columns but as street names etc vary in length it wasn't much use.

Thanks in advance

 

[Richard Schollar]

Hi

When you select the column and go Data>TextToColumn choose the Delimited option and click Next. Then check the Comma checkbox and click Finish. This will split the data up using the comma as the separator, which, if I have understood you correctly, will do what you want.

 

[Stormseed]

Is there another way to do this besides using text to columns?

I have a similar problem in that I have one column containing comma separated addresses that tend to follow this format:

I did try text to columns but as street names etc vary in length it wasn't much use.

Thanks in advance

You need to use Text to Columns with the "Delimited" option and not with "Fixed Width". Using Fixed Width may result in discrepancies as you said that street names vary in length. But using Delimited Comma option, the text to columns would give you very accurate results...try it !

 

[isis]

Yes that's what I was wanting

Thanks guys.