Extract a number which is sandwiched between text

System

This discussion was created from comments split from: extract part of a cell's text.

Glen Urquhart

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.

Paul Newcome

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

Glen Urquhart

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.

Paul Newcome

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

Glen Urquhart

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

Paul Newcome

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