Extract parts of a cell

[Aggies]

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.

 

[Don Guillett]

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

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

 

[PCLIVE]

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)

 

[PCLIVE]

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

 

[Aggies]

Thanks so much... I was wondering how to add the colon... Yall are great.

 

[Aggies]

Wow... these formulas are different from Access.... Thanks so much... it
worked..

 

[Ron Rosenfeld]

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