Convert text into a number

Valeriehern 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.

Thank you,

Valerie

Accepted Solutions
shaanarora 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.

Valeriehern White Belt

Re: Convert text into a number

That worked! Thank you!

NFSharma 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
shaanarora 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.

kshah008 Moderator

Re: Convert text into a number

Valeriehern 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

shaanarora 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.

Valeriehern White Belt

Re: Convert text into a number

That worked! Thank you!

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.

shaanarora 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?

jbuaron 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.

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