# RIGHT + FIND with LEFT + FIND

Options
✭✭✭✭✭

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:

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?

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!