Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: MLA to Bookseller Marketplace Formula

Solved
Jump to Solution
2577 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Alba_Machado2
7 - App Architect
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
Kamille_Parks
16 - Uranus
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 ))

See Solution in Thread

21 Replies 21

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:

Kamille_Parks
16 - Uranus
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 ))

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.

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

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