Tag Numbers

[Lewis]

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 are:-

=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&"0123456789")),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

 

[Lewis]

Hi,

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

 

[Gary''s Student]

I don't have one.

Why not re-post in:

http://www.microsoft.com/office/com...ublic.excel.worksheet.functions&lang=en&cr=US

and request a non-VBA solution.

 

[Rick Rothstein]

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 Rothstein]

Well, I see someone posted the same formula I did over in the OTHER
newsgroup you posted to.

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.

 

[Gary''s Student]

The double posting is my fault Rick.

Lewis did it at my suggestion.

 

[Rick Rothstein]

Ah yes, I see that now. While it wouldn't have helped me with this
particular question (as I first saw the question here in this newsgroup), I
would have considered adding to your suggestion to Lewis something like
"...and mark your new posting as a duplicate to warn the volunteers there
that others might have answered this same question elsewhere". As it turns
out, Mike H (who provided the same formula as I did over in the other
newsgroup) covers both that other newsgroup and this one, so he probably
would have eventually done so in this newsgroup if the question had not been
double posted.

 

[Lewis]

gentlemen,

if I have caused a problem in posting this to another group then i am deeply
sorry, i did it on advisement from another respondent. that aside I am
grateful for the response from Rick & Mike who posted solutions to my
problem. Please be assured of my respect to the rules of this forum in future.

L