Complex formula
Hello All,
I have created a formula which when someone sends an email, from the email, the name is first extracted and then converted to Dr. John Smith for example.
For example, [email protected], then I created a formula :
=IFERROR(LEFT([Alternate Approver Email  Research]@row, FIND("@", [Alternate Approver Email  Research]@row)  1), "")
which extracts John.Smith and then created another formula :
="Dr. " + UPPER(LEFT([Extracted Name (for PI)]@row, 1)) + MID([Extracted Name (for PI)]@row, 2, [Dot location (for PI)]@row  2) + " " + UPPER(MID([Extracted Name (for PI)]@row, [Dot location (for PI)]@row + 1, 1)) + MID([Extracted Name (for PI)]@row, [Dot location (for PI)]@row + 2, [Name Len (for PI)]@row  [Dot location (for PI)]@row)
which then gives Dr. John Smith.
However, the formula,
="Dr. " + UPPER(LEFT([Extracted Name (for PI)]@row, 1)) + MID([Extracted Name (for PI)]@row, 2, [Dot location (for PI)]@row  2) + " " + UPPER(MID([Extracted Name (for PI)]@row, [Dot location (for PI)]@row + 1, 1)) + MID([Extracted Name (for PI)]@row, [Dot location (for PI)]@row + 2, [Name Len (for PI)]@row  [Dot location (for PI)]@row)
works when there is "john.smith" and wouldn't work if it's "johnsmith". Is there a way to fix this where it works for both cases??
Look forward to hearing from you. Thanks so much in advance!!!
Answers

I tried and it doesn't work with "johnsmith" because it's taking this into account as a single name and not differentiating between the FIRST and LAST name. I will keep trying to find a way.. thanks
Ipshita Mukherjee

@Ipshita Thank you so much! Appreciate it!
Help Article Resources
Categories
Check out the Formula Handbook template!