UDF or similar to extract currency amounts or numbers and symbols from text string

[FreeRangeJ]

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

<tbody>

Order Detail	UDF:Extract Numbers (as Number 0 decimals)	UDF:Extract Numbers (as Number 2 decimals)	UDF: Extract Symbols
Random Product 3 Qty: 4 Monthly: £7.00 Total Monthly: £28.00 Monthly Rollinng Tarrif national- 0.9bst allowance 3- 3.4mms	3470028000933	3470028000933.40	: : £. : £. - . - .
23 johbfcvasebn 45 gtr ty65h mt 657	234565657	234565657.00
23 johbfcvasebn 45 gtr. ty65h mt 657	2346	2345.66	.

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

</tbody>

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!

 

[István Hirsch]

Please show us in a table some of your cells to process with the results expected (produced manually).

 

[FreeRangeJ]

This is a rough example:

<tbody>

ORDER DETAIL	UDF Version (incl decimal error)	Anticipated Version	Ideal / Manual Version
Service Details Provide connection through Alpha site infrastructure, presented as an upgrade to Alpha's purchase from source. Product 1 Monthly Price £175 Installation / CPE £0 / Term 36 Months	0.1175036	1 £175 / £0 / 36	£175 £0
Managed service: Install £350 Monthly - £35.00 Terms - as per orginal contact EXP - 15/04/2015	35035.0015	£350 - £35.00 - - 15/04/2015	£350 £35.00
Upgrade from 10 to 30 with 100 scalability - Install: £1000 Monthl cost: £525 (as existing) Contract term: new 36 months	1030100100052540	10 30 100 - : £1000 : £525 ( ) : 36	£1000 £525
Increase from 30 to 60 for a temporary event. £200 one off cost. This has been raised in a ticket	3060200	30 60 . £200 .	£200
Upgrade to 60 (for a temporary event) Install: £200.00 Monthly: £50 Contract Term: 17/03/2015 to 16/04/2015	6020000501703200000000000	60 ( ) : £200.00 : £50 : 17/03/2015 16/04/2015	£200.00 £50
Temporary upgrade to 60M from 30 Install: £200.00 Monthly: £0.00 Contract Term: 30/11/2015	60302000000030100000	60 30 : £200.00 : £0.00 : 30/11/2015	£200.00 £0.00
Product 20 Install: £3,000.00 Monthly: £400.00 Managed Install: Included Monthly: Included Total Install: £3,000.00 Total Monthly: £400.00 Contract Term: 12 Months	20300000400003000000000000	20 : £3,000.00 : £400.00 : : : £3,000.00 : £400.00 : 12	£3,000.00 £400.00 £3000.00 £400.00
Services Size:10 s Install:£500 Monthly:£1000 Contract:12 Months Installed 09/09/08 Updated: 01/07/10 Size: 20	1050010001209090000000000	:10 :£500 :£1000 :12 09/09/08 : 01/07/10 : 20	£500 £1000
Retention of existing Product 20 for new monthly price of £700. This will replace the existing monthly charges of £1000 (ex. VAT). In addition, Monthly: £45.00 Backup Installaton £0.00 - Monthly: £0.00 Total Installation: £0.00 Total Monthly: £745.00 Contract: 36 Months	20700100045000000000000000000	20 £700. £1000 ( . ) . , : £45.00 £0.00 - : £0.00 : £0.00 : £745.00 : 36	£700 £1000 £45.00 £0.00 £0.00 £0.00 £745.00
Upgrade to 40 - New Monthly: £650.00 Term 36 months	406500036	40 - : £650.00 36	£650.00

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

</tbody>

 

[FreeRangeJ]

well, it would have been, the table insert in post not coming through and can only insert image from url not from machine, inspired!

 

[FreeRangeJ]

does this layout make sense:

