FreeRangeJ
New Member
- Joined
- Feb 7, 2013
- Messages
- 40
i have a description column that contains the financial amounts that should be in other columns. I want to extract these amounts into helper columns, not the intended financial columns for validation purposes.
I've found a udf that almost works to pull all numbers from a text string but puts them in a number string with no grouping or symbols, another issue is that even though there are several decimal amounts in the string it pulls the last through or the only one through and decimals the whole resulting number string from that point
I've also found a udf that extracts the symbols into a symbol string
[TABLE="class: grid, width: 1600, align: center"]
<tbody>[TABLE="width: 1430"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Order Detail[/TD]
[TD]UDF:Extract Numbers (as Number 0 decimals)[/TD]
[TD]UDF:Extract Numbers (as Number 2 decimals)[/TD]
[TD]UDF: Extract Symbols[/TD]
[/TR]
[TR]
[TD]Random Product 3 Qty: 4 Monthly: £7.00 Total Monthly: £28.00 Monthly Rollinng Tarrif national- 0.9bst allowance 3- 3.4mms [/TD]
[TD]3470028000933[/TD]
[TD]3470028000933.40[/TD]
[TD] : : £. : £. - . - . [/TD]
[/TR]
[TR]
[TD]23 johbfcvasebn 45 gtr ty65h mt 657[/TD]
[TD]234565657[/TD]
[TD]234565657.00[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]23 johbfcvasebn 45 gtr. ty65h mt 657[/TD]
[TD]2346[/TD]
[TD]2345.66[/TD]
[TD] .
[/TD]
[/TR]
</tbody>[/TABLE]
</tbody>[/TABLE]
so if you can almost do numbers and you can do symbols separately is there a way to do numbers fully combined with symbols and moreover is there a udf or similar to extract them (numbers and symbols) in the groups they appear in the string (using spaces as a delimiter?) so as in the first example:
3 : 4 : £7.00 : £28.00 - 0.9 3- 3.4
or in a string:
3:4:£7.00:£28.00-0.93-3.4
I don't fully understand the udfs i found, i can just see that the numbers one is kinda almost there, it reads like this:
Function ExtractNumber(rCell As Range, _
Optional Take_decimal As Boolean, Optional Take_negative As Boolean) As Double
Dim iCount As Integer, i As Integer, iLoop As Integer
Dim sText As String, strNeg As String, strDec As String
Dim lNum As String
Dim vVal, vVal2
sText = rCell
If Take_decimal = True And Take_negative = True Then
strNeg = "-" 'Negative Sign MUST be before 1st number.
strDec = "."
ElseIf Take_decimal = True And Take_negative = False Then
strNeg = vbNullString
strDec = "."
ElseIf Take_decimal = False And Take_negative = True Then
strNeg = "-"
strDec = vbNullString
End If
iLoop = Len(sText)
For iCount = iLoop To 1 Step -1
vVal = Mid(sText, iCount, 1)
If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then
i = i + 1
lNum = Mid(sText, iCount, 1) & lNum
If IsNumeric(lNum) Then
If CDbl(lNum) < 0 Then Exit For
Else
lNum = Replace(lNum, Left(lNum, 1), "", , 1)
End If
End If
If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum, 1, 1))
Next iCount
ExtractNumber = CDbl(lNum)
End Function
thanks in advance!
I've found a udf that almost works to pull all numbers from a text string but puts them in a number string with no grouping or symbols, another issue is that even though there are several decimal amounts in the string it pulls the last through or the only one through and decimals the whole resulting number string from that point
I've also found a udf that extracts the symbols into a symbol string
[TABLE="class: grid, width: 1600, align: center"]
<tbody>[TABLE="width: 1430"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Order Detail[/TD]
[TD]UDF:Extract Numbers (as Number 0 decimals)[/TD]
[TD]UDF:Extract Numbers (as Number 2 decimals)[/TD]
[TD]UDF: Extract Symbols[/TD]
[/TR]
[TR]
[TD]Random Product 3 Qty: 4 Monthly: £7.00 Total Monthly: £28.00 Monthly Rollinng Tarrif national- 0.9bst allowance 3- 3.4mms [/TD]
[TD]3470028000933[/TD]
[TD]3470028000933.40[/TD]
[TD] : : £. : £. - . - . [/TD]
[/TR]
[TR]
[TD]23 johbfcvasebn 45 gtr ty65h mt 657[/TD]
[TD]234565657[/TD]
[TD]234565657.00[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]23 johbfcvasebn 45 gtr. ty65h mt 657[/TD]
[TD]2346[/TD]
[TD]2345.66[/TD]
[TD] .
[/TD]
[/TR]
</tbody>[/TABLE]
</tbody>[/TABLE]
so if you can almost do numbers and you can do symbols separately is there a way to do numbers fully combined with symbols and moreover is there a udf or similar to extract them (numbers and symbols) in the groups they appear in the string (using spaces as a delimiter?) so as in the first example:
3 : 4 : £7.00 : £28.00 - 0.9 3- 3.4
or in a string:
3:4:£7.00:£28.00-0.93-3.4
I don't fully understand the udfs i found, i can just see that the numbers one is kinda almost there, it reads like this:
Function ExtractNumber(rCell As Range, _
Optional Take_decimal As Boolean, Optional Take_negative As Boolean) As Double
Dim iCount As Integer, i As Integer, iLoop As Integer
Dim sText As String, strNeg As String, strDec As String
Dim lNum As String
Dim vVal, vVal2
sText = rCell
If Take_decimal = True And Take_negative = True Then
strNeg = "-" 'Negative Sign MUST be before 1st number.
strDec = "."
ElseIf Take_decimal = True And Take_negative = False Then
strNeg = vbNullString
strDec = "."
ElseIf Take_decimal = False And Take_negative = True Then
strNeg = "-"
strDec = vbNullString
End If
iLoop = Len(sText)
For iCount = iLoop To 1 Step -1
vVal = Mid(sText, iCount, 1)
If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then
i = i + 1
lNum = Mid(sText, iCount, 1) & lNum
If IsNumeric(lNum) Then
If CDbl(lNum) < 0 Then Exit For
Else
lNum = Replace(lNum, Left(lNum, 1), "", , 1)
End If
End If
If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum, 1, 1))
Next iCount
ExtractNumber = CDbl(lNum)
End Function
thanks in advance!
