Extract parts of a cell
 

I have found several discussions on how to extract a first or last name, yet
I still need help on how to insert additional symbols as well...

I have in Cell A1: Mr. John Smith

I need in Cell B5: Mr. Smith:
and even, if I could get.... Dear Mr. Smith:

Can anyone offer some assistance? Thank you for your time.

Extract parts of a cell
 

Will fail if you have initials Mr. John J. Smith

="Dear "&LEFT(C3,FIND(" ",C3))&RIGHT(C3,LEN(C3)-FIND(" ",C3,FIND(" ",C3)+1))

--
Don Guillett
SalesAid Software

"Aggies" wrote in message
...
I have found several discussions on how to extract a first or last name,
yet
I still need help on how to insert additional symbols as well...

I have in Cell A1: Mr. John Smith

I need in Cell B5: Mr. Smith:
and even, if I could get.... Dear Mr. Smith:

Can anyone offer some assistance? Thank you for your time.

Extract parts of a cell
 

One possible way:

="Dear " & D1 & RIGHT(A1,LEN(A1)-(FIND("^",SUBSTITUTE(A1,"
","^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))) & ":"

HTH,
Paul


This will not work if there is a space in the last name. (ex. - Di Angelo)
"Aggies" wrote in message
...
I have found several discussions on how to extract a first or last name,
yet
I still need help on how to insert additional symbols as well...

I have in Cell A1: Mr. John Smith

I need in Cell B5: Mr. Smith:
and even, if I could get.... Dear Mr. Smith:

Can anyone offer some assistance? Thank you for your time.

Extract parts of a cell
 

Ooops! I forgot to change D1. The correct formula should be:

="Dear " & LEFT(A1,FIND(".",A1)) & " " &
RIGHT(A1,LEN(A1)-(FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))) & ":"

HTH,
Paul

"PCLIVE" wrote in message
...
One possible way:

="Dear " & D1 & RIGHT(A1,LEN(A1)-(FIND("^",SUBSTITUTE(A1,"
","^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))) & ":"

HTH,
Paul


This will not work if there is a space in the last name. (ex. - Di Angelo)
"Aggies" wrote in message
...
I have found several discussions on how to extract a first or last name,
yet
I still need help on how to insert additional symbols as well...

I have in Cell A1: Mr. John Smith

I need in Cell B5: Mr. Smith:
and even, if I could get.... Dear Mr. Smith:

Can anyone offer some assistance? Thank you for your time.

Extract parts of a cell
 

Wow... these formulas are different from Access.... Thanks so much... it
worked..
--
Thank you for your time.
"Happiness is not having what you want, but wanting what you have"


"Don Guillett" wrote:

Will fail if you have initials Mr. John J. Smith

="Dear "&LEFT(C3,FIND(" ",C3))&RIGHT(C3,LEN(C3)-FIND(" ",C3,FIND(" ",C3)+1))

--
Don Guillett
SalesAid Software

"Aggies" wrote in message
...
I have found several discussions on how to extract a first or last name,
yet
I still need help on how to insert additional symbols as well...

I have in Cell A1: Mr. John Smith

I need in Cell B5: Mr. Smith:
and even, if I could get.... Dear Mr. Smith:

Can anyone offer some assistance? Thank you for your time.

Extract parts of a cell
 

Thanks so much... I was wondering how to add the colon... Yall are great.
--
Thank you for your time.
"Happiness is not having what you want, but wanting what you have"


"PCLIVE" wrote:

Ooops! I forgot to change D1. The correct formula should be:

="Dear " & LEFT(A1,FIND(".",A1)) & " " &
RIGHT(A1,LEN(A1)-(FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))) & ":"

HTH,
Paul

"PCLIVE" wrote in message
...
One possible way:

="Dear " & D1 & RIGHT(A1,LEN(A1)-(FIND("^",SUBSTITUTE(A1,"
","^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))) & ":"

HTH,
Paul


This will not work if there is a space in the last name. (ex. - Di Angelo)
"Aggies" wrote in message
...
I have found several discussions on how to extract a first or last name,
yet
I still need help on how to insert additional symbols as well...

I have in Cell A1: Mr. John Smith

I need in Cell B5: Mr. Smith:
and even, if I could get.... Dear Mr. Smith:

Can anyone offer some assistance? Thank you for your time.

Extract parts of a cell
 

On Mon, 21 May 2007 13:21:01 -0700, Aggies
wrote:

I have found several discussions on how to extract a first or last name, yet
I still need help on how to insert additional symbols as well...

I have in Cell A1: Mr. John Smith

I need in Cell B5: Mr. Smith:
and even, if I could get.... Dear Mr. Smith:

Can anyone offer some assistance? Thank you for your time.


You can use Regular Expressions.

Although it can be implemented in VBA, it may be easier to download and install
Longre's free morefunc.xll add-in from http://xcell05.free.fr

Then use this formula:

=REGEX.SUBSTITUTE(A1,"(.*?\.).*?(\w+)$","Dear [1] [2]:")


--ron