On Wed, 28 May 2008 19:19:01 -0700, anna
wrote:

How to Split the contents of cells across multiple cells
Hi ,
I’m trying to split address from one cell across multiple cells with no
success with Data / column to Text and then wizard if the suburb was only
one word it would be no problem but sometimes it has two words Like Port
Augusta then state and postcode
example
Greenwith WA 5122 works ok splits into 3 columns but
Port Augusta WA 5147 not because I got Port August in one column and ta
in next column and state and post code together and if there is a longer
name it goes into for columns can you please help me to solve that
Thank you in advance

US terminology would be City State Zip Code

Assuming that every address has a zip code -- and these should be either 5
digits or 9 digits in the US; and that every zip code is preceded by a two
character state code (also standard in the US, then the following should work:

A1: Address in above format

City
B1: =TRIM(SUBSTITUTE(A1,C1&" "&D1,""))

State
C1:

=TRIM(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(
A1," ",REPT(" ",13)),26)),D1,""))

Zip_Code
D1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10))

If there is more variability, then a different solution may be necessary.
--ron