Separate address into multiple cells at a colon

[Rudi]

Hi all,

A data processing system has exported addresses into Excel that looks like this...

In cell A1 is all this text...

7 Birch Way:Fair
Trees:X:Kirstenbosch:Cape
Town:Western
Province:7348

Above is one of the imports displayed in cell A1.
I need a formula to display 7 Birch Way in cell (B1), Fair Tress in cell (C1), X in D1, Kirstenbosch in (E1) etc.

There are a couple of thousand rows. Any bright ideas? TX

[HansV]

Why not use the Text to Columns wizard (in the Data menu in Excel 2003 or earlier, in the Data tab of the ribbon in Excel 2007 or later)?
Choose the Delimited option, click Next, and enter : in the Other box.

[Rudi]

Silly me... I completely forgot about that. (Embarrassed)

PS: I know you are not a soccer fan, but you guys can soooo close!!! I was rooting for you guys since I have dutch blood in me! Bummer :(

[HansV]

I fear that the Dutch disgraced themselves during the final. Spain deserved to win.