Converting different number formats from string source

[stelian]

Dear community, I am trying to extract data from a form (AI Form model) but the data in that form comes from different users and may sometimes have different number formats ($70,000.00 or 70 000,00).

The flow works well when standard US number format is used but fails when the number format comes as # ###,00 - a format used in many countries. I need to build a flow what would understand both formats. 

Source information is string ("70 000,00"), which needs to be converted to float. 

error:  InvalidTemplate. Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language function 'float' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

Any suggestions? 

[abm]

Hi @stelian 

So you want to convert 70 000,00 to 70000.00? The issue here is you have different standard of input data. Can't you add an IF condition to check whether the input string contains comma (,) or not. If it is then use a replace() expression to convert to 70 000.00. Finally you could use a trim() expression to convert to 70000.00. Is that work for you?

Thanks

[stelian]

Hi and thank you for the message. That does not really work because if I replace comma with a period that the correct formats  like 1,000.00 will be converted to 1.000.00 which will mess things up. 

I want to convert all possible formats coming in in string format: 

$1000.00, $1,000.00, 1000.00, 1 000,00, 1'000.00, 1.000,00, etc. 

to US currency format:

$1,000.00

[abm]

Hi @stelian 

Thanks for your reply.

You can use the substring() function to find the third last character is comma or not. Once that is done then declare and array with all the other possible characters to do the replace. Couple of IF statements, replace and substring() needed to achieve this. If you need further help please let me know.

Thanks

[stelian]

hi @abm, I am back to my issue. I've been trying to solve this but unfortunately I was not successful. Taking a vacation also did not help. Now I have another form that has the same problem. Would it be too much if I asked you to provide me a more detailed solution. I've tried everything before asking for this 🙂 If you could provide a solution for varTotalRequested (formatted as string) that would output the result as #,###.## I'd be jumping of happiness! I do not have an IT background but i've been developing for years using MS Access and VBA. Recently i switched to Sharepoint and Power Automate. Thanks in advance! 

[stelian]

This is what i got so far. Any suggestions on how to optimize it?

formatNumber(if(equals(sub(length(triggerBody()['text']),lastIndexOf(triggerBody()['text'], ',')),3),float(replace(replace(triggerBody()['text'], ',','.'),' ','')),float(triggerBody()['text'])),'C2','us-US')