macro help

[danushka]

dear friends in my excel work sheet column "A" has data like below..

column "A"
5000MTRS
5000 MTRS
5000 MTRS
1000YDS
2000YDS
5000YDS
5000YDS
10000YDS

I want to separate no & text in to column "B" & "C".

ex- if column "A9"=5000MTRS, then column "B9"=5000 & column "C9"=MTRS.that column "A9" value should not change.

my sheet data starting from row 9.pls give me macro for this..thanks in advance.

 

[danushka]

thanks a lot Peter_SSs.u have given me lot of options.thanks a lot again..