(2nd attempt to post this reply. I got "burned" by the x_y_z rule!)
Let's take a look at this example.
A1 = x12x
We want to extract the numeric portion of that string. We know that the
number will *always* be 2 consecutive digits but they could be located
anywhere in the string:
12xx
x12x
xx12
=--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),2)
Returns 12 (as a numeric number)
Here's how it works...
First thing we need to do is find the location of the first digit within the
string. Since the number in the string can contain any of the digits 0 to 9
we have to look for each of those individual digits.
FIND returns the starting position number of the substring to look for
within the string. For example:
A1 = abc
FIND("a",A1) = 1, the substring "a" is located at position 1 within the
string
abc
FIND("b",A1) = 2, the substring "b" is located at position 2 within the
string
abc
FIND("c",A1) = 3, the substring "c" is located at position 3 within the
string
abc
FIND("z",A1) = #VALUE!, the substring "z" can not be found in the string
abc
Note that FIND is case sensitive.
FIND("A",A1) = #VALUE!, the substring "A" can not be found in the string
abc.
Also, FIND will "find" the first instance of the substring within the
string.
A1 = x12x
FIND("x",A1) = 1, the first instance of x is located at position 1
So, we use FIND to get the starting position numbers of the digits 0 to 9 in
the string x12x. The problem with this is that since the number in the
string is only 2 digits we will get those #VALUE! errors for the digits that
can't be found. For example:
A1 = x12x
FIND({0,1,2,3,4,5,6,7,8,9},A1)
FIND(0,A1) = #VALUE!
FIND(1,A1) = 2
FIND(2,A1) = 3
FIND(3,A1) = #VALUE!
FIND(4,A1) = #VALUE!
FIND(5,A1) = #VALUE!
FIND(6,A1) = #VALUE!
FIND(7,A1) = #VALUE!
FIND(8,A1) = #VALUE!
FIND(9,A1) = #VALUE!
So, if we try that approach the result of the formula will be the error
#VALUE!. That doesn't help us do what we need to do!
Here's how we get around all those #VALUE! errors. We make sure that every
digit can be found in the string. We do that by concatenating all of the
individual digits to the end of the string:
A1&"0123456789"
Now FIND looks for the digits in the string:
x12x0123456789
This ensures that each digit will be found and eliminates the #VALUE! error
problem.
So:
A1 = x12x
FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")
FIND(0,A1&"0123456789") = 5
FIND(1,A1&"0123456789") = 2
FIND(2,A1&"0123456789") = 3
FIND(3,A1&"0123456789") = 8
FIND(4,A1&"0123456789") = 9
FIND(5,A1&"0123456789") = 10
FIND(6,A1&"0123456789") = 11
FIND(7,A1&"0123456789") = 12
FIND(8,A1&"0123456789") = 13
FIND(9,A1&"0123456789") = 14
These position numbers are then passed to the MIN function
MIN({5,2,3,8,9,10,11,12,13,14})
MIN returns the minimum number from the array {5,2,3,8,9,10,11,12,13,14} =
2. This is the starting position of the first digit within the string x12x.
The MIN number is then passed to the MID function:
MID(A1,2,2)
Starting at position 2 of the string x12x return the next 2 characters =
"12". MID *always* returns a TEXT value even if it looks like a number. To
convert that TEXT "12" to the numeric number 12 we use the double unary
minus --.
--MID(A1,2,2)
So, to extract the numeric portion of the string x12x:
=--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),2)
exp101
