Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 21, 2020 02:44 PM
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:
Solved! Go to Solution.
Oct 23, 2020 11:43 AM
Once again:
Only one of those four things appears before the name of the place.
Oct 23, 2020 11:46 AM
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?
Oct 23, 2020 11:52 AM
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
Oct 23, 2020 11:58 AM
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}
Oct 23, 2020 12:05 PM
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
Oct 23, 2020 12:06 PM
OR Kuovonne’s clarified version:
Oct 23, 2020 12:06 PM
Airtable has a 50,000 record limit per base. Are you putting your records in multiple bases?
Oct 23, 2020 12:10 PM
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
Oct 23, 2020 12:11 PM
For every field name reference you included an extra } at the end.
Oct 23, 2020 12:15 PM
Yikes! Egg on my face. You’re right. It worked. Thank you both so much for your help.