+ 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
mayur bhavsar 5mayur bhavsar 5
best good morning quotes (https://www.planmymoment.com/good-morning/) and best toilet brushes reviews (https://reviewsclub.in/best-toilet-brushes/)
mohan bhatt 2mohan bhatt 2
AwsmHeart is Collection Of Latest Hindi Shayari and Quotes
Visit AwsmHeart is Collection Of Latest Hindi Shayari and Quotes (https://www.awsmheart.com/)

william smith 53william smith 53
You want to book an air ticket, then visit American Airlines official site (https://americanairlinesreservation.org/) to make your vacations and meetings comfortable. Check out our online portal for American Airlines Reservations (https://americanairlinesreservation.org/)  and get the best discount and offers on your bookings. If you want more concessions, then call on our support number.
Maddy ParkerMaddy Parker
Benefits and benefits are there for you when you make your journey with jetblue airlines (https://airlinesreservation.org/jetblue-airlines/). You get an opportunity to book affordable flights on this airlines. The expenses of air traveling strikes us so why not book with best airlines. This airlines is also dedicated committed to offer convenient and comfortable service to all customers. Special services to fliers who need additional assistance, including individuals with disabilities and unaccompanied minors, with the attention, respect and care they deserve. They also promised to get to your destination on time without any delays. They believe in providing positive journey to passengers so that they are proud off and also, employees should feel like working every day. jetblue airlines flights (https://airlinesreservation.org/jetblue-airlines/) is designed to provide all important benefits and facilities in your journey that you require in your journey. With ample of services and opportunities, this airlines is becoming one of the best leader in Air travel industry.