Seperating Last Name, First Name andl Telephone (TRIM, STRG)

[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


fb

 

[SkipVought]

Hi,

"In most cases... " won't cut it, 'cuz in SOME cases the "In most case" logic may give unwanted results.

You described the data in your cell. Please post concrete examples of each different type of phone format, as it appears in the cell with ALL the name data.

Skip,
[sub]
[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen![/sub]

 

[Storyteller]

Hello Frank,
I would first seperate the telephone data from the name data by using "Text to Columns" it is under the "Data" menu. I would imagine "space" is the delimeter between those two values.

I would then use "Text to Columns" to seperate the First and Last names. The delimeter would be the "Comma".

This should help you get started.

Regards,
Michael

 

[SkipVought]

Michael,

Your tip would work IF there is indeed a space delimiting the name portion from the phone number portion (and not, for instance, a [Tab] character that LOOKS like a space)

AND...

there are no spaces in any of the name portion

OR...

there are the same number of spaces in the name portion.

That's why a quick answer may not be a good solution.

Skip,
[sub]
[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen![/sub]

 

[Niksen]

have you tried to use the "fixed with" feature ?

that helped me out of similar situations.

brgds Nicolai

 

[Niksen]

sorry, i forget to mention, that is in excel

brgds Nicolai

 

[fbacchus]

Thank you all for your responses. As Skip requested, here is an example of the data:

COSTA, PLAYS 212-574-1861
ABC, SMITH
JONES TOM (345) 555-1212
INGRID LUMS, (888)555-1203, EXT 105
SMITH, ZINA 764 281-807
JONAS BIL 345 555 7208


Also, Nicolai, I couldn't find any information on the "fixed with feature" Where can I find this information. Michael, thanks, but I am not sure if the "Text to Column" would work base on variation of the data as above.

Any other suggestions would be gladly appreciated.

thanks


fb

 

[Niksen]

wish i could put a screenie.

if you open some txt file with date and try to open with excel, it will pop a dialog "tekst import wizard"
first step here is to choose fixed width (not with sorry) or delimited.
if you choose fixed width, you get a preview where you can place the column seperator.

very usefull in some situations.

try it

brgds Nicolai