help on splitting into columns
 

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

help on splitting into columns
 

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<athere.com

In the above the email address is 2big4U<athere.com

With that in mind, try these:

A1 = 0998786751fish<atgmail.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,"")


--
Biff
Microsoft Excel MVP


wrote in message
...
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

help on splitting into columns
 

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)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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<athere.com

In the above the email address is 2big4U<athere.com

With that in mind, try these:

A1 = 0998786751fish<atgmail.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,"")


--
Biff
Microsoft Excel MVP


wrote in message
...
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

help on splitting into columns
 

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

help on splitting into columns
 

=""&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

help on splitting into columns
 

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))))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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<athere.com

In the above the email address is 2big4U<athere.com

With that in mind, try these:

A1 = 0998786751fish<atgmail.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,"")


--
Biff
Microsoft Excel MVP


wrote in message
...
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