=""&LOOKUP(99^99,MID(A1,1,ROW(INDIRECT("1:"&LEN(A 1))))+0)

That drops leading zeros.


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
In B1: =""&LOOKUP(99^99,MID(A1,1,ROW(INDIRECT("1:"&LEN(A1 ))))+0)

In C1: =SUBSTITUTE(A1,B1,"")


" wrote:

In excel,i have a column having the phonenumber and emailid(both in
the same column) how to split into 2 ?
please help me to split the phone number and email id into two
different columns....
example : in column A ,is to be separated as
0998786751 in column B and in column C
note:the length of phone number and email id may vary...
Give me a formula not a macro..
there are 1000's of records like this