+ Start a Discussion

How to convert text to number in fomula field

Please help me ,

 I create new formula field ( Data type is Number ) for calculate value from text field but system not allow me to calculate it. Do you have any suggest me ?


Best Answer chosen by Admin (Salesforce Developers) 
Ritesh AswaneyRitesh Aswaney

Use the VALUE function


Converts a text string that represents a number to a number

All Answers

Ritesh AswaneyRitesh Aswaney

Use the VALUE function


Converts a text string that represents a number to a number
This was selected as the best answer

It work! now , Thank

Onttu Lindeman 12Onttu Lindeman 12
I tried that with 09:12 and it just errored out.
Kevin Hart 3Kevin Hart 3
That's because 09:12 isn't a number, it's a time...  use datetime if you have dates too or text with validation rules if you only have hours/minutes. You can also convert time to minutes in a day and validate that way - split the string at the colon and take hours * 60 + minutes and convert to a number.
Carlo Cruz 3Carlo Cruz 3

Hi Ritesh & Kevin - I created a formula field to convert a formula text field (that represents a number) using VALUE (FormulaTextField), I got no Syntax, however, when looking at the Opportunity layout, the formula field is showing an error "#Error!". 

Would appreciate your help. Thanks!

Kevin Hart 3Kevin Hart 3
That generally means the text being passed in can't be converted to a number (does it contain something other than numbers such as letters, spaces or punctuation?). If you provide an example of the text and the formula I may be able to provide a little more specific suggestion, but make sure you are trimming white space and not passing colons etc.
Kevin BarnettKevin Barnett

Hey Kevin, is there anyway to convert to a number if the text field contains both numbers & letters? I have a similar problem as OP here. I have a text field that gets mostly numbers inputted, but sometimes also words example...

4 parts
6 plastic pieces

Is there anyway to write a new formula field (number) that will just pull the numbers from these text fields?

Kevin HartKevin Hart
Hi Kevin, you've got a whole series of numbers here, so it is hard to know what type of single number you are wanting at the end of the day, but let's look at a few possibilities.

First let me start by saying it would be much better to sanitize the input by using a number field or writing a validation rule to only allow numbers to be input if possible. If you are dealing with multiple values you could use a master detail custom object to have fields for both the description and the quantity separately and use a rollup field to add, subtract, etc. rather than placing all the data in a text field. 

If those aren't options then you still have some possibilities:
1. If you just want that first number 5: you could write a formula to strip everything after the first whitespace character
2. You could use a formula with find to locate specifc values or substitute to remove the alpha characters you don't want to end up with a number that is them just concatenated (lumped) together such as 5462
3. Instead of a formula you could add another number field and use a process builder/flow combo to parse the data into a collection and perform math on the collection such as addition, subtraction, etc. or could use Apex to parse it in a similar fashion.
Kevin BarnettKevin Barnett
Hi Kevin,

I just want to copy the number in the text field into a new field. In an ideal world, I'd just create a new number field, but the powers that be don't want to because they want to track all the data that's already in the existing text field, but they can't sum/average/etc the data in reports because it's a text field and often has words. I know i could do VALUE() for the text fields that just have a single number, but I'm not sure what to do when they have words as well
Sophia Smith 5Sophia Smith 5
You can use this method:
/* uncomment and adjust currency sign */ /* "$ " & */ /* uncomment next and last lines to denote negatives using the American accounting style -1234 == (1,234.00),otherwise it's shown as -1,234.00 */ /* ! IF(Cash_L__c < 0, "(", "") & */ /* comment next line if uncommented line above */ IF(Cash_L__c < 0, "-", "") & SUBSTITUTE( /* multiplier and divider of 100 ('* 100' and '/ 100') indicate number of decimal places (2), adjust all if you are going to round to a different decimals */ /* millions */ IF(ABS(ROUND(Cash_L__c,2)) >= 1000000, TEXT(FLOOR(ROUND(Cash_L__c * 100, 0) / 1000000 / 100)) & ",", "") & /* thousands */ IF(ABS(ROUND(Cash_L__c,2)) >= 1000, RIGHT(TEXT(FLOOR(ROUND(Cash_L__c * 100, 0) / 1000 / 100)), 3) & ",", "") & /* hundreds */ RIGHT(TEXT(FLOOR(ROUND(Cash_L__c * 100, 0) / 100)), 3) & /* '*100' and ROUND parameter of 2 indicate number of decimal places (2)*/ "." & RIGHT(TEXT(ROUND(Cash_L__c * 100, 0)), 2) ,"-","") /* ! & IF(Cash_L__c < 0, ")", "") */

Source: stackexchange
You can also see some guides on usersadvice.com