Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Number from text
I have cells with data such as
0.5 ml 1ml 560 gm 373 milliliters and need a function to extract the number part of these. I found a formula that sort of works, ----------------- Function ExtractNumber(rCell As Range) Dim iCount As Integer, i As Integer Dim sText As String Dim lNum As String '''''''''''''''''''''''''''''''''''''''''' 'Written by OzGrid Business Applications 'www.ozgrid.com 'Extracts a number from a cell containing text and numbers. '''''''''''''''''''''''''''''''''''''''''' sText = rCell For iCount = Len(sText) To 1 Step -1 If IsNumeric(Mid(sText, iCount, 1)) Then i = i + 1 lNum = Mid(sText, iCount, 1) & lNum End If If i = 1 Then lNum = CInt(Mid(lNum, 1, 1)) Next iCount ExtractNumber = CLng(lNum) End Function ----------------------- However this formula seems to ignore decimal points and for example the 0.5 is returned as 5. All I want is a function to return just number part and not the units. Any ideas? -Andrew V. Romero |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Number from text
Function ExtractNumber(rCell As Range)
Dim iCount As Integer, i As Integer Dim sText As String Dim lNum As String '''''''''''''''''''''''''''''''''''''''''' 'Extracts a number from a cell containing text and numbers. '''''''''''''''''''''''''''''''''''''''''' sText = rCell For iCount = Len(sText) To 1 Step -1 If IsNumeric(Mid(sText, iCount, 1)) or _ Mid(sText, iCount,1) = "." Then i = i + 1 lNum = Mid(sText, iCount, 1) & lNum End If Next iCount ExtractNumber = CDbl(lNum) End Function -- Regards, Tom Ogilvy wrote in message ups.com... I have cells with data such as 0.5 ml 1ml 560 gm 373 milliliters and need a function to extract the number part of these. I found a formula that sort of works, ----------------- Function ExtractNumber(rCell As Range) Dim iCount As Integer, i As Integer Dim sText As String Dim lNum As String '''''''''''''''''''''''''''''''''''''''''' 'Written by OzGrid Business Applications 'www.ozgrid.com 'Extracts a number from a cell containing text and numbers. '''''''''''''''''''''''''''''''''''''''''' sText = rCell For iCount = Len(sText) To 1 Step -1 If IsNumeric(Mid(sText, iCount, 1)) Then i = i + 1 lNum = Mid(sText, iCount, 1) & lNum End If If i = 1 Then lNum = CInt(Mid(lNum, 1, 1)) Next iCount ExtractNumber = CLng(lNum) End Function ----------------------- However this formula seems to ignore decimal points and for example the 0.5 is returned as 5. All I want is a function to return just number part and not the units. Any ideas? -Andrew V. Romero |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Number from text
Try this:
First set a reference to Microsoft VBScript Regular Expressions 5.5 this will strip out the first numeric value including decimal, even if it is in the middle of the string so GetNumber("-56,424.45 sldkfns") GetNumber("-56,424.45sldkfns") GetNumber("sagsadgag -56,424.45sldkfns") will all return the same value -56424.45 HS ----------------------------------------- Function GetNumber(stringVal As String) As Double Dim regEx, Match, Matches ' Create variable. Set regEx = New REGEXP ' Create a regular expression. regEx.IgnoreCase = True ' Set case insensitivity. regEx.Global = True ' Set global applicability. Const patrn1 = "[0-9\.\,\-]+" 'look for any digit 0 to 9 or decimal point or comma or the minus sign regEx.Pattern = patrn1 ' Set pattern. Set Matches = regEx.Execute(stringVal) ' Execute search. If Matches.Count 0 Then GetNumber = CDbl(Matches(0).Value) Else GetNumber = 0 End If Set Matches = Nothing Set regEx = Nothing End Function wrote in message ups.com... :I have cells with data such as : 0.5 ml : 1ml : 560 gm : 373 milliliters : : and need a function to extract the number part of these. I found a : formula that sort of works, : ----------------- : Function ExtractNumber(rCell As Range) : Dim iCount As Integer, i As Integer : Dim sText As String : Dim lNum As String : : '''''''''''''''''''''''''''''''''''''''''' : 'Written by OzGrid Business Applications : 'www.ozgrid.com : : 'Extracts a number from a cell containing text and numbers. : '''''''''''''''''''''''''''''''''''''''''' : sText = rCell : : For iCount = Len(sText) To 1 Step -1 : If IsNumeric(Mid(sText, iCount, 1)) Then : i = i + 1 : lNum = Mid(sText, iCount, 1) & lNum : End If : : If i = 1 Then lNum = CInt(Mid(lNum, 1, 1)) : Next iCount : : : ExtractNumber = CLng(lNum) : End Function : ----------------------- : However this formula seems to ignore decimal points and for example the : 0.5 is returned as 5. All I want is a function to return just number : part and not the units. Any ideas? : : -Andrew V. Romero : |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Number from text
Just modify the IsNumeric line:
If IsNumeric(Mid(sText, iCount, 1)) Or (Mid(sText, iCount, 1)=".") Then... -- - K Dales " wrote: I have cells with data such as 0.5 ml 1ml 560 gm 373 milliliters and need a function to extract the number part of these. I found a formula that sort of works, ----------------- Function ExtractNumber(rCell As Range) Dim iCount As Integer, i As Integer Dim sText As String Dim lNum As String '''''''''''''''''''''''''''''''''''''''''' 'Written by OzGrid Business Applications 'www.ozgrid.com 'Extracts a number from a cell containing text and numbers. '''''''''''''''''''''''''''''''''''''''''' sText = rCell For iCount = Len(sText) To 1 Step -1 If IsNumeric(Mid(sText, iCount, 1)) Then i = i + 1 lNum = Mid(sText, iCount, 1) & lNum End If If i = 1 Then lNum = CInt(Mid(lNum, 1, 1)) Next iCount ExtractNumber = CLng(lNum) End Function ----------------------- However this formula seems to ignore decimal points and for example the 0.5 is returned as 5. All I want is a function to return just number part and not the units. Any ideas? -Andrew V. Romero |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Number from text
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to extract text from number/text cell | Excel Worksheet Functions | |||
Extract a number(s) from a text string | Excel Discussion (Misc queries) | |||
Extract a number from a variable text string | Excel Discussion (Misc queries) | |||
extract number and use in formula from text & numbers in cell | Excel Worksheet Functions | |||
Extract number from text/number string.. | Excel Discussion (Misc queries) |