RIGHT + FIND with LEFT + FIND
Hi,
So I can run automations to notify individuals, I have a column that is a contact list. For data reporting purposes, I need to upload the names into an API in a (last_name, first_name) format.
To get around this, I have concocted a twostep process:
 Write a function in a cell to pull back (first_name last_name)
 Write another function to put it into (last_name, first_name) format.
The first function is as follows, and is working fine to pull the contact name into the "Prep  KLG Primary Contact (auto calculate)" cell:
=[KLG Project Lead]@row
The issue comes in at step two.
I have written the following function:
=RIGHT([Prep  KLG Primary Contact (auto calculate)]@row, FIND(" ", [Prep  KLG Primary Contact (auto calculate)]@row, 1)) + ", " + LEFT([Prep  KLG Primary Contact (auto calculate)]@row, FIND(" ", [Prep  KLG Primary Contact (auto calculate)]3))
It works well for some names, but not for others, and I can't figure out why.
Can someone help?
Best Answer

OHHHHH. Duh. I found a solution for you. You can't use the same method to find the last name as you did the first because the lengths of the two names are different. You have to take the full length of the name and subtract the length of the first name and the space to get the number of characters you need for the last name. :) This formula should do the trick for you. 😝
=RIGHT([Prep  KLG Primary Contact (auto calculate)]@row, LEN([Prep  KLG Primary Contact (auto calculate)]@row)  FIND(" ", [Prep  KLG Primary Contact (auto calculate)]@row, 1)) + ", " + LEFT([Prep  KLG Primary Contact (auto calculate)]@row, FIND(" ", [Prep  KLG Primary Contact (auto calculate)]@row))
Answers

Are there extra spaces in those names? It seems to be grabbing and repeating the last letter of the first name. You might want to trim those results. I'm going to play with this formula and see what I can find.

Hmmm. Try this slight adjustment.
=RIGHT([Prep  KLG Primary Contact (auto calculate)]@row, FIND(" ", [Prep  KLG Primary Contact (auto calculate)]@row, 1)) + ", " + LEFT([Prep  KLG Primary Contact (auto calculate)]@row, FIND(" ", [Prep  KLG Primary Contact (auto calculate)]@row))

Hi @Mike Wilday no extra spaces are present. I checked that :)
I tried your adjusted formula, and I'm still experiencing the same issue.

you were lucky some worked.. it is when the ratio of the length of the Lastname and that of the first name
Try this
=RIGHT([Prep  KLG Primary Contact (auto calculate)]@row, LEN([Prep  KLG Primary Contact (auto calculate)]@row)  FIND(" ", [Prep  KLG Primary Contact (auto calculate)]@row, 1)) + "," + LEFT([Prep  KLG Primary Contact (auto calculate)]@row, FIND(" ", [Prep  KLG Primary Contact (auto calculate)]@row))
I added a check to subtract where it finds the " " from the length of the entire string.
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com

OHHHHH. Duh. I found a solution for you. You can't use the same method to find the last name as you did the first because the lengths of the two names are different. You have to take the full length of the name and subtract the length of the first name and the space to get the number of characters you need for the last name. :) This formula should do the trick for you. 😝
=RIGHT([Prep  KLG Primary Contact (auto calculate)]@row, LEN([Prep  KLG Primary Contact (auto calculate)]@row)  FIND(" ", [Prep  KLG Primary Contact (auto calculate)]@row, 1)) + ", " + LEFT([Prep  KLG Primary Contact (auto calculate)]@row, FIND(" ", [Prep  KLG Primary Contact (auto calculate)]@row))

And @Brent Wilson beat me by minutes to the solution. 🤣

@Brent Wilson and @Mike Wilday, very impressive! It's fixed!
Help Article Resources
Categories
Check out the Formula Handbook template!