Reversing First and Last Names

[Alba_Machado2]

I’m back with a puzzle it wouldn’t have occurred to me to even attempt if I hadn’t seen some miraculous-seeming formulas early on in my Airtable forays. I sell books that I inherited from my dad. In my Books table, there’s an Author field that sometimes lists names first then last (Mark Twain) and sometimes lists names last, first (Twain, Mark). There’s no consistency. The thing is that there are different circumstances that call for different formatting.

So my modest dream for this situation is to have two additional fields, both formula fields, obviously:

First Last
Last, First

This will allow me to rollup one way for online marketplace inventory submissions (first last) and another way for professional association catalogs (last, first). I’m guessing making note of the comma will be an integral part of these formulas. Am I wrong to think this is a puzzle that can be solved on Airtable? TIA for your puzzling skills!

[Vivid-Squid]

Hi @Alba_Machado2,
I would first get all of my Author names in the same order. I would start by separating the names with a comma from the names without a comma.

FIND({Name}, ‘,’)

Filter to just these records

Then if your names are always formatted as Last, First

This will reverse them

RIGHT(Name, SEARCH(',', Name)-2)&' '&LEFT(Name, SEARCH(',', Name)-1)

[Alba_Machado2]

Ah, I just realized there’s an extra obstacle: some Author cells have multiple authors listed in them. Is there a way to write the formula so that the cells with multiple commas get ignored? I’ll give you some examples pulled at random:

Chesterton, G.K. - Lewis, C.S.

Steiner, George - Wolfe, Tom et al - McLuhan, Marshall

Fraser, J.T., Lawrence, N. And Haber, F.C. (eds.)

Zimring, Franklin E. & Hawkins, Gordon

[Alba_Machado2]

Also, the preference for the catalog stuff I’m working on is to get all the fields to be Last, First. So the reverse of the formula you provided. Thanks for that, by the way! It’s been a long day but where are my manners?