Home 
Search 
Today's Posts 
#31




A Different approach that may work
Ron  Don't know if you are still monitoring this or will receive an email
notification of this post but I wanted to thank you for this excellent tool. It's saved me a lot of time and trouble and its much appreciated. That's besides the fact that its going to make me look good too! Thanks, Bill "Ron Coderre" wrote: I've been playing with this formula, which pulls from the first digit found thru the last digit found: =MID(A1,MATCH(1,ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1+MAX(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIREC T("1:"&LEN(A1))))MATCH(1,ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)) Note: Commit that array formula by holding down the [Ctrl] and [Shift] keys when you press [Enter]. So... "OFF 114005ABC" would result in 114005 "OFF///130H" would result in 130 etc I think that handles all of the pertinent scenarios...let me know. *********** Regards, Ron "Ron Coderre" wrote: Nope....it misses punctuation within the numbers, as in: OFF 114005 *********** Regards, Ron "Ron Coderre" wrote: I know this is a bit late, but I just came up with this, which seems to work well: =MID(A1,MATCH(1,ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),COUNT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))) If finds the first digit location and the count of digits in the string and uses those values in the MID function. *********** Regards, Ron "Corey" wrote: Hello. I have a column with data that contain numerics and text: ON 3127 ON2679 O/F 20R OFF///130H Is there a formula I can input in an adjacent cell to only pull out the numbers to give me the following: 3127 2679 20 130 Any help is appreciated. Thanks! 
#32




A Different approach that may work
That post is a year and a half old, but feedback is always welcome. Thanks
for taking the time to let me know that formula helped you out., Bill. Much appreciated. *********** Best Regards, Ron XL2003, WinXP "BurghRocks" wrote: Ron  Don't know if you are still monitoring this or will receive an email notification of this post but I wanted to thank you for this excellent tool. It's saved me a lot of time and trouble and its much appreciated. That's besides the fact that its going to make me look good too! Thanks, Bill "Ron Coderre" wrote: I've been playing with this formula, which pulls from the first digit found thru the last digit found: =MID(A1,MATCH(1,ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1+MAX(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIREC T("1:"&LEN(A1))))MATCH(1,ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)) Note: Commit that array formula by holding down the [Ctrl] and [Shift] keys when you press [Enter]. So... "OFF 114005ABC" would result in 114005 "OFF///130H" would result in 130 etc I think that handles all of the pertinent scenarios...let me know. *********** Regards, Ron "Ron Coderre" wrote: Nope....it misses punctuation within the numbers, as in: OFF 114005 *********** Regards, Ron "Ron Coderre" wrote: I know this is a bit late, but I just came up with this, which seems to work well: =MID(A1,MATCH(1,ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),COUNT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))) If finds the first digit location and the count of digits in the string and uses those values in the MID function. *********** Regards, Ron "Corey" wrote: Hello. I have a column with data that contain numerics and text: ON 3127 ON2679 O/F 20R OFF///130H Is there a formula I can input in an adjacent cell to only pull out the numbers to give me the following: 3127 2679 20 130 Any help is appreciated. Thanks! 
#33




Extract Numerics only
This is the only one that works for me, but I lose the last number. My values
look more like this: 1+0756 1+0789AH 1+0478BK I know this is an old topic, but hopefully someone can help. "Domenic" wrote: Maybe... =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN (A1)LEN(SUBSTITUTE(A1,{"",0,1,2,3,4,5,6,7,8,9},"")))) ....which will return... 114005 040652 3127 2679 20 130 ....when you have the following... OFF 114005 ON 040652 ON 3127 ON2679 O/F 20R OFF///130H Hope this helps! In article , "Corey" wrote: Thanks for all the suggestions people. I'm not to familiar with VBA nor UDF. Also, I'm trying to put together a tool for a below novice user of Excel as it takes her hours to decipher a statement. I was hoping to use just a simple formula and have her cut and paste the data to have it automatically populate with this formula. I've tried yours Domenic, and it seems to work for the most part, except for some: OFF 114005 returns 769142 ON 040652 returns 40652 (losing the first 0) "Domenic" wrote: If your numbers are not more than 15 digits in length, try... =LOOKUP(9.99999999999999E+307,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14 ,15})) Otherwise, try... =LOOKUP(9.99999999999999E+307,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))) Hope this helps! In article , "Corey" wrote: Hello. I have a column with data that contain numerics and text: ON 3127 ON2679 O/F 20R OFF///130H Is there a formula I can input in an adjacent cell to only pull out the numbers to give me the following: 3127 2679 20 130 Any help is appreciated. Thanks! 
#34




