Extract parts of a cell

A

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.
 
D

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))
 
P

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)
 
P

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
 
R

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.
Click to expand...

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
 
Top