With
A1: (text followed by numbers)

Try this:

B1: =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))-1)
C1: =SUBSTITUTE(A1,B1,"")

If A1: ABC876
Then
B1 returns: ABC
C1 returns 876

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Narasimha" wrote in message
...
Hi all,
how do separate alpha numeric value for example I have values like
ABC123,AB234
starts with alpha but the length may be 2 or 3 or 4. just I want alpha
value
in one column and numeric in another column.

could anyone help me ?
thanks