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

[Brett_Drachenbe]

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?

Thank you in advance.

[Kamille_Parks · Accepted solution]

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

[kuovonne]

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.

[Brett_Drachenbe]

@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.

[kuovonne]

Brett_Drachenberg:

I am still curious how the -2 (the number of characters to extract) is returning such varied results.

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.

[Brett_Drachenbe]

Ah. I see. Many thanks.