Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

MLA to Bookseller Marketplace Formula

Topic Labels: Formulas
Solved
Jump to Solution
4335 21
cancel
Showing results for 
Search instead for 
Did you mean: 
Alba_Machado2
7 - App Architect
7 - App Architect

Hustling bookseller back with another puzzler. What formula would I use to take data in my dad’s {Publisher MLA} field (NY: Atheneum, 1979) and separate it out into three formula fields ({PublishPlace}, {Publisher}, and {PublishYear})? Bonus for a formula that can change NY to New York too. Please and thank you. :slightly_smiling_face:

21 Replies 21

Once again:

Only one of those four things appears before the name of the place.

Is this an accurate example of your data, including the parentheses?
If so, @Kamille_Parks said what your separators are in her posts.

For the {PublishPlace} the “starting separator” is the opening parenthesis.

If it is not a example, could you please provide a screen shot?

Alba_Machado2
7 - App Architect
7 - App Architect

I’m sorry. I know you both are awesome and have been super helpful to me. It must be how scrambled my brain is from trying to wrangle my dad’s 50,000+ records into Airtable now that they’re broken because of the CSV download. I’ll try attaching a screenshot of the last formula I tried, using open and closed parenthesis for starting separator and ending separatorAirtable 1

As Kuovonne surmised, the issue now is that I interpreted your example to be literal, meaning that the value for your {Original Publisher Info} field included paranthesis but as we can see from your screenshot it does not.

Keep the same formula pattern I originally gave you for your “middle” field {Press}

For {Place}:

TRIM(
   MID(Name, 1, FIND(":", Name) -1)
)

and for

{Year}:

TRIM(
   MID(Name, FIND(",", Name) + 1, LEN(Name))
)

Where “Name” = {Original Publisher Info}

Alba_Machado2
7 - App Architect
7 - App Architect

The formulas for Place and Year worked! :slightly_smiling_face: But Press didn’t work, maybe because I’m not clear on which formula I’m supposed to use there

OR Kuovonne’s clarified version:

Airtable has a 50,000 record limit per base. Are you putting your records in multiple bases?

Alba_Machado2
7 - App Architect
7 - App Architect

Press Field still eludes me…

Airtable 2

And omg, thanks for the warning kuovonne! I guess I could split his records into sold and not sold, so that each could be stored on a different base

For every field name reference you included an extra } at the end.

Alba_Machado2
7 - App Architect
7 - App Architect

Yikes! Egg on my face. You’re right. It worked. Thank you both so much for your help. Heart