Extract parts of a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Will fail if you have initials Mr. John J. Smith

="Dear "&LEFT(C3,FIND(" ",C3))&RIGHT(C3,LEN(C3)-FIND(" ",C3,FIND(" ",C3)+1))
 
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)
 
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
 
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
 
Back
Top