Function to take of characters, letters, etc.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The cell has info in this format: Name,"phone","email".
I need to take out the "phone" and the "email" and place them in separate columns. Please help.
Joe
 
Hi
one easy way would be to use 'Data - text to Columns' and define the
coma as delimiter
 
Thanks Frank. It worked great.
One more request. In the column titled NANE, How can I separate the first and last names to be in different columns (right now it is in one column)
Thanks
Joe
 
Hi,

Suppose the name is in cell A16. Enter the following array formula (Ctrl+Shift+Enter) in cell B16 to get the first name

MID(A16,MATCH(TRUE,EXACT(MID(A16,ROW($1:$13),1),PROPER(MID(A16,ROW($1:$13),1))),0),MATCH(TRUE,EXACT(MID(A16,ROW($2:$13),1),PROPER(MID(A16,ROW($2:$13),1))),0))

Enter the following formula formula (Ctrl+Shift+Enter) in Cell C16 to get the Surname

MID(A16,MATCH(TRUE,EXACT(MID(A16,ROW($2:$13),1),PROPER(MID(A16,ROW($2:$13),1))),0)+2,255)

Regards,
 
Hi,

Supose the name is in cell A16. To get the first name, array enter (Ctrl+Shift+Enter) the following formula in cell B16

MID(A16,MATCH(TRUE,EXACT(MID(A16,ROW($1:$13),1),PROPER(MID(A16,ROW($1:$13),1))),0),MATCH(TRUE,EXACT(MID(A16,ROW($2:$13),1),PROPER(MID(A16,ROW($2:$13),1))),0))

To get the last name, array enter (Ctrl+Shift+Enter) the following formula in cell C16

MID(A16,MATCH(TRUE,EXACT(MID(A16,ROW($2:$13),1),PROPER(MID(A16,ROW($2:$13),1))),0)+2,255)

Regards,
 
Back
Top