Just to add to Mike's response, there were some limitations that went along
with the formula from Lars-Åke Aspelin that he posted...

- 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 practical 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)


"Mike H" wrote in message
...
Hi,

Assuming you tag numbers are in A1 down put this in b1 and drag down. I
hesitate to note for what i think is an excellent formula that it has some
limitations but it works for all your posted examples.

Enter as an Array with Ctrl+Shift+Enter
Credit to Lars-Åke Aspelin who first posted this.

=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)

Mike


"Lewis" wrote:

Hi,

I originally posted this in general question and Gary's Student knidly
suggested i repost her.

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