Extract a number which is sandwiched between text
Answers

Hello @Paul Newcome,
Hope you're well.
I've been playing around with your above formula, to make a variation, however am having little luck.
I was wondering if you could take at what I'm trying to achieve and let me know your thoughts:
A 3rd party app I use pulls through text strings, similar to the below into a sheet.
"K Pegs (Amount: 2.50 GBP, Quantity: 1, Color: Silver RAL 9022),GBP,2.50,Array"
Regardless of the lenght of the string, the final part will always display "),GBP,Numeric Value,Array"
This is the only occasion in the text string which ")GBP," and "Array" are used, they are unique in this position.
The numeric value will range from 1.00, to 100000.00.
I would like to extract just that number which is sandwiched between ")GBP," and "Array"
(2.50 in the example above)
Any advice would be greatly appreciated as always.
Thank you, Glen.

@Glen Urquhart Try something like this:
=MID([Column Name]@row, FIND("GPB,", [Column Name]@row) + 4, FIND(",Array", [Column name]@row)  (FIND("GPB,", [Column Name]@row) + 4))

Thank you for getting back to me Paul, Unfortunatly this hasn't pulled through the correct area.
The problem I had with my 'closest so far' variant (I no longer have a copy of the formula) was that it was recognising the first close bracket as part of the formula, instead of part of the text string.
Below results using your formual provided:
(The row above the rown which shows the formula is the one used in my previous example.
Cheers, Glen.

Ah. Ok. I didn't realize there was more than one "GPB" in the string. Is the number of times that appears consistent (always the second occurrence)?

Unfortunatly not;
If the form is submitted for 1 type of product "GBP" will occur twice in the string  once in the product list, and then once again at the end.
If the form is submitted for 4 types of product, "GBP" will occur 5 times in the string  four times in the product list, and then once again at the end.
The only unique charachteristic, is that the "GBP" at the end (where is would like to extract the value from) is always preceeded by a close bracket, and then a comma i.e.: "),GBP" where as other instances in the form have no characters directly next to the start of "GBP".

In that case, try this:
=MID([Column Name]@row, FIND(",GPB,", [Column Name]@row) + 5, FIND(",Array", [Column name]@row)  (FIND(",GPB,", [Column Name]@row) + 5))

@Paul Newcome  hoping you can help me here
This formula is working great for rows where the criteria fits  but can it be an IF statement so rows that dont have brackets are just blank?
Additionally, how can I parse the second and third sets of brackets into their separate cells for the same purpose? These are effectively helper cells to draft a document with these details

Hey @KaseyK
Here's one approach for determining the contents of multiple brackets. The first IF looks to see if any brackets are present. The second IF evaluates how many bracket pairs are found in your string. The substitute function is used to leverage it's ability to specify which occurrence of the search term is being addressed. The "~" is a placeholder for that named occurrence.
First set of brackets, if any
=IF(FIND("[", Description@row) > 0, MID(Description@row, FIND("[", Description@row) + 1, FIND("]", Description@row)  1  FIND("[", Description@row)))
(If desired, you could use the MID formula below (replacing 2's with 1's) in place of your MID formula: =IF(FIND("[", Description@row) > 0, MID(Description@row, FIND("~", SUBSTITUTE(Description@row, "[", "~", 1)) + 1, FIND("~", SUBSTITUTE(Description@row, "]", "~", 1))  1  FIND("~", SUBSTITUTE(Description@row, "[", "~", 1))))
Second set of brackets, if any
=IF(FIND("[", Description@row) > 0, IF((LEN(JOIN(Description@row))  LEN(SUBSTITUTE(JOIN(Description@row), "[", ""))) / LEN("[") > 1, MID(Description@row, FIND("~", SUBSTITUTE(Description@row, "[", "~", 2)) + 1, FIND("~", SUBSTITUTE(Description@row, "]", "~", 2))  1  FIND("~", SUBSTITUTE(Description@row, "[", "~", 2)))))
Third set of brackets, if any
=IF(FIND("[", Description@row) > 0, IF((LEN(JOIN(Description@row))  LEN(SUBSTITUTE(JOIN(Description@row), "[", ""))) / LEN("[") > 2, MID(Description@row, FIND("~", SUBSTITUTE(Description@row, "[", "~", 3)) + 1, FIND("~", SUBSTITUTE(Description@row, "]", "~", 3))  1  FIND("~", SUBSTITUTE(Description@row, "[", "~", 3)))))
etc
Will this work for you?
Kelly 
Those all worked perfectly, thanks so much!
Help Article Resources
Categories
Check out the Formula Handbook template!