Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Extracting currecy Characters but keeping decimal separators

Hello Guys,

I am trying to created a Calculated Filed which extracts numbers and decimal separator from Fields with Currecy symbols.

Example Data

ARS10,952.23

S/.5,324.23

\$1,234.34

Ch\$3,452.12

I am trying to get

10,952.23

5,324.23

1,234.34

3,452.12

I am using the KeepChar Functions wich is supposed to allow multiple characters. The problem is that is not allowing me to keep numbers and characters las , or . or any other letter

What works: KeepChar([Order Value], 0123456789)

Not working

KeepChar([Order Value], ´0123456789´) or KeepChar([Order Value], ´.,0123456789´) or KeepChar([Order Value], ´\$0123456789´)

The Function Char () works but allows just one symboy; for example KeepChar([Order Value], Char(44))

How should I addres this issue?

All the best,

Dino

1 Solution

Accepted Solutions
Contributor III

Based on some of the very helpful answers mentioned above to your problem, I tried the following which worked for me.

Data_tmp:

Order value

"ARS10,952.23"

"S/.5,324.23"

"\$1,234.34"

"Ch\$3,452.12"

];

Data:

KeepChar([Order value],'0123456789')/100 as CurrencyVal

Resident Data_tmp;

DROP Table Data_tmp

4 Replies

The following should work: KeepChar([Order Value], '.0123456789'). Further make sure that these values are recognized as numbers with the right delimiters - if not you might need an additionally num#() with your needed format-string.

- Marcus

Try this?

Num(Num#(PurgeChar([Order Value],'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz\$/.,')))/100

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Creator III

use KeepChar to determine the first position of a number in the string:

TEST:

Mid([Order Value],Index([Order Value],Left(KeepChar([Order Value],'0123456789'),1))) as [Order Value]

;

Order Value

'ARS10,952.23'

'S/.5,324.23'

'\$1,234.34'

'Ch\$3,452.12'

];

- Christian

Contributor III

Based on some of the very helpful answers mentioned above to your problem, I tried the following which worked for me.

Data_tmp:

Order value

"ARS10,952.23"

"S/.5,324.23"

"\$1,234.34"

"Ch\$3,452.12"

];

Data: