If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Extract Numerics only
On Thu, 1 Dec 2005 09:26:02 -0800, "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! If you just have single numbers, as in the above, then 1. Download and install Laurent Longre's free morefunc.xll add-in from http://xcell05.free.fr/ 2. Then use this formula: =REGEX.MID(A1,"\d+") --ron |
#12
|
|||
|
|||
Extract Numerics only
On Thu, 01 Dec 2005 17:58:01 -0500, Ron Rosenfeld
wrote: On Thu, 1 Dec 2005 09:26:02 -0800, "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! If you just have single numbers, as in the above, then 1. Download and install Laurent Longre's free morefunc.xll add-in from http://xcell05.free.fr/ 2. Then use this formula: =REGEX.MID(A1,"\d+") --ron The following formula will also work, and have the advantage of being able to pull out ALL numbers -- even if they are not contiguous: =REGEX.SUBSTITUTE(A1,"[^0-9]","") --ron |
#13
|
|||
|
|||
Extract Numerics only
What a good idea. If you don't want to install that DLL, you could use
Harlan Grove's SubStr regular expression UDF, and then use this formula =--SubStr(SubStr(A1,"[^0-9 \t]{2,}"," "),"[^0-9]","") '----------------------------------------- Function Substr(orig_text As String, _ match_pat As String, _ replace_pat As String, _ Optional instance As Variant) As Variant '----------------------------------------- 'Similar to Excel's SUBSTITUTE but using VBScript's perl-like regexps 'Required: ' orig_text - string to search through, ' match_pat - regexp to find, ' replace_pat - replacement pattern 'Optional: ' instance - which matched substring to replace or 0 for all (default) '----------------------------------------- Dim regex As Object, matches As Object, m As Object If IsMissing(instance) Then instance = 0 ElseIf TypeName(instance) "Double" Then Substr = CVErr(xlErrValue) 'invalid instance type instance = -1 ElseIf CDbl(instance) = 0.5 Then Substr = CVErr(xlErrNum) 'invalid instance value instance = -1 Else instance = Int(instance + 0.5) End If If instance = -1 Then Exit Function 'do nothing quickly Set regex = CreateObject("vbscript.regexp") regex.Pattern = match_pat regex.Global = True If instance = 0 Then Substr = regex.Replace(orig_text, replace_pat) Else Set matches = regex.Execute(orig_text) If instance matches.Count Then Substr = orig_text 'matchnum out of bounds - do nothing Else Set m = matches.Item(instance - 1) Substr = Left(orig_text, m.FirstIndex) & _ regex.Replace(m.Value, replace_pat) & _ Right(orig_text, Len(orig_text) - m.FirstIndex - m.Length) End If End If End Function -- HTH RP (remove nothere from the email address if mailing direct) "Ron Rosenfeld" wrote in message ... On Thu, 1 Dec 2005 09:26:02 -0800, "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! If you just have single numbers, as in the above, then 1. Download and install Laurent Longre's free morefunc.xll add-in from http://xcell05.free.fr/ 2. Then use this formula: =REGEX.MID(A1,"\d+") --ron |
#14
|
|||
|
|||
Extract Numerics only
Taking Ron's lead, just use
=---SubStr(A1,"[^0-9]","") -- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... What a good idea. If you don't want to install that DLL, you could use Harlan Grove's SubStr regular expression UDF, and then use this formula =--SubStr(SubStr(A1,"[^0-9 \t]{2,}"," "),"[^0-9]","") '----------------------------------------- Function Substr(orig_text As String, _ match_pat As String, _ replace_pat As String, _ Optional instance As Variant) As Variant '----------------------------------------- 'Similar to Excel's SUBSTITUTE but using VBScript's perl-like regexps 'Required: ' orig_text - string to search through, ' match_pat - regexp to find, ' replace_pat - replacement pattern 'Optional: ' instance - which matched substring to replace or 0 for all (default) '----------------------------------------- Dim regex As Object, matches As Object, m As Object If IsMissing(instance) Then instance = 0 ElseIf TypeName(instance) "Double" Then Substr = CVErr(xlErrValue) 'invalid instance type instance = -1 ElseIf CDbl(instance) = 0.5 Then Substr = CVErr(xlErrNum) 'invalid instance value instance = -1 Else instance = Int(instance + 0.5) End If If instance = -1 Then Exit Function 'do nothing quickly Set regex = CreateObject("vbscript.regexp") regex.Pattern = match_pat regex.Global = True If instance = 0 Then Substr = regex.Replace(orig_text, replace_pat) Else Set matches = regex.Execute(orig_text) If instance matches.Count Then Substr = orig_text 'matchnum out of bounds - do nothing Else Set m = matches.Item(instance - 1) Substr = Left(orig_text, m.FirstIndex) & _ regex.Replace(m.Value, replace_pat) & _ Right(orig_text, Len(orig_text) - m.FirstIndex - m.Length) End If End If End Function -- HTH RP (remove nothere from the email address if mailing direct) "Ron Rosenfeld" wrote in message ... On Thu, 1 Dec 2005 09:26:02 -0800, "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! If you just have single numbers, as in the above, then 1. Download and install Laurent Longre's free morefunc.xll add-in from http://xcell05.free.fr/ 2. Then use this formula: =REGEX.MID(A1,"\d+") --ron |
#15
|
|||
|
|||
Extract Numerics only
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 11-4005 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! |
#16
|
|||
|
|||
Extract Numerics only
Thanks for the suggestion Dave. I tried your VBA code and it gets hung up on
the "Next K" portion. ??? "Dave O" wrote: The only way I can think to do this is with VBA code. This code works on a column of numbers, and will place the numeric-only string in the column immediately to the right of the original column. Type the word "stop" (no quotes) in the cell under the last alpha-numeric cell you want to evaluate. Note- this code formats the cells that will hold the numeric-only values as text to preserve any leading zeroes. See the comments within the code. Sub RemoveText() Dim Werd As String, NewWerd As String Dim K As Byte Do Until ActiveCell.Value = "stop" 'start main loop Werd = ActiveCell.Value 'store part number to memory For K = 1 To Len(Werd) 'strip out non-numeric characters If Asc(Mid(Werd, K, 1)) = 48 And Asc(Mid(Werd, K, 1)) = 57 Then NewWerd = NewWerd & Mid(Werd, K, 1) Next K ActiveCell.Offset(0, 1).Select 'move to adjacent column Selection.NumberFormat = "@" 'format cell as text to preserve leading zero, if any ActiveCell.Value = NewWerd 'apply numeric value ActiveCell.Offset(0, -1).Select 'move back NewWerd = "" 'set value to nothing ActiveCell.Offset(1, 0).Select 'move down one cell Loop End Sub |
#17
|
|||
|
|||
Extract Numerics only
The reason the first returns 769142 is that the extracted value 11-4005
is seen as a date so Excel translates the date as 11/01/05 which is 769142 days after Jan 0 1900 Excel's first date, given that the numbers can be so different it is hard to guard against, try this adaptation of Domenic's excellent formula =LOOKUP(REPT("z",255),MID(A1,MIN(SEARCH({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789 ")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})) -- Regards, Peo Sjoblom "Corey" wrote in message ... 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 11-4005 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! |
#18
|
|||
|
|||
Extract Numerics only
You might want to convert that to a number
=--LOOKUP(REPT("z",255),MID(A1,MIN(SEARCH({0,1,2,3,4, 5,6,7,8,9},A1&"01234567 89 ")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})) -- HTH RP (remove nothere from the email address if mailing direct) "Peo Sjoblom" wrote in message ... The reason the first returns 769142 is that the extracted value 11-4005 is seen as a date so Excel translates the date as 11/01/05 which is 769142 days after Jan 0 1900 Excel's first date, given that the numbers can be so different it is hard to guard against, try this adaptation of Domenic's excellent formula =LOOKUP(REPT("z",255),MID(A1,MIN(SEARCH({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789 ")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})) -- Regards, Peo Sjoblom "Corey" wrote in message ... 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 11-4005 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! |
#19
|
|||
|
|||
Extract Numerics only
I believe the OP complained that he got 769142 instead of 11-4005 thus the
change, otherwise one might as well keep Domenic's original formula -- Regards, Peo Sjoblom "Bob Phillips" wrote in message ... You might want to convert that to a number =--LOOKUP(REPT("z",255),MID(A1,MIN(SEARCH({0,1,2,3,4, 5,6,7,8,9},A1&"01234567 89 ")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})) -- HTH RP (remove nothere from the email address if mailing direct) "Peo Sjoblom" wrote in message ... The reason the first returns 769142 is that the extracted value 11-4005 is seen as a date so Excel translates the date as 11/01/05 which is 769142 days after Jan 0 1900 Excel's first date, given that the numbers can be so different it is hard to guard against, try this adaptation of Domenic's excellent formula =LOOKUP(REPT("z",255),MID(A1,MIN(SEARCH({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789 ")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})) -- Regards, Peo Sjoblom "Corey" wrote in message ... 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 11-4005 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! |
#20
|
|||
|
|||
Extract Numerics only
This works perfect Peo! Thanks a bunch! Also to you Domenic for coming up
with that original formula. I tried Bob's suggestion to this as well, but got #VALUE!. Since your formula returns the everything to the right, starting with the first numeric, I just added VALUE(LEFT(A1,2)) to pull out the first two numbers. This is the division number I've been trying to get at. Perhaps if I said that earlier, there's another way to get it done. ?? Anyways, I've also added two if statements to the beginning of that and it works great. When I try to add a third I get an error that leads me to the "SEARCH" portion of the formula. Is there a limit to how many conditionals I can put in this thing? Thanks again! "Peo Sjoblom" wrote: The reason the first returns 769142 is that the extracted value 11-4005 is seen as a date so Excel translates the date as 11/01/05 which is 769142 days after Jan 0 1900 Excel's first date, given that the numbers can be so different it is hard to guard against, try this adaptation of Domenic's excellent formula =LOOKUP(REPT("z",255),MID(A1,MIN(SEARCH({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789 ")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})) -- Regards, Peo Sjoblom "Corey" wrote in message ... 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 11-4005 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! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
a tip about how to extract .SWFfile from PowerPoint file | SusanZheng | Powerpoint | 1 | November 3rd, 2005 03:58 AM |
Extract records with a specific field appearing more than once in the DB | markx | Running & Setting Up Queries | 7 | September 28th, 2005 06:44 PM |
Trendline Extract | Phil Hageman | Charts and Charting | 5 | July 6th, 2005 02:27 AM |
Extract Unique Values, Then Extract Again to Remove Suffixes | Karl Burrows | General Discussion | 23 | June 25th, 2005 10:37 PM |
Extract specific data into its own workbook via macro? | Adrian B | General Discussion | 2 | February 24th, 2005 06:09 AM |