Need formula to split column

[Sm3tUser]

I have a column with mobile phone numbers as well as landlines:


[table="width: 500, class: grid"]
[tr]
[td]0403920493[/td]
[/tr]
[tr]
[td]02 9345 9483[/td]
[/tr]
[tr]
[td]93545493[/td]
[/tr]
[/table]


I need a formula to split up the mobile numbers and the landlines. It could either work by:
- First splitting off the numbers that begin with '02', then apply another formula to split the ones that begin with '9' - I will then merge to the landlines columns that I just created
- Split off any numbers that begin with either '02' or '9'


What I don't want is for the formula to rather split off any numbers that begin with '04'.


Thanks!

 

[alansidman]

i hope this is what you meant and are looking for

Excel Workbook
	A	B	C	D
1	Original Data	4	2	9
2	403920493	403920493
3	02 9345 9483		02 9345 9483
4	93545493			93545493
Sheet1