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.