Split cell help!

[davids]

I was wondering if there was a formula I could use in excel to split data out
from a single cell into multiple cells

ie Sydney NSW 2000 (Currently All in one cell)
Would like to know how to split "Sydney" into it's own cell,"NSW" into its
own cell and respectively "2000" into its own cell

Thanks

 

[JudithJubilee]

Hello davids,

Check out this tutorial:

http://www.cpearson.com/excel/FirstLast.htm

Judith

 

[pau1a]

I don't know of a formula but try selecting the cell containing the data,
then from top toolbar- data -text to columns.

Select delimited, then space as your delimiter. Should have the desired
effect if you are looking for a one off fix!

 

[davids]

this works but it is limited say for instance my suburb is called "lane cove"
i get "lane" in its own column and "cove" in its own column is there a way to
keep "lane cove" in one cell then "NSW" in its own cell and then "2022" in
its own cell?

 

[Ron Rosenfeld]

this works but it is limited say for instance my suburb is called "lane cove"
i get "lane" in its own column and "cove" in its own column is there a way to
keep "lane cove" in one cell then "NSW" in its own cell and then "2022" in
its own cell?

I think you need to present us with the possible variations in order to obtain
a coherent answer.

Will NSW always be NSW? If not, how do we differentiate it from the rest of
the string.

For example: is it always a three letter string of capital letters that
precedes a four digit number that ends the string? Or are there more
variations than that?


--ron