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

Find more posts tagged with

Accepted answers

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.

That worked! Thank you!

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 comments

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.

text to number.jpg

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

text to number.jpg

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

text to number.jpg

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.

text to number.jpg

That worked! Thank you!

text to number.jpg

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!

text to number.jpg

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

text to number.jpg

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.

text to number.jpg

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

text to number.jpg

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.

text to number.jpg

How can i use this function in domo dimenssion level.

text to number.jpg

I have a dimension that is actually number formate but domo showing Text(T). So i need to convert that text(T) into Number(123)..

Give me the replay how to do it.

text to number.jpg

This seems to be a problem with the data source. Just check if your source dataset is setting the right datatypes. If you cannot correct that, then change the column datatype in a workflow and power your card with the transformed dataset.

text to number.jpg

@DomoBeaver You can convert that column's datatype in a workflow to whole number ?

text to number.jpg

This is what I did and it worked for transforming a column datatype from text to number as my original dataset could not be corrected I would have to do it in ETL workflow.

Note that my original data even though was a text type it did not contain any text character but strictly number. It was just incorrectly recorded as text type data.

So I started with Text Formatting and Only Show Numbers in this step, and then Set Cloumn Type to Whole Number in the next step, and voila you got Account Number (my column name) transformed to what supposed to be a numeric datatype.

text to number.jpg

Quick Links