MLA to Bookseller Marketplace Formula

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:

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

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.

D: 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.

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

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

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.

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

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

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?

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

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}

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?

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

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

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

I don’t know where to click Solution because it’s kind of all over the place in this exchange :joy:

Dealer’s choice. Every formula we gave you works, so any reply with a formula in it is the solution. I recommend either the very first formula given or the very last one (the reply which describes {Place} and {Year}).