Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

# MLA to Bookseller Marketplace Formula

Topic Labels: Formulas
Solved
747 21
cancel
Showing results for
Did you mean:
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:

1 Solution

Accepted Solutions
16 - Uranus

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 ))`

21 Replies 21
16 - Uranus
7 - App Architect

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:

16 - Uranus

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 ))`

7 - App Architect

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.”

16 - Uranus

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.

7 - App Architect

But I can’t use NY as the start because there are countless places in these records, not just NY

16 - Uranus

^ You will find the separator which precedes “NY” (and literally any other state) in the list I gave you.

18 - Pluto

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.

7 - App Architect

What should I put in for “starting separator” if the place can be any of 1,000 different places other than NY?

16 - Uranus

Once again:

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

18 - Pluto

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?

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 separator

16 - Uranus

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}`

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

16 - Uranus

OR Kuovonne’s clarified version:

18 - Pluto

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

7 - App Architect

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

16 - Uranus

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

7 - App Architect

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