Salesforce VALUE Function

In this Salesforce tutorial, you will learn the Salesforce VALUE function. You will also learn the basic syntax of the function and see various examples illustrating several use cases of the function in Salesforce.

Do you face issues while doing the calculations of the numerical data that is stored as text in Salesforce?

Suppose you are trying to add 1 to the text value of 8.5 but you are not getting the desired result.

If yes, Salesforce provides us with a function called VALUE() that can do data manipulation. The function is used to convert the text string that represents a number to a numerical value.

Furthermore, you will explore the steps of using the VALUE function in Salesforce Lightning and Salesforce Classic with the real-based scenario.

Salesforce VALUE Function

The Salesforce VALUE() function is one of the text functions that are used to perform text operations, do manipulation and manage the data effectively on Salesforce fields.

The VALUE function in Salesforce converts a text string that includes the number into the actual numeric value. It is best used in scenarios where you need to do work on numerical data stored as text.

In addition, the VALUE function ensures perfect calculations within the Salesforce environment. Make sure that the text in the function does not include any special characters but if you want use the decimal point called period or the minus sign called dash.

Moreover, if the text parameter of the function is non-numerical or in an invalid format, the formula will not work and will return the result as a blank value.

Now that you have got the basics of the VALUE function, let’s move to the next section and take a look at the syntax of the VALUE function in Salesforce.

Syntax of Salesforce VALUE Function

The syntax of the Salesforce VALUE() function is very simple and easy to use and understand. Below is the syntax:

VALUE(text)

Here:

  • text: The text is the parameter that specifies the numeric value in the text format. It is the text field, constant, or expression having a number that you want to convert to an original numerical value.

Return: The VALUE function returns the numbers passed as text in the actual numerical format. But if the text parameter is in invalid format it returns the result as blank.

In addition, you need to choose the formula return data type as “Number”, “Currency”, and “Percent”. If you choose any other data type as the formula return type, you will get an error.

Now, you have covered the syntax of the VALUE function. Let’s move to the next section and see some simple examples of the function that help you to understand how the function works in practical situations.

Examples of VALUE Function in Salesforce

The table given below gives a simple example of how the VALUE functions work in Salesforce.

The “Input Field” column shows the text expression entered by the user and the “Return” column displays the output returned by the VALUE function in Salesforce.

Input FieldOutput of VALUE() Function
“35.6”35.6
5 + VALUE(“35”)40
“USA”blank
25 + VALUE(“new york”)blank
-50-50
“$100.59”blank

With this, you have learned examples showcasing how the VALUE function works. Let’s proceed to the next section and learn the step-by-step procedure of using the VALUE function in Salesforce with a scenario-based example.

How to use VALUE Function in Salesforce Lightning

There are many scenarios in Salesforce Lightning, where you need to use the VALUE() function. One such scenario is you want to calculate the final price of the product.

Here, you will go through a step-by-step implementation of how to use the VALUE function to achieve the above-defined scenario in Salesforce Lightning. Below are the steps:

1. Login to the Salesforce account in the Lightning Experience with the help of your credentials. If you are not logged in to Lightning, shift from Salesforce Classic to Lightning Interface.

2. If you are redirected to the default home page, click on the “Gear” icon on the top right side, and from the dropdown select the “Setup” option. But if you are already on “Home Setup”, you can skip this step.

Salesforce VALUE Function

3. Click on the “Object Manager” option in the navigation bar.

Salesforce VALUE Function Example

4. On the right side in the “Quick Find” search the object in which you want to use the VALUE function. According to the scenario, you need to choose the “Products” object.

Salesforce Lightning VALUE Function

5. In the left section, click on the “Fields & Relationships” option. Then, click on the “New” button at the right to create a new field.

Salesforce Lightning VALUE Function Example

6. Choose the field type for specifying the type of information you want to store. As you are using the function, click on the radio button next to the “Formula” data type. Then, click on the “Next”.

VALUE Function in Salesforce

7. Type the “Field Label” and then click on the “Field Name” It automatically fills up based on the field label. According to the scenario, you can type “Final Price” as the field label.

See also  MOD() Function in Salesforce | Calculate remainder in Salesforce

8. Move downward and select the formula return type from the list of the available data types. By the scenario, click on the radio button next to the “Currency” data type.

After that, select the “Decimal Places” according to your requirements. Here, for the example, I have set the decimal places to “1”. After that, click on the “Next” button.

VALUE Function in Salesforce Lightning

9. Click on the “All Functions Categories” dropdown, and choose the required function category.  Following the scenario, you need to select the “Text” category.

