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:
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!
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.
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)
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