How to use FormatCurrency with a text field filled with decimal numbers

Question

I have a data extension with decimal numbers (seperated by comma) and formatted as text (changeable).

Now I want to use that data and put it with FormatCurrency() into the right shape for the countries.

e.g. 50,00 --> culturecode de_AT € 50,00

I tried different things, but failed.

I tried to convert a decimal number (seperated by comma) into the correct curreny:

%%[

var @priceStr, @priceFormatted
set @priceStr = "20,50" /* for testing */
set @priceFormatted = formatCurrency(@priceStr,"de-AT") 

]%%

the result is € 2.050,00. I was hoping for € 20,50.

Then I tried to reference directly to the data extension:

set @priceFormatted = FormatCurrency(@priceStr,"de-AT")

The error message:

An error occurred when attempting to evaluate a FormatCurrency function call. The input value () cannot be formatted as a number. The first parameter value for a FormatNumber call must be a numeric type supported by the requested format. Value: Format: C

I get that message by using textfields, decimals or numbers in the data extension.

---

update: My source data field from a data extension: p3_Product1_Preis_Text1 (formatted as text) e.g. 17,99

My goal: convert that data into the currency format of the specific country by using the culture-code

My approach: var @priceStr, @priceFormatted /generate new var´s set @priceStr = replace(@p3_Product1_Preis_Text1,',','.') / replace the comma by a dot to make it fittable for the cuurency function set @priceFormatted = FormatNumber(@priceStr,"C","de-DE") / using the function

The error message: the replace-function is working. Thanks! Now when I´m connecting the AMP with the real data extension I´m still getting that error message: "The error message: An error occurred when attempting to evaluate a FormatCurrency function call. The input value () cannot be formatted as a number. The first parameter value for a FormatNumber call must be a numeric type supported by the requested format. Value: Format: C

I get that message by using textfields, decimals or numbers in the data extension.

Thanks in advance!

----Update----

Hi Johannes, sorry but I´m kind of new in the Amp-world.

With your last answer I tried that in the initialization:

set @P3_Product1_Preis_Text1= lookup(@source,"P3_Product1_Preis_Text1","countrylangkey",@countrylangkey) set @priceStr = lookup(@source,"P3_Product1_Preis_Text1","countrylangkey",@countrylangkey) var @priceStr2 set @priceStr2 = Replace(@priceStr,',','.')

I filled that in the content block: %%=FormatCurrency(@priceStr2,"fr_FR")=%%

and received the error message: An error occurred when attempting to evaluate a FormatCurrency function call. See inner exception for details. Function Call: FormatCurrency(@priceStr2,"fr_FR")

An error occurred when attempting to evaluate a FormatCurrency function call. The input value () cannot be formatted as a number. The first parameter value for a FormatNumber call must be a numeric type supported by the requested format. Value: Format: C

What am I doing wrong? :-(

Answer 1

I have tried this on a cloudpage.

%%=FormatCurrency("50,00","de_AT")=%%<br>
%%=FormatCurrency("50,00","fr_FR")=%%
<br>

<br>
%%=FormatCurrency(50.00,"de_AT")=%%<br>
%%=FormatCurrency(50.00,"fr_FR")=%%

Result

I believe you did something wrong. maybe a typical mistake like this one %%=FormatCurrency(50,00,"fr_FR")=%% In this case the comma will be used to seperate the parameters of the function and will not be used for the currecy value.

If you have troubles with , and . inside the variable use the AMP function replace.

%%=Replace(@priceStr,',','.')=%%

Code Example 2:

%%[
var @priceStr, @priceStr2
set @priceStr = "20,50"
set @priceStr2 = Replace(@priceStr,',','.')
]%%

%%=v(@priceStr)=%%
%%=v(@priceStr2)=%%
<br><br>
%%=FormatCurrency(@priceStr,"fr_FR")=%%<br>
%%=FormatCurrency(@priceStr,"de_AT")=%%<br>
<br><br>
%%=FormatCurrency(@priceStr2,"fr_FR")=%%<br>
%%=FormatCurrency(@priceStr2,"de_AT")=%%<br>

Result 2:

When you want to have this value while Sending you should request it with a LookUp or directly from the DataExtension (if you are sending from it). For reference of LookUp: https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-programmatic-content.meta/mc-programmatic-content/lookup.htm