Convert text into a number

Reply
Highlighted
White Belt

Convert text into a number

Hello,

 

I have a problem with numeric data being recognized in Domo as text (word) data when I'm trying to build my card. I know this is the problem because the data (which is numbers) is placed under CATEGORIES and it is listed with a T symbol.

 

I need help with a Beast Mode formula that will translate this data into numeric values so it falls under the VALUES with the "123" symbol next to it. 

 

Please help.

 

Thank you,

 

Valerie


Accepted Solutions
Green Belt

Re: Convert text into a number

Yes that is probably the issue. You can try this but I have not verified that it will work. 

 

SUM(REPLACE(`number_column`,',',''))

 

This replaces all commas with an empty string, which essentially removes all commas. The SUM function should then be able to parse your column as a number instead of a string.

White Belt

Re: Convert text into a number

That worked! Thank you!

White Belt

Re: Convert text into a number

This works.

Otherwose DOMO treats the 1st comma as the decimal point and rounds up rest of the number.

Example: 1,053, 233 will be treated as 1.00 when you perform mathematical operations.

                 REPLACE(`number_column`,',','') takes all the ',' off and makes the text 1053233, that is treated     like a number.


All Replies
Green Belt

Re: Convert text into a number

If there are definitely no non-number values in the column, then using SUM(`Column Name`) should convert all the rows in that column to numbers.

Moderator

Re: Convert text into a number

@Valeriehern, did shaanarora's reply help you out?

White Belt

Re: Convert text into a number

Hi there,

 

Thank you for the help, but this did not work for me. Do you think the problem is that the numeric values include a comma. Example: 12,500 ?

 

-Valerie 

 

 

Green Belt

Re: Convert text into a number

Yes that is probably the issue. You can try this but I have not verified that it will work. 

 

SUM(REPLACE(`number_column`,',',''))

 

This replaces all commas with an empty string, which essentially removes all commas. The SUM function should then be able to parse your column as a number instead of a string.

White Belt

Re: Convert text into a number

That worked! Thank you!

Visitor

Re: Convert text into a number

I still cannot make this work.  I have a column of text that is made up of numbers but domo is reading it as text.  I need it to be read as numbers.  When I use the solution above it will not work on a chart.

 

Please help and thanks!

Green Belt

Re: Convert text into a number

@DomoBeaver Can you provide a sample of the column you're trying to convert to a number? Is it possible that there are nulls in it?

White Belt

Re: Convert text into a number

tried

 

 

RIGHT(`Forecasted/Actual Churn Value`,2)*1

 

or

 

SUM(REPLACE(`ACV Amount at Risk`,',',''))

 

and both not working on my end or Im doing wrong. The RIGHT commands output something but wrong value - meaning to small on the real $ value while the SUM command is not outputting anything at all.

 

Please find the attached screenshot.

Visitor

Re: Convert text into a number

I saw this as I was searching for a similar solution. Here is how I solved it. I create a Magic ETL dataset to keep this complexity away from my business users. 

 

Our Salesforce outputs converted $ amounts like:

 

USD 12,500

 

I added a Replace Text Step to and ETL as:

 

Column        Find                               Replace

 

Amount        USD                                 Empty String

 

Amount        ,                                       Empty String

 

Amount      (\.[0-9][0-9]) (Regex)       Empty String

 

Amount      <space>                           Empty String

 

 

Then add a Set Column Type step

 

to convert Amount from Text to Decimal.

 

Hope that helps,

Dennis

Announcements
Win free lodging at Domopalooza! We want to hear your Domo story, enter our Dojo contest and win up to four nights lodging at Domopalooza. Click here!