RIGHT + FIND with LEFT + FIND

Kayla Q

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 two-step process:

1. Write a function in a cell to pull back (first_name last_name)
2. 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?

Mike Wilday

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

Mike Wilday

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.

Mike Wilday

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

Kayla Q

Hi @Mike Wilday no extra spaces are present. I checked that :)

I tried your adjusted formula, and I'm still experiencing the same issue.

Brent Wilson

@Kayla Q

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.

Mike Wilday

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

Mike Wilday

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

Kayla Q

@Brent Wilson and @Mike Wilday, very impressive! It's fixed!