I need a formula that performs a specific mathematical operation, but only with the number that meets specific conditions. In this case – with numbers extracted from strings with specific text in them.

In the first column we have some raw data: a string with different numbers and text divided by the underscore. I need to split this data into several different rows and use the following formula for this: =TRANSPOSE(SPLIT(A3,"_"))

The next column should only contain numbers, but the problem is that one of these numbers (which contains "tb" in this specific example) should be divided or multiplied by the specific number (multiplied by 1000 in this case).

I've tried the following formula which only works as long as there is no "tb" or if it's in the very beginning of the string: =IF(REGEXMATCH(A6,"tb"),REGEXEXTRACT(A6,"(\d )tb")*1000,REGEXEXTRACT(B6,"(\d )"))

If it's somewhere in the middle or at the end of the string only the first number still undergoes the math operation instead. I wonder if there's a way to achieve the result I want without resorting to complex formulas (I'm very new to this and would ideally like to use formulas that I can understand and easily modify for other similar tasks). A sample table for better visualisation can be seen below. Thanks in advance!

CodePudding user response：

Try not putting tb in the first REGEXEXTRACT:

=IF(REGEXMATCH(A6,"tb"),REGEXEXTRACT(A6,"(\d )")*1000,REGEXEXTRACT(B6,"(\d )"))

EDIT

Option 2: to extract the numbers adjacent before "tb"

=IF(REGEXMATCH(A6,"tb"),REGEXEXTRACT(A6,"(\d )tb")*1000,REGEXEXTRACT(B6,"(\d )"))