Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Splitting Numbers Into Different Columns | Excel Discussion (Misc queries) | |||
Splitting Column in to 2 columns | Excel Discussion (Misc queries) | |||
Splitting one column into multiple columns | Excel Worksheet Functions | |||
Splitting text to columns | Excel Worksheet Functions | |||
Splitting text in one column into two (or more) columns. | Excel Worksheet Functions |