Try this:

With
A1: (text, with numbers before or after)

B1: =SUBSTITUTE(A1,C1,"")
C1: (in sections for readability)
=LOOKUP(99^99,--("0"&MID(A1,MIN(
SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),
ROW($1:$10000))))

If
A1: ABC876
or
A1: 876ABC

Then, either way...
B1 returns: ABC
C1 returns 876

Note: If there are NO numbers in the string, Col_C returns a zero.

Does that help?
--------------------------

Regards,

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


"Narasimha" wrote in message
...
Sorry, it doesn't working for repeated numbers i.e 111ML
help me
.................................................. .......
I got it with your inspiration for below one also
=RIGHT(A2,COUNT(IF(FIND({0,1,2,3,4,5,6,7,8,9},A2), 1))-1)
thanks alot Ron.


dear Ron,
if starts with numeric and ends with alpha for example 123MLN , then how?
please suggest for this also
thanks

"Ron Coderre" wrote:

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