Hi Lewis

I'm not sure if it can be done by formula, but I created a little UDF which
can be called from your worksheet.

The code below is to be inserted in a standard module. ALT + F11 to open the
VBA editor, goto "Insert" and select module. Copy the code below to the
module and close the VBA editor.

Public Function FindNumber(iString As String) As Long
iStrLen = Len(iString)
For c = 1 To iStrLen
If IsNumeric(Mid(iString, c, 1)) Then
Res = Res & Mid(iString, c, 1)
End If
Next
If Res = "" Then Res = "#Value"
FindNumber = Res
End Function

With your inputstring in A1 enter =FindNumber(A1) in the desired cell.

Hopes it helps although I used some code.

Regards,
Per



"Lewis" skrev i meddelelsen
...
Apologies, I never made it clear. My request if for a worksheet formula or
confirmation I need to resort to code.

"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