Search code examples
stringtextexcel-formulaextract

Need excel formula to extract a single or double digit number preceding a character or symbol

Here's the case I have a column with a number of text strings. Each string contains either a single or double-digit number followed by either an "x" or the words " set" or " rounds." I'm trying to extract the numbers preceding the "x" or the words. Here's an example:

string Desired Outcome
jump 3x10 3
push 10x3 10
pull 3 sets 10 times 3
pull 3 rounds 8 times 3
push 10 times 3 sets 3

I've tried FIND, SEARCH, {1,2,3,4, 5, 6,7, 8, 9} only to over-complicate this. There has to be a simple way to locate these combinations (##&"x", "## sets" or ""## rounds") and extract the related numbers.

Solution

  • Assume "String" data housed in Column A1:A6 with header.

    In "Outcome" B2, formula copied down :

    =LOOKUP(9^9,0+RIGHT(LEFT(A2,MIN(SEARCH({"x"," sets"," rounds"},A2&"x sets rounds"))-1),ROW(A$1:A$250)))