Extract Numerics only
1+0756
1+0789AH 1+0478BK What results do you expect/want?  Biff Microsoft Excel MVP "katdot" wrote in message ... This is the only one that works for me, but I lose the last number. My values look more like this: 1+0756 1+0789AH 1+0478BK I know this is an old topic, but hopefully someone can help. "Domenic" wrote: Maybe... =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN (A1)LEN(SUBSTITUTE(A1,{"",0,1,2,3,4,5,6,7,8,9},"")))) ....which will return... 114005 040652 3127 2679 20 130 ....when you have the following... OFF 114005 ON 040652 ON 3127 ON2679 O/F 20R OFF///130H Hope this helps! In article , "Corey" wrote: Thanks for all the suggestions people. I'm not to familiar with VBA nor UDF. Also, I'm trying to put together a tool for a below novice user of Excel as it takes her hours to decipher a statement. I was hoping to use just a simple formula and have her cut and paste the data to have it automatically populate with this formula. I've tried yours Domenic, and it seems to work for the most part, except for some: OFF 114005 returns 769142 ON 040652 returns 40652 (losing the first 0) "Domenic" wrote: If your numbers are not more than 15 digits in length, try... =LOOKUP(9.99999999999999E+307,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14 ,15})) Otherwise, try... =LOOKUP(9.99999999999999E+307,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))) Hope this helps! In article , "Corey" wrote: Hello. I have a column with data that contain numerics and text: ON 3127 ON2679 O/F 20R OFF///130H Is there a formula I can input in an adjacent cell to only pull out the numbers to give me the following: 3127 2679 20 130 Any help is appreciated. Thanks! 
#35




Extract Numerics only
I need to pull out just the numbers. So ...
1+0756 10756 1+0789AH 10789. The formula from Domenic gives me results, but chops off the last number. So .... 1+0478BK 1+047 1+0756 1+075 1+0789AH 1+078 I don't have enough knowledge to figure out what's going wrong. "T. Valko" wrote: 1+0756 1+0789AH 1+0478BK What results do you expect/want?  Biff Microsoft Excel MVP "katdot" wrote in message ... This is the only one that works for me, but I lose the last number. My values look more like this: 1+0756 1+0789AH 1+0478BK I know this is an old topic, but hopefully someone can help. "Domenic" wrote: Maybe... =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN (A1)LEN(SUBSTITUTE(A1,{"",0,1,2,3,4,5,6,7,8,9},"")))) ....which will return... 114005 040652 3127 2679 20 130 ....when you have the following... OFF 114005 ON 040652 ON 3127 ON2679 O/F 20R OFF///130H Hope this helps! In article , "Corey" wrote: Thanks for all the suggestions people. I'm not to familiar with VBA nor UDF. Also, I'm trying to put together a tool for a below novice user of Excel as it takes her hours to decipher a statement. I was hoping to use just a simple formula and have her cut and paste the data to have it automatically populate with this formula. I've tried yours Domenic, and it seems to work for the most part, except for some: OFF 114005 returns 769142 ON 040652 returns 40652 (losing the first 0) "Domenic" wrote: If your numbers are not more than 15 digits in length, try... =LOOKUP(9.99999999999999E+307,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14 ,15})) Otherwise, try... =LOOKUP(9.99999999999999E+307,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))) Hope this helps! In article , "Corey" wrote: Hello. I have a column with data that contain numerics and text: ON 3127 ON2679 O/F 20R OFF///130H Is there a formula I can input in an adjacent cell to only pull out the numbers to give me the following: 3127 2679 20 130 Any help is appreciated. Thanks! 
#36




Extract Numerics only
On Tue, 8 Jan 2008 11:20:02 0800, katdot
wrote: This is the only one that works for me, but I lose the last number. My values look more like this: 1+0756 1+0789AH 1+0478BK I know this is an old topic, but hopefully someone can help. This UDF will return all the numbers in the string. If there are NO numbers, it returns #VALUE! That can be modified, if you wish. ================================ Option Explicit Function Nums(str As String) As Double Dim re As Object, mc As Object Const sPat As String = "\D" Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = sPat Nums = re.Replace(str, "") End Function =================================== To enter this <altF11 opens the VBEditor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code above into the window that opens. Then use the formula =Nums(cell_ref) on your worksheet. ron 
#37




