In summary, it looks for all the numeric values (and adds 0123456789 to the
end to ensure all are found), and then builds an array of all numeric
strings within the overall string (the MIN is used to find the first one,
and thus where to start). LOOKUP with a lookup value of
9.99999999999999E+307 (which is the largest number that can be stored in a
cell) if it can't find a value (which it shouldn't using
9.99999999999999E+307 <vbg) will return the largest value in the array,
which will be the final string in this case.

Using an example of ABC123 in A1.

SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")) will return an array of
{7,4,5,6,11,12,13,14,15,16} - not that these numbers relate to the position
in A1, but some are greater than 6 because we appended 0123456789

MIN will the return 4 - self explanatory

ROW(INDIRECT("1:"&LEN(A1))) returns an array of {1;2;3;4;5;6}which is passed
to the MID function to extract sub-strings

MID(A1, SEARCH(...),ROW(...)) then builds an array of numeric substrings
{"1";"12";"123";"123";"123";"123"} - note that we use the length of A1 in
case it is all numeric, but it does mean that the final numeric substring,
123, gets repeated 3 more times because of the 3 letters at the start

The -- is just used to transform the array to numeric values

LOOKUP then lookups 9.99999999999999E+307 in that array, doesn't find it, so
it returns the largest value found, 123

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Gizmo63" wrote in message
...
Hi Bob,

I take it back about needing a macro if variable :-o

Can you explain the elements in this please? With the 9.9... and {} etc
I'm
lost.
Cheers

Giz

"Bob Phillips" wrote:

Regardless of position


=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"
0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Scoops" wrote in message
oups.com...
Hi Thomas

If your data is fixed at aaannn then you can use:

=RIGHT(A1,3)

Regards

Steve