Extracting Last and First name fro Cell

[fbacchus]

Hi:

I have a cell containing Last and First Name. In most cases the
Lastname and Firstname are seperated by a comma. Also in this same
field there is a telephone number which can be in several different
formats (e.g (718-xxx-xxxx) or 718-xxx-xxxx)). I would like to extract
the phone number from this "Name" cell, leaving just the lastname and
the firstname. Seperating the names would be a bonus but I don't think
is possible, as there is no set patterns.

For the telephone number, there is another cell with telephone numbers
which is sometimes blank. What I'd like to do is, if I can extrapulate
the telephone number (formating it as 718-xx-1212) and place it in the
"telephone number cell", only if that cell is blank. This would be my
ultimate goal. Any help would be appreciated.

thanks

frank

 

[Beege]

fbacchus,

Try:

Copy column to blank area, perhaps new sheet
Select Column
Use Data/Text to Columns, follow clues there
HTH

Beege

 

[Ron Rosenfeld]

Hi:

I have a cell containing Last and First Name. In most cases the
Lastname and Firstname are seperated by a comma. Also in this same
field there is a telephone number which can be in several different
formats (e.g (718-xxx-xxxx) or 718-xxx-xxxx)). I would like to extract
the phone number from this "Name" cell, leaving just the lastname and
the firstname. Seperating the names would be a bonus but I don't think
is possible, as there is no set patterns.

For the telephone number, there is another cell with telephone numbers
which is sometimes blank. What I'd like to do is, if I can extrapulate
the telephone number (formating it as 718-xx-1212) and place it in the
"telephone number cell", only if that cell is blank. This would be my
ultimate goal. Any help would be appreciated.

thanks

frank

Do you want the results to be in the same cells as the entries? Or can they be
in adjacent columns.

If the latter, then, with your Name String in A1 and possible phone number in
B1, do the following:

1. Download and install Longre's free morefunc.xll add in from:

C1: Remove the phone number from the string
=REGEX.SUBSTITUTE(A1,"\d{3}-\d{3}-\d{4}")

D1: Extract phone number from string unless there is something in B1
=IF(B1="",REGEX.MID(A1,"\d{3}-\d{3}-\d{4}"),B1)

If the former, then you can either copy/paste values the results of the above
formulas over columns A&B, or use a VBA macro.

--ron

 

[Ron Rosenfeld]

Do you want the results to be in the same cells as the entries? Or can they be
in adjacent columns.

If the latter, then, with your Name String in A1 and possible phone number in
B1, do the following:

1. Download and install Longre's free morefunc.xll add in from:

C1: Remove the phone number from the string
=REGEX.SUBSTITUTE(A1,"\d{3}-\d{3}-\d{4}")

D1: Extract phone number from string unless there is something in B1
=IF(B1="",REGEX.MID(A1,"\d{3}-\d{3}-\d{4}"),B1)

If the former, then you can either copy/paste values the results of the above
formulas over columns A&B, or use a VBA macro.

Actually, if you need the results in the same cells, you cannot copy/paste
because that would destroy anything in B1. You will need a VBA macro is that
is a requirement.

If that is what you need, it will be simple to design one.
--ron