Skip to Content
0

Conversion from text to number and Substr

Mar 23, 2017 at 06:53 PM

102

avatar image
Former Member

Hello All,

I would like to convert text to number because we have attributes from master data and in WEBI its considering it as text. e.g.

Amount

1,000 CAD

2,000 CAD

Result should be following in order for me to apply calculation on amount. Can you please help me on the WEBI formula for Substr?

Result

$1000

$2000

Thanks.

10 |10000 characters needed characters left characters exceeded

Hi,

if you think that Unit of Currency is always in 3 chars then try the below formula.

What is your query coming from? which data source?.

=Substr([Amount];4;Length([Amount])

Thanks,

Jothi

0
Former Member

Hi Jothi, thanks for your reply. My DS is BW and the object is an attribute of MD. I have used replace function to get remove CAD but now i noticed that the format of number needs to be changed also.

Example:

25.456,00

Result should be:

25,456.00

The comma and decimals are in different locations. Do you have some information on it in terms for changing the format?

Thanks

0
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
AMIT KUMAR
Mar 28, 2017 at 09:41 AM
0

use this formula=ToNumber(Replace(Substr([Amount];1;Pos([Amount];" ")-1);",";""))

After that for currecy symbol,right click on the column->Format Number->Currency->apply the format.

Show 5 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thankyou so much Amit for your help :) It works and round off values, but once the report is converted in French i am getting #Error. It happens once the "Tonumber" is used. .

Is there a way i can just swap ,(comma) & .(dot) then convert into number?

Example:

25.456,00 CAD

Result:

$25,456.00

1

try this.

=ToNumber(replace(Replace(Substr([Amount];1;Pos([Amount];" ")-1);",";"");".";""))

0
Former Member

i have tried and it works ok in English but as soon as we change language to French "#Error" shows up. Any help please?

=ToNumber(Replace(Replace(Substr([Amount];1;Pos([Amount];" ")-1);".";"");",";"."))

Thanks Amit for your help.

0

Without using this formula how are the values in [Amount] object in French ?

0
Former Member

issue is fixed now we have created formulas in bex. Thanks Amit for your help.

0