help on splitting into columns

[deepikakct]

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 : (e-mail address removed) in column A ,is to be separated as
0998786751 in column B and (e-mail address removed) 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

 

[T. Valko]

Here's one way but if you have 1000's of these this might not be the best
way to go. Also, if an email address contains numbers how would you know
where the phone number ends (unless *every* phone number is the same length)
and the email address begins?

123456789012big4U<at>here.com

In the above the email address is 2big4U<at>here.com

With that in mind, try these:

A1 = 0998786751fish<at>gmail.com

For the phone number, array entered** :

=LEFT(A1,MATCH(FALSE,ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

For the email address, assuming the above formula is entered in B1:

=SUBSTITUTE(A1,B1,"")

 

[T. Valko]

Here's a slightly shorter array formula** for the phone number:

=LEFT(A1,MATCH(1,--ISERR(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Teethless mama]

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

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

 

[T. Valko]

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

That drops leading zeros.

 

[T. Valko]

Here's another version to get the phone number with a caveat.

Assumes you will *never* insert new rows above the range. That way we can
get rid of the volatile function INDIRECT.

Normally entered (not array entered).

=LEFT(A1,SUMPRODUCT(--ISNUMBER(-MID(A1,ROW(A$1:A$50),1))))