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

KaseyK

@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

Kelly Moore

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

KaseyK

Those all worked perfectly, thanks so much!