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

[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!

 

[FreeRangeJ]

Ah, never mind, I see what the problem is with your originally attempted revision. You posted this...

Function Order(S As String, DigitCount) As String
  Dim X As Long, Parts() As String
  Parts = Split(S, "Order: ", , vbTextCompare)
  For X = 1 To UBound(Parts)
    If Parts(X) & " " Like String(DigitCount, "#") & "[!0-9]*" Then
      [B][COLOR=#FF0000]SF[/COLOR][/B] = "Order: " & Left(Parts(X), DigitCount)
      Exit For
    End If
  Next
End Function

The red highlighted text above should be the same as the function name (that is how the calculated value is returned from the function... you assign the value directly to the function name)...

Function Order(S As String, DigitCount) As String
  Dim X As Long, Parts() As String
  Parts = Split(S, "Order: ", , vbTextCompare)
  For X = 1 To UBound(Parts)
    If Parts(X) & " " Like String(DigitCount, "#") & "[!0-9]*" Then
      [B][COLOR=#FF0000]Order[/COLOR][/B] = "Order: " & Left(Parts(X), DigitCount)
      Exit For
    End If
  Next
End Function

Ah that makes sense, i didn't spot that SF, thanks again!

 

[cztomjan]

Dear Rick,

your GetCur UDF works without any problems (was written for it) if currency code is placed in front of the number/amount (£7.00). Could you please modify it to work for the case when currency code is placed after the number? This text (as example) has been placed into the cell: 5 TCZK Leiharbeiter, 1 TCZK Frachtkosten, 25 TCZK Stellenanzeigen, -2 TCZK Ohruntersuchung. Thank you.

 

[Rick Rothstein]

Dear Rick,

your GetCur UDF works without any problems (was written for it) if currency code is placed in front of the number/amount (£7.00). Could you please modify it to work for the case when currency code is placed after the number? This text (as example) has been placed into the cell: 5 TCZK Leiharbeiter, 1 TCZK Frachtkosten, 25 TCZK Stellenanzeigen, -2 TCZK Ohruntersuchung. Thank you.

First, before I try to modify my UDF, I note you are using xl365... do you really need a UDF or would a formula be okay for your purposes? If so, try this one...

=LET(symb,"TCZK",TEXTJOIN(" "&symb&" ",,TOROW(0+TEXTSPLIT(A1,HSTACK(symb," ")),3)))

You can wrap this in a LAMBDA and assign it to a defined name, such as GetCur, and then use it the same as you would a UDF. The LAMBDA formula you would use for this defined name would be this...

=LAMBDA(txt,LET(symb,"TCZK",TEXTJOIN(" "&symb&" ",,TOROW(0+TEXTSPLIT(A1,HSTACK(symb," ")),3))))

and if you gave it the name GetCur, then you would call it this way...
=GetCur(A1)

 

[cztomjan]

Hello Rick, your formula works almost great, thank you! There are only minor issues, see enclosed picture: currency code will not be placed after last figure (row 1); in case that there are some information in brackets (for example about quantity), these are consired like amount and will be placed to formula results (row 2):

 

[Rick Rothstein]

Not sure which formula you are referring to, the first direct in-cell formula or the LAMBDA to be used with a defined name. For this reply, I'll assume the first. Your first issue was an oversight by me and easily fixed...

=LET(symb,"TCZK",TEXTJOIN(" "&symb&" ",,TOROW(0+TEXTSPLIT(A1,HSTACK(symb," ")),3))&" "&symb)

As for your second issue, are you saying you want the currency placed after any number inside of parentheses no matter what text follows it?

 

[cztomjan]

Hi, Rick, thank for your support again. Yes, I have used the first option (direct in-cell formula). The first issue is solved. Regarding the second issue: the formula should list numbers with currency code (TCZK) only, not another numbers (with another/without codes put after them). See examples below (some of them are correct, some not); anyway, you already helped me a lot; numbers which should be not in the formula result I can remove manually...

Test.xlsx
	M
14	5 TCZK Leiharbeiter, 1 TCZK Frachtkosten, 1 TCZK Auszug aus Patientenakten (2 MA), 47 TCZK Reinigung/Pflege Lösungsmittel Pyrrolidon, 14 Stck.
15	5 TCZK 1 TCZK 1 TCZK 47 TCZK 14 TCZK
16	141 TCZK Schlosserei, Plan-Stunden 414, Ist-Stunden 261
17	141 TCZK 414 TCZK 261 TCZK
18	110 TCZK QS Labor (187 Std.)
19	110 TCZK
20	110 TCZK QS Labor, 187 Stck.
21	110 TCZK 187 TCZK
Tabelle1

Cell Formulas
Range	Formula
M15,M21,M19,M17	M15	=LET(symb,"TCZK",TEXTJOIN(" "&symb&" ",,TOROW(0+TEXTSPLIT(M14,HSTACK(symb," ")),3))&" "&symb)