MLA to Bookseller Marketplace Formula

[Alba_Machado2]

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:

[Kamille_Parks]

Once again:

Kamille_Parks:

B: Your separators are, in order: the opening parenthesis, the colon, the comma, the closing parenthesis.

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

[kuovonne]

Alba_Machado2:

(NY: Atheneum, 1979)

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]

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 separator

[Kamille_Parks]

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]

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

[Kamille_Parks]

Kamille_Parks:

TRIM(MID(Name, FIND(“the separator which marks the START of the item in question”, Name) + 1, FIND(“the separator which marks the END of the item in question”, Name) - FIND(“the separator which marks the START of the item in question”, Name) - 1 ))

OR Kuovonne’s clarified version:

kuovonne:

TRIM(
  MID(
    {Field Name}, 
    FIND("starting separator", {Field Name}) + 1, 
    FIND("ending separator", {Field Name}) - FIND("starting separator", {Field Name}) - 1 
  )
)

[kuovonne]

Alba_Machado2:

trying to wrangle my dad’s 50,000+ records into Airtable

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

[Alba_Machado2]

Press Field still eludes me…

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

[Kamille_Parks]

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

[Alba_Machado2]

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