Now search for the “VALUE” function and click on it. Then, click on the “Insert Selected Function” button under the functions section.

10. Replace the parameters of the functions with the numeric value or the text field stored number according to the scenario.

For example, you want to calculate the final price of the product based on the product price and discount. But there is a twist that the product price is stored in the text format rather than the currency.

Therefore, the calculation can’t be done between the currency and text fields. You need to change the text format to the original numerical value so that you can easily perform the calculation and get the desired result.

The formula for the scenario is given below:

VALUE(Product_Price__c) - (Discount__c * VALUE(Product_Price__c) / 100)

Here is the detailed description of the formula:

Fields, Operators, and Functions used in the formula

  • Product_Price__c: It is the custom field that represents the original price of the product.
  • Discount__c: It is the custom field that represents the discount percentage that you need to give on the product price.
  • VALUE(): It is the salesforce function that converts the text field value to a numeric format to make sure that it works as a numeric value.
  • /: It is the division operator used to divide the values.
  • 100: It is a constant numeric value.

Working of the formula:

  • Discount__c * VALUE(Product_Price__c) / 100: Here you calculate the discount by multiplying the product price by the discount percentage and then dividing it by 100 to convert it to a decimal value.
  • VALUE(Product_Price__c_) – (Discount__c * VALUE(Product_Price) / 100): Here you subtract the discount amount calculated in the above step from the product price to get the final price of the product.

11. Click on the “Check Syntax” button to verify the formula. If there is no error, you will get the notification “No Syntax error in merge fields or function”. Otherwise, you will get an error.

If you want to you can enter the “Description”, and “Help Text” to provide additional information. After that, click on the “Next” button.

VALUE Function in Salesforce Example

12. Select the profiles to whom you want to grant field-level security edit access. Some profiles may be selected by default. If you want to grant access to additional profiles, click on the checkbox next to the respective profile names.

To quickly select all the profiles, click on the checkbox next to the “Visible” option. Click on the “Next” button to move to the next step.

VALUE Function in Salesforce Lightning Example

13. You will get the list of the page layouts on which this field is visible. By default, all the page layouts are selected. But if you don’t want to add the custom field on any one of them, unselect them.

See also  BR() Function in Salesforce | How to Break Line in Salesforce

14. Click on the “Save” button to complete the process of using the VALUE() function in Salesforce.

Convert text value to numeric value in Salesforce

15. Once the formula field is created, you will be able to use this formula field. Let’s see how it works:

  • Open the “Products” item, create a new product with the fields “Product Price” and “Discount” and save the product.
  • After saving it, move to the “Details Section” where you will see the “Final Price” field with the output of the VALUE function.

Let’s understand it with the example:

  • Suppose you have entered the product with the product price “100” and discount “10”. After the execution of the formula, you will get the output for the final price “90”.
  • Hence, you can say that the formula effectively converts the text value to the numeric value in Salesforce.
Convert text value to numeric value in Salesforce Example

With this, you have learned how to use the VALUE function to convert the text value to a numeric value in Salesforce Lightning. Now, you will learn how to use the VALUE function in Salesforce Classic in the next section.

How to convert text value to numeric value in Salesforce Classic

Within the Salesforce Classic, there are various situations where you can use the VALUE function. Consider a scenario where you want to calculate the return on investment for a campaign.

Let’s explore the steps of how to create a formula to convert text value to numeric value in Salesforce Classic:

1. Sign in to your Salesforce account with your credentials in the Classic Interface. If you are not in classic experience, click on your “Profile Icon” and then click on “Switch to Salesforce Classic”.

Salesforce Classic VALUE Function

2. Click on the “Setup” option located at the top right side of the page between your name or the help option. If it is not located here, click on the dropdown arrow next to your name here you will get it.

Salesforce Classic VALUE Function Example

3. Move to the “Build” section located on the left side. Click on the “Customize” dropdown, and it will show you the list of the Salesforce Objects.

4. Choose the object on which you want to utilize the VALUE function click on the dropdown and expand the options. From the expanded list, click on the “Fields” option.

Following the scenario, you need to choose the “Campaign” object.

VALUE Function in Salesforce Classic Example

5. Scroll down to the “Campaign Custom Fields & Relationships” section. Click on the “New” button to create the new field in Salesforce Classic.

VALUE Function in Salesforce Classic

6. From the list of the data type specify the type of information that the custom field will contain by clicking on the radio button next to it. According to the scenario, you need to choose the “Formula” as the data type.

After that, click on the “Next” button to proceed to the next step.

How to convert text value to numeric value in Salesforce Classic

