separate text and number

[raju]

dear sir

in Cell A1 is "rajulu1234". i want in cell B1 "rajulu" & cell C1 "1234".
please help me.

thanks advance.

raju

 

[Jacob Skaria]

Hi Raju

One way..

In B1
=LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)

In C1
=SUBSTITUTE(A1,B1,)

 

[Rick Rothstein]

Give these formulas a try...

B1: =LEFT(A1,LEN(A1)-LEN(C1))

C1: =LOOKUP(9E+307,--RIGHT(A1,ROW($1:$99)))

Note: When you put the first formula into B1, it will initially display the
entire contents of A1 **until** you put the other formula into C1.

 

[Teethless mama]

In C1: =LOOKUP(10^10,--RIGHT(A1,ROW(1:99)))

In B1: =SUBSTITUTE(A1,C1,)

 

[ozgrid.com]

Try: http://www.ozgrid.com/VBA/ExtractNum.htm to pull the number into C1,
then: =SUBSTITUTE(A1,C1,"") in B1