Write formula in the cell to delete "money icon"

[Tanner_IST]

Hi,

I'm getting my monthly credit card expence report from the bank in excel format.

But problem is, expence amount cell has also money icon. e.g. i spent $5,12, it's in cell written as 5,12 usd. As far as i know, cell type changes to "text" from "number" if there's text in the cell.  Then Qlick can't sum the expences.

How can i write a formula to clean money icon in cell ? Or is there any other method you use ?

thanks,

[Ravi_Nagmal · Accepted solution]

@Tanner_IST Try using Num() function in load script or use subfield() function to exclude text from column

[anat · Accepted solution]

can you try below script

LOAD a,Num(Replace(a,'$','')) as a1;
load * Inline [
a
$512
$123

];

[Ahidhar · Accepted solution]

try this

load replace(replace(amount,',',''),'$','') as amount;
load * Inline
[
amount
"$5,12"
"$10,12"
];

[vinieme12 · Accepted solution]

use KeepChar() to keep only numbers and decimal separator, as below

tab:
load keepchar(sales,'0123456789,') as sales Inline [
sales
$5,12
5,12
];