Unanswered

Expression to remove special characters: EUR and USD symbols does not work

(0) Share

Report

Posted on by CU22080723-2

Hi Everyone,

i have built a power automate flow to extract infomration from receipts (date and amount) and add them into xlsx, however, sometimes it fails to add values properly.

Let me give you examples:

Here's what the expression suppose to do. I want to cover all the cases that can cause incorrect data entry:

Removes 'EUR' and 'eur' strings: Eliminates both uppercase and lowercase "EUR" from the text.
Removes non-numeric characters: Uses a pattern ('[^\d.,]') to remove everything except digits, commas, and periods.
Replaces commas with periods: Converts commas to periods. For me =sum works with periods, not commas
Removes spaces: Eliminates all spaces from the text.
Trims whitespace: Removes any leading and trailing spaces from the result.

trim(replace(replace(replace(replace(replace(outputs('Extract_information_from_receipts_2')?['body/responsev2/predictionOutput/result/fields/total/value'], 'EUR', ''), 'eur', ''), '[^\d.,]', ''), ',', '.'), ' ', ''))

Any ideas hwo to fix this isse. Maybe I have the wrong order in my expression or any other mistake? Could someone help me here. I need the correct expression.
To keep it simple, i need an expression that removes everything except numbers and replaces commas with dots if any.

I will then adjust the expression for the Amount column in my flow

Categories:

AI Builder

I have the same question (0)

All responses (3)

Answers (0)

CU22080723-2 58 on at

Like (0)

Report

I have adjusted the expression.

this one removes eur symbol:

replace(replace(replace(replace(outputs('Extract_information_from_receipts_2')?['body/responsev2/predictionOutput/result/fields/total/value'], '€', ''), ',', '.'), '[^\d.]', ''), ' ', '')

am I missing something or maybe you have ideas how this expression can be further improved?

Was this reply helpful? Yes No
codeninja.sj 115 on at

Like (0)

Report

Could you explain which model was used to retrieve the amount value? Also, have you used the number field to extract the amount value?

Was this reply helpful? Yes No
Chriddle 8,436 Super User 2025 Season 2 on at

Like (0)

Report
Unfortunately Regex is not available in a PA replace() function ;(

Use toLower() to avoid case sensitivity issues

Here are 3 different ways to approach this problem:

Left one: Simple replacements.

(Replace item() with your value)

trim( replace( replace( replace( replace( replace( replace( toLower(item()), ',', '.' ), 'euro', '' ), 'eur', '' ), '€', '' ), 'usd', '' ), '$', '' ) )

Middle: Here you don't need to know what to replace. This filters for numbers and dots. but it's not a simple expression anymore, you need a Select (or a Filter).

Select_2 From

chunk(replace(outputs('Compose'), ',', '.'), 1)

Select_Map

if( contains( '.0123456789', item() ), item(), '' )

Compose_2

join(body('Select_2'), '')

Right: This removes all characters in "€$ abcdefghijklmnopqrstuvwxyz". Add more characters if needed.

xpath( xml('<doesnotmatter/>'), concat( 'translate("',toLower(replace(outputs('Compose_3'), ',', '.')), '", "€$ abcdefghijklmnopqrstuvwxyz", "")' ) )

Was this reply helpful? Yes No