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 10:28 AM
Follow this same pattern for all three formula fields
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 ))
Oct 21, 2020 07:46 PM
Oct 23, 2020 10:14 AM
Hi Kamille,
I tried this solution, but my publisher info does not contain any line breaks so, of course, it didn’t work. I tried replacing the formula’s “\n” with “:” since place and press are separated by a colon, but that only eliminated the colon and kept the rest of the publishing info together. :confused:
Oct 23, 2020 10:28 AM
Follow this same pattern for all three formula fields
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 ))
Oct 23, 2020 10:35 AM
Maybe I’m misunderstanding, but this formula yielded a blank cell for me. I tried it three different ways:
1
TRIM(MID({Original Publisher Info}, FIND(":", Name) + 1, FIND(",", Name) - FIND(",", Name) - 1 ))
2
TRIM(MID({Original Publisher Info}, FIND(":", Place) + 1, FIND(",", Press) - FIND(",", Year) - 1 ))
3
TRIM(MID({Original Publisher Info}, FIND(":", Place + 1))
The third way, I would have repeated separately for each pull-out field, but I got an error message saying, “Can’t save field because it causes a circular reference.”
Oct 23, 2020 10:54 AM
A: If your field is named {Original Publisher Info}
you have to replace the name of the field everywhere, not just at the start of the formula. The formula I gave only ever references one field.
B: Your separators are, in order: the opening parenthesis, the colon, the comma, the closing parenthesis. If your publish location is NY
, then the START of that isn’t the colon.
C: The pattern for the FIND formulas as I wrote them are START, END, START. you did START, END, END.
😧 If you got the circular reference error that means you tried to reference the field {Place}
inside of the formula for the field {Place}
, which as you’ve learned you cannot do. Aside from that not being the pattern I suggested, there shouldn’t be a colon in your {Place}
field anyway.
Oct 23, 2020 10:58 AM
But I can’t use NY as the start because there are countless places in these records, not just NY
Oct 23, 2020 11:02 AM
^ You will find the separator which precedes “NY” (and literally any other state) in the list I gave you.
Oct 23, 2020 11:39 AM
Here is @Kamille_Parks’s excellent formula reformatted to make it a little easier to see the logical parts.
TRIM(
MID(
{Field Name},
FIND("starting separator", {Field Name}) + 1,
FIND("ending separator", {Field Name}) - FIND("starting separator", {Field Name}) - 1
)
)
TRIM
just removes any extra spaces at the beginning or end of the string
MID
says you want text in the middle of the string
The three parameters for MID
tell you source for the string, where to start, and how many characters to grab.
Note that if your desired {PublishPlace} or {Publisher} has a comma, you may also have problems.
As for converting NY to New York, I recommend doing that in a different formula field after you have isolated the state.
Oct 23, 2020 11:42 AM
What should I put in for “starting separator” if the place can be any of 1,000 different places other than NY?
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.