Posted previously by Lars-Åke Aspelin...

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10 ^(300-ROW($1:$300))),2,300)

This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER
rather than just ENTER.

It has the following (known) limitations:

- The input string in cell A1 must be shorter than 300 characters

- There must be at most 14 digits in the input string.
(Following digits will be shown as zeroes.)

Maybe of no pratical use, but it will also handle the following two cases
correctly:

- a "0" as the first digit in the input will be shown correctly in the
output

- an input without any digits at all will give the empty string as output
(rather than 0).

--
Rick (MVP - Excel)


"Lewis" wrote in message
...
Hi,

Thanks for that which works fine. Any thoughts on a formula solution.



"Gary''s Student" wrote:

See Rothstein's reply in:

http://groups.google.com/group/micro...bdf9bb8eefa583

--
Gary''s Student - gsnu200805


"Lewis" wrote:

Hi,

The examples below are a typical but not an exhaustive list of the
types of
equipment numbers in a maintenance records system. If it matters; and I
suspect it doesn't, the letters represent the type of equipment:-

P= Pump
K=Fan
LICA= Level Indicator Cotrol Alarm
XE = Automatic emergency stop
HE = Hand emergency stop
an on and on there are a myriad of types

What I need to to is in a seperate column extract just the numbers as
in the
examples below. The maximum string length is 20 characters and there
can be
up to 4 groups of numbers. There are multiple posts similar to this and
I've
tried lots but because of the randomness of the number/character mix
they all
fail.

Most posts seem to rely on MID etc which involves searching for a
particular
delimiter and none of these work. Typical of others I've tried a-

=LOOKUP(10^23,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))

The second one comes close and can extract any single group of numbers
but
fails if the numbers are split by letters into 2 or more groups. While
not
averse to VB I prefer a formula.

Any help would be most appreciated.

11HE1245 = 111245
P2475B - 2475
11XE1234 - 111234
LC1278 - 1278
FRICA1428 - 1428
LICA1235
K1407
12LUX23E

Lew