Skip to main content

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!

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)

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)

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

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)

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?

Reply


Terms & Conditions