Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
functions:extract_number_from_text [2016/02/16 16:22]
metecuma
functions:extract_number_from_text [2016/02/16 17:00]
metecuma
Line 9: Line 9:
 // Adapted from [[http://​www.ozgrid.com/​VBA/​ExtractNum.htm|http://​www.ozgrid.com/​VBA/​ExtractNum.htm]] \\ // // Adapted from [[http://​www.ozgrid.com/​VBA/​ExtractNum.htm|http://​www.ozgrid.com/​VBA/​ExtractNum.htm]] \\ //
 By using this function, you can extract numeric part of alphanumeric text. According to your needs, you may extract numbers with decimal sign and/or negative sign. \\ By using this function, you can extract numeric part of alphanumeric text. According to your needs, you may extract numbers with decimal sign and/or negative sign. \\
 +  =Extract_Number_From_Text(Value,​ Include_Decimal,​ Include_Negative)
 At the figure below, you can see using of parameters; \\ At the figure below, you can see using of parameters; \\
 {{functions:​extract_number_from_text.png?​nolink|samples of extracting number from text}} \\ {{functions:​extract_number_from_text.png?​nolink|samples of extracting number from text}} \\
Line 15: Line 16:
 Function Extract_Number_From_Text(Value As Range, Optional Include_Decimal As Boolean, _ Function Extract_Number_From_Text(Value As Range, Optional Include_Decimal As Boolean, _
                                   Optional Include_Negative As Boolean) As Double                                   Optional Include_Negative As Boolean) As Double
-                                  ​ 
 Dim iCount As Integer, i As Integer, iLoop As Integer Dim iCount As Integer, i As Integer, iLoop As Integer
 Dim sText As String, strNeg As String, strDec As String, lNum As String Dim sText As String, strNeg As String, strDec As String, lNum As String
 Dim vVal, vVal2 Dim vVal, vVal2
- 
 '​Extracts numeric values from a cell containing text and numbers. '​Extracts numeric values from a cell containing text and numbers.
 sText = Value sText = Value
- 
 If Include_Decimal = True And Include_Negative = True Then If Include_Decimal = True And Include_Negative = True Then
     strNeg = "​-"​     strNeg = "​-"​
Line 34: Line 32:
 End If End If
 iLoop = Len(sText) iLoop = Len(sText)
- 
 For iCount = iLoop To 1 Step -1 For iCount = iLoop To 1 Step -1
 vVal = Mid(sText, iCount, 1) vVal = Mid(sText, iCount, 1)
- 
 If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then
     i = i + 1     i = i + 1
Line 47: Line 43:
 End If End If
 End If End If
- 
 If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum,​ 1, 1)) If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum,​ 1, 1))
 Next iCount Next iCount
- 
 Extract_Number_From_Text = CDbl(lNum) Extract_Number_From_Text = CDbl(lNum)
- 
 End Function End Function
 </​file>​ </​file>​