string - Excel formula to extract a number preceding an x not working

Question

I'm using the following formula to extract the number preceding an "x" from a string (e.g. ##x## where # equals a number 0-9) but with I have other words in the string that have an "x" in them, the formula doesn't work.

Here's the formula:

=IF(ISBLANK(A154),"",IFERROR(IF(VALUE(MID(A154,MIN(FIND({"1","2","3","4","5","6","7","8","9","0"},A154 & "(1,2,3,4,5,6,7,8,9,0")),FIND(INDEX(SET_TERMS,MATCH(1,COUNTIF(A154,"*"&$R$2:$R$5&"*"),0)),A154,1)-MIN(FIND({"1","2","3","4","5","6","7","8","9","0"},A154 & "(1,2,3,4,5,6,7,8,9,0"))))<=1,"",TRIM(MID(A154,MIN(FIND({"1","2","3","4","5","6","7","8","9","0"},A154 & "(1,2,3,4,5,6,7,8,9,0")),FIND(INDEX(SET_TERMS,MATCH(1,COUNTIF(A154,"*"&$R$2:$R$5&"*"),0)),A154,1)-MIN(FIND({"1","2","3","4","5","6","7","8","9","0"},A154 & "(1,2,3,4,5,6,7,8,9,0")))&" sets")),""))

Notes: SET_TERMS ($R$2:$R$5) is a list: rounds, set, sets, x.

Here are examples where the formula works fine:

Skater jumps 3x5 each side  RESULT 3 sets
Russian Twist 3x30 seconds  RESULT 3 sets
Push-ups 3x max   RESULT 3 sets
Y holds 3x30 seconds   RESULT 3 sets

Now, here are two examples of the strings that return a blank because Flexion and Extension have "x" in them:

Neck Flexion 3x20 seconds  RESULT Blank
Neck Extension 3x20 seconds Result Blank

Any ideas on how to fix this?

Thanks

Answer 1

You just need to be more specific in what you are looking for.

For example, the following will return the digit prior to the x:

=MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9}&"x",A1&"0x1x2x3x4x5x6x7x8x9x")),1)

If you have Windows Excel 2013+ or O365, and you need to deal with multiple digit numbers, the following will extract space-separated "nodes" that have the pattern of ddx, where dd can be any number (including decimals). You can then use string functions to extract just the number.

=FILTERXML("<t><s>" & SUBSTITUTE(A1," ","</s><s>") & "</s></t>","//s[boolean(number(substring-before(.,'x')))]")