Skip to Content
avatar image
Former Member

Conversion from text to number and Substr

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.

Add comment
10|10000 characters needed 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

  • 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

  • Get RSS Feed

1 Answer

  • Best Answer
    Mar 28, 2017 at 09:41 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded