MLA to Bookseller Marketplace Formula

[Alba_Machado2]

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:

[Kamille_Parks]

Split text in 1 column into 3 separate columns Formulas

Hello, I need to separate a complete mailing address into 3 columns. Currently the entire Mailing address is on column, per this picture. [image] In this case I would like to spilt the cell after Department and after 9701 so (Experian Dispute Department in is on Column) P.O. Box 9701 is in a another column and then Allen, TX 75013 is in another column.

[Alba_Machado2]

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 · Accepted solution]

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

[Alba_Machado2]

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

[Kamille_Parks]

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.

[Alba_Machado2]

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

[Kamille_Parks]

Kamille_Parks:

B: Your separators are, in order: the opening parenthesis, the colon, the comma, the closing parenthesis.

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

[kuovonne]

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.

[Alba_Machado2]

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