Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

MLA to Bookseller Marketplace Formula

Topic Labels: Formulas
Solved
Jump to Solution
8854 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