Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tag Numbers
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tag Numbers
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tag Numbers
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tag Numbers
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tag Numbers
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 (MVP - Excel) "Lewis" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tag Numbers
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. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Lewis" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tag Numbers
The double posting is my fault Rick.
Lewis did it at my suggestion. -- Gary''s Student - gsnu200805 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Lewis" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tag Numbers
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. -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... The double posting is my fault Rick. Lewis did it at my suggestion. -- Gary''s Student - gsnu200805 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Lewis" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tag Numbers
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 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... The double posting is my fault Rick. Lewis did it at my suggestion. -- Gary''s Student - gsnu200805 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Lewis" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP should compare numbers stored as text to plain numbers. | Excel Worksheet Functions | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
convert negative numbers to positive numbers and vice versa | Excel Worksheet Functions | |||
Count comma separated numbers, numbers in a range with dash, not t | Excel Discussion (Misc queries) |