# RIGHT(SEARCH()) odd results for last name first name reverse formula results

Topic Labels: Formulas
Solved
1241 5
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

I am getting in inexplicable result from a RIGHT(SEARCH()) formula.

My .csv import includes the field [Payroll Name} formatted as LastName, FirstName.
I found a name reversal formula on this thread:

RIGHT({Payroll Name}, SEARCH(‘,’, {Payroll Name})-2)&’ ‘&LEFT({Payroll Name}, SEARCH(’,', {Payroll Name})-1)

The formula returns the last name correctly, but the first name returns a maddening variety of results. Some first names are correct, others include the comma and space from {Payroll Name}, some include the last letter of the last name, or remove the first, second, or third letter of the first name as shown below (The FirstName and LastName fields are the respective portions of the {Name} formula, shown for clarity).

I have tried a variety of values for “-1” and “-2” (among other things), with unsatisfactory results.

Of course I can correct the first name problem in Excel before importing, but I am super curious about how to fix it in Airtable. What is going on here? How do I get the result I want?

1 Solution

Accepted Solutions
16 - Uranus

If your logic is “Last Name = anything before the comma” then that formula can be simplified to

``````REGEX_REPLACE({Payroll Name}, "(.*),.*", "\$1")
``````

^ I used REGEX, but a RIGHT or MID formula could also work.

If your logic is “First Name = everything else except the comma and space” then it can be simplified to

``````SUBSTITUTE({Payroll Name}, {Last Name} & ", ", "")
``````

Then the reversed name field could be just

``````TRIM(
CONCATENATE({First Name}, " ", {Last Name})
)
``````
5 Replies 5
16 - Uranus

If your logic is “Last Name = anything before the comma” then that formula can be simplified to

``````REGEX_REPLACE({Payroll Name}, "(.*),.*", "\$1")
``````

^ I used REGEX, but a RIGHT or MID formula could also work.

If your logic is “First Name = everything else except the comma and space” then it can be simplified to

``````SUBSTITUTE({Payroll Name}, {Last Name} & ", ", "")
``````

Then the reversed name field could be just

``````TRIM(
CONCATENATE({First Name}, " ", {Last Name})
)
``````
18 - Pluto

The `RIGHT()` formula extracts a number of characters from the right side of the string. The number in the second parameter is the number of characters to extract, not the starting position. You are sending a number based on the starting position of the first name, not the length of the first name. The number of characters to for the first name would be the `LEN({Payroll Name}) - SEARCH(...)` with some adjustments for the comma and space.

I was about to recommend REGEX, but I see Kamille has beat me to it.

5 - Automation Enthusiast

@Kamille_Parks Those worked perfectly. Thank you.

@kuovonne Thank you for the clarification, that makes sense. I am still curious how the -2 (the number of characters to extract) is returning such varied results.

18 - Pluto

Let’s look at `Luu, Loan`. The comma is the 4th character. 4 -2 is 2. So the formula returns the right two characters: `an`.

For `Solis, Carmela`, the comma is the 6th character. 6-2 = 4. So the formula returns the right four characters: `mela`.

For `McDonald, Felicia`, the comma is the 9th character. 9-2 = 7. The formula returns the right 7 characters: `Felicia`. In this case you luck out because the first name happens to be 7 characters long.

5 - Automation Enthusiast

Ah. I see. Many thanks.