I don't have one.

Why not re-post in:

http://www.microsoft.com/office/comm...&lang=en&cr=US

and request a non-VBA solution.
--
Gary''s Student - gsnu200805


"Lewis" wrote:

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