7. Enter the “Field Label” for the new custom formula field and then click on the “Field Name” It gets automatically filled up based on the field label.

As per the scenario, you can enter the field label “Return on Investment”.

8. Scroll down to the formula return type and choose the appropriate data type for the formula return type by clicking on the radio button next to the desired data type.

In accordance with the scenario, I select the “Percentage” as the data type.

Move down at set the “Decimal Places” to 0. After that, click on “Next” and move to the next step.

Convert text value to numeric value in Salesforce Classic

9. It is time to define the formula in the formula sub-tab, click and expand the “All Function Categories” dropdown. Search and select the category of the function in which your function resides.

In accordance, with the scenario, click on the “TEXT” category. Now select the function from the category and click on it. By the scenario, you need to click on the “VALUE” function.

10. Click on the “Insert Selected Function” button below the function section to enter the formula in the subtab. Now, replace the parameters with the appropriate fields and operators to create the formula.

For example, you need to calculate the return on investment for a campaign. But here both the fields that you required are of the text type having numeric value and due to this performing calculation is not possible.

See also  SQRT() Function in Salesforce | Find Square Root in Salesforce

Therefore, you need to first convert these text values to the original numerical value in Salesforce. Below is the formula for that:

(VALUE(Revenue__c) - VALUE(Cost__c)) / VALUE(Cost__c)

Here is the detailed description of the formula:

Fields, Operator, and Function used in the formula:

  • Revenue__c: It is a custom field that represents the revenue.
  • Cost__c: It is a custom field that represents the cost.
  • VALUE(): It is the function used to convert the text into a numeric value. It ensures that the Revenue__c and Cost__c fields are treated as numeric fields.
  • /: It is a division operator used to divide value.

Working on the formula:

  • (VALUE(Revenue__c) – VALUE(Cost__c)): It calculates the difference between the revenue and the cost.
  • (VALUE(Revenue__c) – VALUE(Cost__c)) / VALUE(Cost__c): It divides the above-calculated result by the cost to calculate the return on investment in percentage.

11. To verify whether the formula is correct or has some error, click on the “Check Synatx” button. If it has an error, the error message will be displayed. Otherwise, it displays “No Synatx error in merge fields or function”.

Optionally, you can also enter the “Description” and “Help Text” to provide more information about the formula. Even you can handle the blank fields if you want.

After that, click on the “Next” button to proceed.

Convert text value to numeric value in Salesforce Classic Example

12. Now establish field-level security by selecting the profiles to which you want to grant edit access to this field via field-level security. The field will be hidden from all those profiles that you don’t have added to the field-level security.

Some of the profiles are already selected by default. If you want to modify anything and want to grant or revoke the edit access select and unselect profiles accordingly.

But here I want to quickly choose all profiles in one go, simply click on the “Visible” checkbox on the topic. To proceed to the next step, click on the “Next” button.

Use VALUE Function in Salesforce Classic

13. You are on the add-to-page layout step. Select the page layout that should include this field. The field will be added as the last field in the first 2 column section of these page layouts.

The field will not appear on any pages if you do not select a layout. If you want to change the location of the field on the page, you will need to customize the page layout.

By default all the page layouts are selected, you can uncheck the checkbox and unselect them if you aren’t required to add the custom field on that specific page layout.

14. Click on the “Save” button to complete the process of creating a formula field where you have used the VALUE() function of Salesforce.

Use VALUE Function in Salesforce Classic Example

15. Now, you will be able to use this formula field. Let’s see the steps:

  • Click on the “Campaigns” item in the Salesforce Classic, create a new campaign with the fields “Revenue” and “Cost”, and save it.
  • Open the campaign move to the details section and locate the “Return on Investment”. Here you will see the output of the VALUE function on the field based on your formula.

Let’s understand it with the example:

  • Suppose you entered a campaign with a revenue of “7500” and cost “5000”. After the execution of the formula, you will get the output for the return on investment “50%”.
  • Hence you can say that the formula effectively converts the text value to the original numeric value in Salesforce Classic using the VALUE function.
Example of VALUE Function in Salesforce

With this, you have learned how to use the VALUE function to create a formula that converts a text string that represents a number to a number in Salesforce Classic.

Conclusion

It is possible to do the calculations of the numerical data that is stored as text in Salesforce.

You can use the Salesforce VALUE function in the formula field to convert a text string to a number.

Through this complete article, you have learned the syntax and use cases of the VALUE function. Additionally, you have explored the step-by-step implementation of the function in both Salesforce Lightning and Salesforce Classic.

You may like to read the following articles: