Splitting an address into parts

[tuti]

I have a list of addresses, which I want to split into three parts (the house number, the street name, and the street type). When the address is simple , like 2 Smith St, it is easily done by changing text to columns, using a space as the delimiter. However, when dealing with an apartment address, like 1/5 Smith St, the house number part (1/5) gets converted by Excel to a date (in Australia, it would appear as 1-May). I have tried lots of different ways of formatting the original column and the destination columns after the split (such as general or text format) with no luck. Has anyone found a way around this problem, or found a different way to split the address without getting the apartment numbers as a date?

 

[fairwinds]

Hi,

After selecting space as delimiter, don't click finish. Click next instead and set data column format as text. Then click finish.

 

[tuti]

Thanks - works like a charm. It's always so simple in hindsight!