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?