CODEWITHSUNDEEP
excelexcel-formulasubstring
romeo b
romeo b

Reputation: 13

Extract word within string in Excel row

I am trying to pull some information from one string of text into it's own field and wonder if somebody can help. Sounds simple enough and I can get it working how I want, but when the position of the text moves around, it can't find it. I've tried using Mid, left, right, without much success.

It is a basic spreadsheet used as a job list, to monitor which country each item was sent to. Or, whether it wasn't dispatched using a courier, and nothing is required to be noted.

Hopefully my photo explains it, and the formula in this photo is:

=MID(LEFT(A1,FIND(")",A1)-1),FIND("(Shipped to ",A1)+12,LEN(A1))

I have rows of text showing something along the lines of:

and my code above will pull through the country, as I want.

.. but ever so often, each line will flip around, so show like:

.. which upsets the formula due to the left/right/mid positioning.

This is an ongoing spreadsheet, so I'd like to be using a single formula if possible. I know I can workaround by running second formulas on erroneous entries but would rather not do that.

Lastly, when there was no shipping involved, it generates a value error (as it couldn't find the string whatsoever). Again, I could make this say "No shipping" by running a second formula, but not ideal.

Anyone got any better simpler ideas? Photo attached for reference!

I'm just looking to pull out the word after "(Shipped to " and before the following ")" regardless of it's relative position in the line of text.

Thanks a lot

Photo: https://i.sstatic.net/HU6lB.png

Upvotes: 1

Views: 85

Answers (1)

Mech
Mech

Reputation: 4015

=MID(MID(A1,FIND("SHIPPED TO",UPPER(A1),1)+11,25),1,FIND(")",MID(A1,FIND("SHIPPED TO",UPPER(A1),1)+11,25),1)-1)

This takes a 25 character slice of the string and looks at it. If it's at the end of the string, it won't matter. From there, it will dissect it further by only using that slice to work from.

Upvotes: 1

Related Questions