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

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

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/

then use this formula

=REGEX.SUBSTITUTE(A2,"\D+","")



"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

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

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

Mike,

Can you pl. explain how this works?



"Mike H" wrote:

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

Hmm! I posted that same formula over in the OTHER newsgroup you asked your
question in.

PLEASE CONSIDER THIS PREVIOUS POST by Jeff Johnson when you post new
questions in the future!

"You have posted this question individually to multiple groups.
This is called Multiposting and it's BAD. Replies made in one
group will not be visible in the other groups, which may cause
multiple people to respond to your question with the same answer
because they didn't know someone else had already done it. This
is a waste of time.

If you MUST post your message to multiple groups, post a single
message and select all the groups (or type their names manually
in the Newsgroups field, separated by commas) in which you want
it to be seen. This is called Crossposting and when used properly
it is GOOD."

Some additional comment previously posted by me:

"You may not see this as a problem, but those of us who volunteer
answering questions on newsgroups do see it as a problem. You can't
imagine how annoying it is for a volunteer to read a question,
research background material, test sample code and then formulate
and post an answer to the original question only to go to another
newsgroup and find the question posted and ALREADY answered over
there. On top of that, if you cross-post your question, all of the
readers in all the newsgroups it is cross-posted to see both the
original question and all of the answers given to it. This is
beneficial to you because then we can add additional material to,
add clarification to, as well as add additional examples to an
answer you have received previously... that means you end up with
a more complete solution to your problem. This is a win-win
situation for all of us."

And if you are using a web interface that does not allow you to specify
multiple newsgroups as indicated above, then simply pick one newsgroup, post
your message and **wait**... most of the volunteers here visit all the
newsgroups, so you should get the same answers anyway.

--
Rick (MVP - Excel)


"Sheeloo" wrote in message
...
Mike,

Can you pl. explain how this works?



"Mike H" wrote:

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

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

Hello Rick,

Sorry for violating the protocol.

I jsut wanted to understand how the formula works so that I can adapt it in
future and also help others...

When I had asked the questions I had used the formula provided without
understanding how it worked..

Regards,
Sheeoo

"Rick Rothstein" wrote:

Hmm! I posted that same formula over in the OTHER newsgroup you asked your
question in.

PLEASE CONSIDER THIS PREVIOUS POST by Jeff Johnson when you post new
questions in the future!

"You have posted this question individually to multiple groups.
This is called Multiposting and it's BAD. Replies made in one
group will not be visible in the other groups, which may cause
multiple people to respond to your question with the same answer
because they didn't know someone else had already done it. This
is a waste of time.

If you MUST post your message to multiple groups, post a single
message and select all the groups (or type their names manually
in the Newsgroups field, separated by commas) in which you want
it to be seen. This is called Crossposting and when used properly
it is GOOD."

Some additional comment previously posted by me:

"You may not see this as a problem, but those of us who volunteer
answering questions on newsgroups do see it as a problem. You can't
imagine how annoying it is for a volunteer to read a question,
research background material, test sample code and then formulate
and post an answer to the original question only to go to another
newsgroup and find the question posted and ALREADY answered over
there. On top of that, if you cross-post your question, all of the
readers in all the newsgroups it is cross-posted to see both the
original question and all of the answers given to it. This is
beneficial to you because then we can add additional material to,
add clarification to, as well as add additional examples to an
answer you have received previously... that means you end up with
a more complete solution to your problem. This is a win-win
situation for all of us."

And if you are using a web interface that does not allow you to specify
multiple newsgroups as indicated above, then simply pick one newsgroup, post
your message and **wait**... most of the volunteers here visit all the
newsgroups, so you should get the same answers anyway.

--
Rick (MVP - Excel)