+ Start a Discussion
SS_SFSS_SF 

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

 

VALUE(text)
Converts a text string that represents a number to a number

All Answers

Ritesh AswaneyRitesh Aswaney

Use the VALUE function

 

VALUE(text)
Converts a text string that represents a number to a number
This was selected as the best answer
SS_SFSS_SF

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

5
4 parts
6 plastic pieces
2

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