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 |
#1
|
|||
|
|||
Extract Numerics only
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! |
#2
|
|||
|
|||
Extract Numerics only
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 |
#3
|
|||
|
|||
Extract Numerics only
Corey
Suggest a UDF if you want a formula to extract numbers to another cell and leave original data in place. Function DeleteNonNumerics(ByVal sStr As String) As Long Dim i As Long If sStr Like "*[0-9]*" Then For i = 1 To Len(sStr) If Mid(sStr, i, 1) Like "[0-9]" Then DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1) End If Next i Else DeleteNonNumerics = sStr End If End Function If you want to strip in place use a macro after selecting the column. Sub RemoveAlphas() '' Remove alpha characters from a string. Dim intI As Integer Dim rngR As Range, rngRR As Range Dim strNotNum As String, strTemp As String Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _ xlTextValues) For Each rngR In rngRR strTemp = "" For intI = 1 To Len(rngR.Value) If Mid(rngR.Value, intI, 1) Like "[0-9]" Then strNotNum = Mid(rngR.Value, intI, 1) Else: strNotNum = "" End If strTemp = strTemp & strNotNum Next intI rngR.Value = strTemp Next rngR End Sub Gord Dibben Excel MVP 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! |
#4
|
|||
|
|||
Extract Numerics only
Gord,
Best way to avoid an error in the function if the cell does not have any numerics? Thanks for your knowledge and time! Dennis "Gord Dibben" wrote: Corey Suggest a UDF if you want a formula to extract numbers to another cell and leave original data in place. Function DeleteNonNumerics(ByVal sStr As String) As Long Dim i As Long If sStr Like "*[0-9]*" Then For i = 1 To Len(sStr) If Mid(sStr, i, 1) Like "[0-9]" Then DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1) End If Next i Else DeleteNonNumerics = sStr End If End Function If you want to strip in place use a macro after selecting the column. Sub RemoveAlphas() '' Remove alpha characters from a string. Dim intI As Integer Dim rngR As Range, rngRR As Range Dim strNotNum As String, strTemp As String Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _ xlTextValues) For Each rngR In rngRR strTemp = "" For intI = 1 To Len(rngR.Value) If Mid(rngR.Value, intI, 1) Like "[0-9]" Then strNotNum = Mid(rngR.Value, intI, 1) Else: strNotNum = "" End If strTemp = strTemp & strNotNum Next intI rngR.Value = strTemp Next rngR End Sub Gord Dibben Excel MVP 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! |
#5
|
|||
|
|||
Extract Numerics only
NOTE: If you use any UDF's, one must place the UDF in "ThisWorkbook" as it
will not work from i.e. Personal.xls "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! |
#6
|
|||
|
|||
Extract Numerics only
Until someone comes up with a better answer.....
=IF(ISERROR(deletenonnumerics(J6)),"",deletenonnum erics(J6)) Gord On Thu, 1 Dec 2005 11:51:02 -0800, Dennis wrote: Gord, Best way to avoid an error in the function if the cell does not have any numerics? Thanks for your knowledge and time! Dennis "Gord Dibben" wrote: Corey Suggest a UDF if you want a formula to extract numbers to another cell and leave original data in place. Function DeleteNonNumerics(ByVal sStr As String) As Long Dim i As Long If sStr Like "*[0-9]*" Then For i = 1 To Len(sStr) If Mid(sStr, i, 1) Like "[0-9]" Then DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1) End If Next i Else DeleteNonNumerics = sStr End If End Function If you want to strip in place use a macro after selecting the column. Sub RemoveAlphas() '' Remove alpha characters from a string. Dim intI As Integer Dim rngR As Range, rngRR As Range Dim strNotNum As String, strTemp As String Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _ xlTextValues) For Each rngR In rngRR strTemp = "" For intI = 1 To Len(rngR.Value) If Mid(rngR.Value, intI, 1) Like "[0-9]" Then strNotNum = Mid(rngR.Value, intI, 1) Else: strNotNum = "" End If strTemp = strTemp & strNotNum Next intI rngR.Value = strTemp Next rngR End Sub Gord Dibben Excel MVP 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! |
#7
|
|||
|
|||
Extract Numerics only
Dennis
You can a UDF from Personal.xls if you qualify it in the formula. =Personal.xls!udfname(arguments) Gord Dibben Excel MVP On Thu, 1 Dec 2005 11:56:02 -0800, Dennis wrote: NOTE: If you use any UDF's, one must place the UDF in "ThisWorkbook" as it will not work from i.e. Personal.xls "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! |
#8
|
|||
|
|||
Extract Numerics only
Corey,
A formula approach. First, this is based upon the data being in A1:An, so adjust all formulae to suit. Insert an Excel name (InsertNameDefine...), with a name of pos_array, and a RefersTo value of =ROW(INDIRECT("A1:A"&LEN('3'!A1))) Then in B1, add this formula =IF(ISNUMBER(--LEFT(A1,1)),LEFT(A1,MIN(IF(ISERROR(1*(MID(A1,pos_a rray,1))),p os_array,255))-1),MID(A1,MIN(IF(ISERROR(1*(MID(A1,pos_array,1))), 255,pos_arr ay)),99)) as an array formula, which means that it is committed with Ctrl-Shift-Enter, and copy down. You will see that for ON 3127, you already get the end-result, but not for O/F 20R. So we need to do one more iteration of this. So, copy B1 over to C1, and then down, the second iteration should get you your end-results. If you want the end-result as a number, rather than the text that these formulae produce, use a slight variation in C1 =--(IF(ISNUMBER(--LEFT(A1,1)),LEFT(A1,MIN(IF(ISERROR(1*(MID(A1,pos_a rray,1)) ),pos_array,255))-1),MID(A1,MIN(IF(ISERROR(1*(MID(A1,pos_array,1))), 255,pos_ array)),99))) again as an array formula, and again copy down. -- HTH RP (remove nothere from the email address if mailing direct) "Corey" wrote in message ... 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! |
#9
|
|||
|
|||
Extract Numerics only
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! |
#10
|
|||
|
|||
Extract Numerics only
Very good. A bit better than mine :-))
-- HTH RP (remove nothere from the email address if mailing direct) "Domenic" wrote in message ... 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 04: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 07:09 AM |