You need to sign in to do that
Don't have an account?
SS_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 ?
Use the VALUE function
All Answers
Use the VALUE function
It work! now , Thank
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!
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?
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.
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
/* 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