Complex formula

Deesa Dontamsetti

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!!!

Ipshita

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

Deesa Dontamsetti

@Ipshita Thank you so much! Appreciate it!