Extract Numerics only
Solution from Harlan Grove A1: abc123def456ghi789 First, create a Named Formula Names in Workbook: Seq Refers to: =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1)) This ARRAY FORMULA (committed with CTRL+SHIFT+ENTER, instead of just ENTER) removes ALL nonnumerics from a string. In sections, for readability: B1: =SUM(IF(ISNUMBER(1/(MID(A1,seq,1)+1)),MID(A1,seq,1)* 10^MMULT((seq<TRANSPOSE(seq)),ISNUMBER(1/(MID(A1,seq,1)+1))))) In the example, the formula returns: 123456789 Does that help?  Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "katdot" wrote in message ... I need to pull out just the numbers. So ... 1+0756 10756 1+0789AH 10789. The formula from Domenic gives me results, but chops off the last number. So ... 1+0478BK 1+047 1+0756 1+075 1+0789AH 1+078 I don't have enough knowledge to figure out what's going wrong. "T. Valko" wrote: 1+0756 1+0789AH 1+0478BK What results do you expect/want?  Biff Microsoft Excel MVP "katdot" wrote in message ... This is the only one that works for me, but I lose the last number. My values look more like this: 1+0756 1+0789AH 1+0478BK I know this is an old topic, but hopefully someone can help. "Domenic" wrote: Maybe... =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN (A1)LEN(SUBSTITUTE(A1,{"",0,1,2,3,4,5,6,7,8,9},"")))) ....which will return... 114005 040652 3127 2679 20 130 ....when you have the following... OFF 114005 ON 040652 ON 3127 ON2679 O/F 20R OFF///130H Hope this helps! In article , "Corey" wrote: Thanks for all the suggestions people. I'm not to familiar with VBA nor UDF. Also, I'm trying to put together a tool for a below novice user of Excel as it takes her hours to decipher a statement. I was hoping to use just a simple formula and have her cut and paste the data to have it automatically populate with this formula. I've tried yours Domenic, and it seems to work for the most part, except for some: OFF 114005 returns 769142 ON 040652 returns 40652 (losing the first 0) "Domenic" wrote: If your numbers are not more than 15 digits in length, try... =LOOKUP(9.99999999999999E+307,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14 ,15})) Otherwise, try... =LOOKUP(9.99999999999999E+307,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))) Hope this helps! In article , "Corey" wrote: Hello. I have a column with data that contain numerics and text: ON 3127 ON2679 O/F 20R OFF///130H Is there a formula I can input in an adjacent cell to only pull out the numbers to give me the following: 3127 2679 20 130 Any help is appreciated. Thanks! 
#38




Extract Numerics only
That worked beautifully. It even copied into the attachment to send the boss.
Thank you so much! I just hope my coworkers don't think this means I have all the answers. 
#39




Extract Numerics only
The Ron's are on a roll. This worked too. I might go with this, since I've
very little knowledge of macros in excel. I'm more comfortable with formulas and functions, but it's nice to know I have more than one option. You guys are great! Thanks! "Ron Coderre" wrote: Solution from Harlan Grove A1: abc123def456ghi789 First, create a Named Formula Names in Workbook: Seq Refers to: =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1)) This ARRAY FORMULA (committed with CTRL+SHIFT+ENTER, instead of just ENTER) removes ALL nonnumerics from a string. In sections, for readability: B1: =SUM(IF(ISNUMBER(1/(MID(A1,seq,1)+1)),MID(A1,seq,1)* 10^MMULT((seq<TRANSPOSE(seq)),ISNUMBER(1/(MID(A1,seq,1)+1))))) In the example, the formula returns: 123456789 Does that help?  Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "katdot" wrote in message ... I need to pull out just the numbers. So ... 1+0756 10756 1+0789AH 10789. The formula from Domenic gives me results, but chops off the last number. So ... 1+0478BK 1+047 1+0756 1+075 1+0789AH 1+078 I don't have enough knowledge to figure out what's going wrong. "T. Valko" wrote: 1+0756 1+0789AH 1+0478BK What results do you expect/want?  Biff Microsoft Excel MVP "katdot" wrote in message ... This is the only one that works for me, but I lose the last number. My values look more like this: 1+0756 1+0789AH 1+0478BK I know this is an old topic, but hopefully someone can help. "Domenic" wrote: Maybe... =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN (A1)LEN(SUBSTITUTE(A1,{"",0,1,2,3,4,5,6,7,8,9},"")))) ....which will return... 114005 040652 3127 2679 20 130 ....when you have the following... OFF 114005 ON 040652 ON 3127 ON2679 O/F 20R OFF///130H Hope this helps! In article , "Corey" wrote: Thanks for all the suggestions people. I'm not to familiar with VBA nor UDF. Also, I'm trying to put together a tool for a below novice user of Excel as it takes her hours to decipher a statement. I was hoping to use just a simple formula and have her cut and paste the data to have it automatically populate with this formula. I've tried yours Domenic, and it seems to work for the most part, except for some: OFF 114005 returns 769142 ON 040652 returns 40652 (losing the first 0) "Domenic" wrote: If your numbers are not more than 15 digits in length, try... =LOOKUP(9.99999999999999E+307,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14 ,15})) Otherwise, try... =LOOKUP(9.99999999999999E+307,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))) Hope this helps! In article , "Corey" wrote: Hello. I have a column with data that contain numerics and text: ON 3127 ON2679 O/F 20R OFF///130H Is there a formula I can input in an adjacent cell to only pull out the numbers to give me the following: 3127 2679 20 130 Any help is appreciated. Thanks! 
#40




Extract Numerics only
On Wed, 9 Jan 2008 12:54:04 0800, katdot
wrote: That worked beautifully. It even copied into the attachment to send the boss. Thank you so much! I just hope my coworkers don't think this means I have all the answers. Glad to help. Thanks for the feedback. ron 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Extract sub string  Excel Worksheet Functions  
VBA code to extract mcoefficient in linear trendlines from ALL charts  Excel Discussion (Misc queries)  
Trendline Extract  Charts and Charting in Excel  
Extract Unique Values, Then Extract Again to Remove Suffixes  Excel Discussion (Misc queries)  
Extract specific data into its own workbook via macro?  Excel Discussion (Misc queries) 