ORDER DETAIL	UDF Version (incl decimal error)	Anticipated Version	Ideal / Manual Version
Service Details Provide connection through Alpha site infrastructure, presented as an upgrade to Alpha's purchase from source. Product 1 Monthly Price £175 Installation / CPE £0 / Term 36 Months	0.1175036	1 £175 / £0 / 36	£175 £0
Managed service: Install £350 Monthly - £35.00 Terms - as per orginal contact EXP - 15/04/2015	35035.0015	£350 - £35.00 - - 15/04/2015	£350 £35.00
Upgrade from 10 to 30 with 100 scalability - Install: £1000 Monthl cost: £525 (as existing) Contract term: new 36 months	1030100100052540	10 30 100 - : £1000 : £525 ( ) : 36	£1000 £525
Increase from 30 to 60 for a temporary event. £200 one off cost. This has been raised in a ticket	3060200	30 60 . £200 .	£200
Upgrade to 60 (for a temporary event) Install: £200.00 Monthly: £50 Contract Term: 17/03/2015 to 16/04/2015	6020000501703200000000000	60 ( ) : £200.00 : £50 : 17/03/2015 16/04/2015	£200.00 £50
Temporary upgrade to 60M from 30 Install: £200.00 Monthly: £0.00 Contract Term: 30/11/2015	60302000000030100000	60 30 : £200.00 : £0.00 : 30/11/2015	£200.00 £0.00
Product 20 Install: £3,000.00 Monthly: £400.00 Managed Install: Included Monthly: Included Total Install: £3,000.00 Total Monthly: £400.00 Contract Term: 12 Months	20300000400003000000000000	20 : £3,000.00 : £400.00 : : : £3,000.00 : £400.00 : 12	£3,000.00 £400.00 £3000.00 £400.00
Services Size:10 s Install:£500 Monthly:£1000 Contract:12 Months Installed 09/09/08 Updated: 01/07/10 Size: 20	1050010001209090000000000	:10 :£500 :£1000 :12 09/09/08 : 01/07/10 : 20	£500 £1000
Retention of existing Product 20 for new monthly price of £700. This will replace the existing monthly charges of £1000 (ex. VAT). In addition, Monthly: £45.00 Backup Installaton £0.00 - Monthly: £0.00 Total Installation: £0.00 Total Monthly: £745.00 Contract: 36 Months	20700100045000000000000000000	20 £700. £1000 ( . ) . , : £45.00 £0.00 - : £0.00 : £0.00 : £745.00 : 36	£700 £1000 £45.00 £0.00 £0.00 £0.00 £745.00
Upgrade to 40 - New Monthly: £650.00 Term 36 months	406500036	40 - : £650.00 36	£650.00

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[István Hirsch]

If you have place for some helper columns you can use the formule below. Enter the formula in C1 then copy across and down, then enter the formula in B1 and copy down. You may have to concatenate more cells in B1, if there are more items to extract than 7.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Service Details Provide connection through Alpha site infrastructure, presented as an upgrade to Alpha's purchase from source. Product 1 Monthly Price ?175 Installation / CPE ?0 / Term 36 Months	?175 ?0	?175	?0
2	Managed service: Install ?350 Monthly - ?35.00 Terms - as per orginal contact EXP - 15/04/2015	?350 ?35.00	?350	?35.00
3	Upgrade from 10 to 30 with 100 scalability - Install: ?1000 Monthl cost: ?525 (as existing) Contract term: new 36 months	?1000 ?525	?1000	?525
4	Increase from 30 to 60 for a temporary event. ?200 one off cost. This has been raised in a ticket	?200	?200
5	Upgrade to 60 (for a temporary event) Install: ?200.00 Monthly: ?50 Contract Term: 17/03/2015 to 16/04/2015	?200.00 ?50	?200.00	?50
6	Temporary upgrade to 60M from 30 Install: ?200.00 Monthly: ?0.00 Contract Term: 30/11/2015	?200.00 ?0.00	?200.00	?0.00
7	Product 20 Install: ?3,000.00 Monthly: ?400.00 Managed Install: Included Monthly: Included Total Install: ?3,000.00 Total Monthly: ?400.00 Contract Term: 12 Months	?3,000.00 ?400.00 ?3,000.00 ?400.00	?3,000.00	?400.00	?3,000.00	?400.00
8	Services Size:10 s Install:?500 Monthly:?1000 Contract:12 Months Installed 09/09/08 Updated: 01/07/10 Size: 20	?500 ?1000	?500	?1000
9	Retention of existing Product 20 for new monthly price of ?700. This will replace the existing monthly charges of ?1000 (ex. VAT). In addition, Monthly: ?45.00 Backup Installaton ?0.00 - Monthly: ?0.00 Total Installation: ?0.00 Total Monthly: ?745.00 Contract: 36 Months	?700. ?1000 ?45.00 ?0.00 ?0.00 ?0.00 ?745.00	?700.	?1000	?45.00	?0.00	?0.00	?0.00	?745.00
10	Upgrade to 40 - New Monthly: ?650.00 Term 36 months		?650.00
Sheet

 

[FreeRangeJ]

Thanks Istvan

This looks great, have tested it on sample data with throw it into the full sheet shortly

I get the concatenation of B1 and B2, if you have time could you just quickly step through the Fx in C1

Thanks again

 

[István Hirsch]

Do not you get the result as shown in columns C-I of my table?

 

[FreeRangeJ]

Hi Istvan

Sorry for the tardiness, been sidelined. The it doesn't quite run but doesn't seem to error, but will pick this up tomorrow, new thread time, something that's more pressing is not quite working as i thought it might in my head, look our for IF AND Statement based on ranges

 

[Rick Rothstein]

I think this UDF will do what you want...

Function GetCur(S As String) As String
  Dim X As Long, Cur() As String
  Cur = Split(S, "£")
  For X = 1 To UBound(Cur)
    GetCur = GetCur & " £" & Format(Val(Cur(X)), "0.00")
  Next
  GetCur = Trim(GetCur)
End Function