Formula needed to split text and Numbers up?

[tonywatsonhelp]

Hi Everyone,
Double request as it the same thing but there way around

Column W has data in it in the form or numbers then words no break.
I want to split the numbers and the letters up into column X & Y so if W2 = 2345631TRESDYFF X2 would be 2345631 and Y2 Would be TRESDYFF

the only problem is that I don't know how many Numbers will be before the letters (It will always be numbers first then TEXT)

how can I do this please?

Thanks

Tony

 

[tonywatsonhelp]

PS this is going to be copied down over a lot of cells so if its better to do it with a macro the range is W2:W500000

Thanks

Tony

 

[tonywatsonhelp]

Can anyone help me on this?

Tony

 

[Snakehips]

Tony,

Does this help?

Excel 2007
	W	X	Y
1
2	2345631TRESDYFF	2345631	TRESDYFF
3	3TRESDYFF	3	TRESDYFF
4	2345-TRESDYFF	2345	-TRESDYFF
Sheet4

Cell Formulas
Range	Formula
X2	=-LOOKUP(1,-LEFT(W2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(W2)))))
Y2	=SUBSTITUTE(W2,X2,"")

 

[tonywatsonhelp]

thats brilliant, thank you snakehips