MID Function in Salesforce | How to get substring in Formula Field in Salesforce

This Salesforce tutorial will discover how to get the substring using the MID function in Salesforce. We will also explore the syntax of the function and the practical application of the Salesforce MID function in everyday business situations.

While working in a multinational USA-based company that uses Salesforce for CRM, I got a task to create a group of leads and contacts on the basis of email addresses and assign it to the concerned team manager so that each and everyone gets the proper message.

So, to do this, I searched and tried to find an easier solution for the division of leads and contacts. Finally, I got the solution and decided to use the MID function that separates the domain part of the email address from the complete email.

If you want to learn how to use the MID function in Salesforce, with examples in both Salesforce Lightning and Salesforce Classic, you are in the right article. So, let’s go through the complete article to learn about the Salesforce MID function.

What is MID Function in Salesforce

Within Salesforce, there are multiple TEXT functions that are used for manipulating text and performing various text operations on the Salesforce fields, and out of these functions there is one function called the Salesforce MID() function.

The Salesforce MID() function, is used to return characters from the middle of a text string, given a starting position and length. This MID function is short for a middle function that is used by the developers to fetch a specified part of the text string depending upon the position within the string.

In addition, this function is useful in cases of text manipulation, especially extremely valuable when we need specific characters or we can say substring from the large text string or field.

Furthermore, the below is the syntax of the Salesforce MID function:

MID(text, start_num, num_chars)

Here is a detailed description of the parameters:

• text: The text parameter specifies the string, expression, or text field from which we want to extract the portion of text or substring.
• start_num: The start_num parameter specifies the position from which we want to extract the text. In simple words, we can say that it specifies the number of characters from the left to use as a starting position. In addition, the parameter is an integer value and the first character of the string is positioned at 1.
• num_chars: The num_chars parameter specifies the number of characters that we want to extract from the text string or text field. In simple words, we can say that it is the total number of characters that we want to return from the text string.

Now, let’s see one example to clearly understand the concept of the MID function in Salesforce:

Imagine you are working in a research company as a data extraction specialist. One day you got a task to extract a specific word from the description and you wanted to extract the description from the start of the 8th character of the sentence till the total number of characters was 15.

Let’s suppose you have a text as given below and according to syntax below is the formula:

MID("The quick brown fox jumps over the lazy dog.", 11, 15)

With this, we have covered the Salesforce MID function, with its syntax and practical applications that explain the working of the MID function in detail. Next, we will explore the step-by-step use of the MID function in both Salesforce Lightning and Salesforce Classic, with an example.

Read LPAD Function in Salesforce

How to use MID Function in Salesforce Lightning

Here are the steps for using the MID function to get the substring in Salesforce Lightning: To understand this concept better, we will go through a scenario.

Scenario: Let’s suppose you are working in a customer support company. One day, you found that some of the cases added their contact information in the description box including their phone number. And after finding the resolution of the case, your job is to call the particular contact.

So, now your task is to find out the contact number from the description. But you notice that manually fetching the contact number from the description is time time-consuming process.

So, you decide to create the formula that fetches the contact number from the description, and for this research, you find that it can be easier done with the MID function of Salesforce with some other functions too.

If you want to explore the steps of how to use the MID function in Salesforce Lightning to get the solution of the defined scenario follow the below given steps:

Step-1: Access Salesforce Lightning Account Home Page

• Login to your Salesforce Lightning interface account by entering your username and password.
• Check whether you are on the main setup page of the Lightning interface, if not move your cursor to the gear icon located on the right side of the screen.
• Now, click on the “Gear” icon to open a dropdown menu, and from the dropdown menu, select “Setup” (Setup for the current app).

Step-2: Decide and choose the Object

• When you reach the main setup page for the current app in the Lightning interface, navigate to the “Object Manager” option located in the navigation bar.

• Once you click, the object manager page will be opened and here you can find both custom and pre-existing Salesforce objects. Now, locate the specific object according to the scenario in which you want to use the MID function, utilizing the “Quick Find” search box.
• After you find the object, in my example “Case” click on it to proceed.

Step-3: Create and Choose the Field Type

• In this step, navigate to the left side of the page, where you’ll find the “Fields & Relationships” option and click on it.
• Now, to create the custom field where you can apply the MID function click on the “New” button situated at the top right-hand side of the page.

• Once you click the new button, you will get the list of available field types within Salesforce. From here choose the field type based on the specific requirement of the scenario. As discussed in the scenario that you want to use the MID function, choose the “Formula” field type by clicking the circle next to it.
• Afterward, click the “Next” button to proceed to the next step.

Step-4: Decide the Formula Output Type

• Once you click the next button, you will be redirected to the “Choose Output Type” step. Here you need to fill up the “Field Label” and “Field Name”. Here as per the scenario, I filled out the field label as “Contact Number”.
• After that, when you click inside the field name box, it will automatically be filled for you.
• Now scroll down and select the appropriate data type for the field where the generated output will be stored. In accordance with the scenario, I need the result in text format. So, to choose the text data type, click the radio button next to “TEXT”.
• Then, click the “Next” button to proceed to the next step in the process.

Step-5: Enter Salesforce Formula with MID function

• In this step, move to the right side of the page toward the “Functions” section. Here, you will see the “All Functions Categories” dropdown, click on it.
• Now will get the various function categories available in Salesforce, such as “Date & Time”, “Logical”, “Math”, “Text”, and “Advanced”. But as you know our main need is the MID function, which falls under the “Text” category, select “TEXT” from the list.
• After you click on the text, you will get the function related to the text category. Now, search and find the MID function from here, and after locating click on it.
• Once you click the function, you will get the button “Insert Selected Function” under the function category box. Click on this button to insert the function into the advanced formula subtab.
• Similarly, insert all the functions that you need for the formula at their specific position. After that, replace the parameters within the functions with appropriate merge fields, functions, and operators as per your specific scenario requirements and create the formula.

In my case, as per the scenario, the formula to fetch the contact number from the description is as given below:

IF(
    NOT(ISBLANK(Description__c)),
    MID(
        Description__c,
        FIND("Phone:", Description__c) + 7,
        12
    ),
    ""
)

Here is a detailed explanation of the formula:

First, use the IF statement that checks if the Description field is not empty using the NOT() function of Salesforce:

If the field is empty, the formula returns the empty string as “”, which means there is no phone number in the description field. If the field is not empty, the formula works as follows:

• We use the MID() function of Salesforce, to fetch the substring phone number from the description field.
• Then, to set the start position of the extracting text use the FIND function, the function located the position of the text “Phone:” within the description field. After that 7 is added so that seven more characters are extracted after the text “Phone:”.
• Next, we assume that the phone number is 12 characters long, so we set the number of extracting text to 12 by simply passing an integer value of 12.

• After you enter the formula, click on the “Check Syntax” button to check whether the formula is correct or has some errors. If the formula has an error the error message will displayed, otherwise it displays the message “No Syntax errors in merge fields or functions”.
• You can include the “Description” and the “Help Text” if you want to and you can also manage the blank fields. Click the “Next” button after that.

Step-6: Establish Field-Level Security

• In this step, you are on the page where you need to set up the “Field Level Security”. By setting up the field-level security you can choose the profiles to whom you want to edit the field. If you don’t include the custom formula field in the field-level security, it will not be visible to profiles that have not been selected.
• As I want to make the custom formula field accessible for all user profiles, I have selected the checkbox next to the “Visible” option. After that, click the “Next” button to proceed to the next step.

Step-7: Add to Page Layout

• In this step, you need to choose the page layout where you want to include the custom formula field. It is important to note that the field will be added as the last item on the selected page layout.
• If you don’t choose a layout, the field will not appear on any page. By default, all the available page layouts are pre-selected. You can leave a page layout unselected if you don’t want to add the field to appear on all layouts.
• After that, click on the “Save” button to save the newly created formula field.

Step-8: Testing

Once the formula field is created, you can use the formula field. Let’s see an example:

• Open the “Cases” item, create a new case with the “Description” field, and save the case.
• Once you save, move to the cases details page. Here you will observe the “Contact Number” field that shows you the outcome of the MID function.

Let’s deeply understand it with the example:

• Suppose you have created the case with the description “Customer Name: John Doe Phone: (123) 456-7890 Email: john.doe@example.com Address: 123 Main St” and save it. Then the formula executes and gives the contact number as (123) 456-7890. Hence, the formula effectively extracts the text from the text field.

With this, we have learned the use of the MID function in Salesforce Lightning. Now, we will move and explore the implementation of the MID function in Salesforce Classic, with the help of a scenario.

Read PICKLISTCOUNT Function in Salesforce | Count Selected Values in Multi-select field in Salesforce

How to use MID Function in Salesforce Classic

Here are the steps for using the MID function to get the substring in Salesforce Classic: To understand this concept better, we will go through a scenario.

Scenario: Let’s suppose you are working as a salesperson in a software company where you sell software products. Each product that you sell comes with an activation number and the activation number has a specific format i.e. YYYYMMDD-XXX.

One day you got a task to separate the year, month, and date of activation of the product i.e. YYYYMMDD from the activation number of the product. As a Salesforce developer, you decided to create the formula for this to do the process faster.

After searching you decided to use the Salesforce MID function with some other functions for this logic creation that extract the activation dates from these numbers.

To understand how to utilize the MID function in Salesforce Classic to solve the specified scenario, follow the steps given below:

Step-:1 Setup Salesforce Classic and Choose Object

• If you are logged in to the Salesforce Lightning Interface by default. First, switch from Salesforce Lightning Experience to Salesforce Classic. For this, go to your “Profile” and select “Switch to Salesforce Classic” from the dropdown menu.

• Once you are redirected to the Salesforce Classic user interface, click on “Setup”, which is located between the help and username options at the top of the screen.
• Now, scroll down to the “Build” section, find the “Customize” dropdown, and select the object where you want to use the MID function. In accordance with the given scenario, I selected the “Products” object of Salesforce.
• After selecting the product object, a dropdown menu will open with various options. But as your main task is to create a field where you can utilize the Salesforce MID function, click on the “Fields” option.

Step-2: Create Field for the Function

• As you have clicked on the fields option, you will be directed to the page where various fields are displayed. Scroll down to find the section labeled “Products Custom Fields & Relationships” and then click on the “New” button.

• Once you click the new, you will get the list of available field types in Salesforce. Choose the data type that suits best your requirements. In this scenario, our main need is to use the MID function, therefore you have to choose the “Formula” field type by selecting the radio button next to it.
• Then, click on the “Next” button to proceed.

Step-3: Choose Output Return Type

• In this step, enter the “Field Label” and “Field Name” fields. As per the scenario, I entered “Activation Date” as a field label, and when you take your mouse over the “Field Name”, it will automatically populate with value.
• After that, specify the return type for the formula field. Scroll down to the “Formula Return Type” section and choose the appropriate option. As you are extracting the date from the activation number, select the radio button next to “Date” to make sure that the result is in date format.
• Then, click on the “Next” button to move to the next step.

Step-4: Enter Formula in Advance Tab

• Now, you are on the step where you need to enter the formula according to the scenario. Move to the right side of the page, in the “Functions” section. Then, click on the “All Functions Categories” dropdown and choose “TEXT” as the category since you know that the MID function is one of the text-type functions.
• After you click on the text category, you will get the list of all functions within that category. Find the “MID” function and click on it.
• When you click on the function, you will see a button “Insert Selected Function” appear under the functions box. Now, click on this button, and the function will be inserted into the advanced formula box. Here, replace the function parameters with the appropriate text, fields, etc. as per the scenario.
• Here’s the formula for our specific requirement of extracting the date from the activation number of the product.

IF(
LEN( Activation_Number__c ) = 12,
DATE(
VALUE(MID(Activation_Number__c, 1, 4)),
VALUE(MID(Activation_Number__c, 5, 2)),
VALUE(MID(Activation_Number__c, 7, 2))
),
NULL
)

Here is a detailed explanation of the formula:

First, use the IF function that checks whether the length of the “Activation_Number__c” field is 12 characters or not.

If the length of the “Activation_Number__c” is not equal to 12 characters, the formula returns “Null”.

If the length of the “Activation_Number__c” is equal to 12 characters, the formula works as follows:

• We first use the MID function that extracts the characters of the activation number with a starting position of 1 and the number of characters as 4. These characters represent the year of the activation date.
• Then, second use the MID function that extracts the characters of the activation number with a starting position of 5 and the number of characters till 2. These characters represent the month of the activation date.
• After that, again we use the MID function that extracts the characters of the activation number with a starting position of 7 and the number of characters to 2. These characters represent the day of the activation date.

Next, we use the VALUE function to change the extracted substring to the numerical values. Finally, we use the DATE function with all the values to combine it as the date.

• After that select the “Check Syntax” button located under the formula box to verify if there are any errors in your formula. If your formula is error-free, you will receive a message indicating “No Syntax errors in merge fields or functions”. However, if an error is there, you will get the error message.
• If you want, you can also enter the “Description” and “Help Text” fields and manage how blank fields are handled.
• After that, click on the “Next” button to move to the next step.

Step-:5 Field-Level Security Setup

• In this step, you must select the profiles for which you want to provide field-level security access to edit the fields. If you don’t select any profiles, the field will be for the particular profile for editing. But I want to enable the edit access of the field for all the profiles, therefore I have marked the checkbox labeled “Visible”.
• To move to the next step, click on the “Next” button.

Step-6: Add to Page Layout

• In this step, choose the page layout in which you want to add the newly created custom formula field. By default, all page layouts are usually selected. If you wish not to want the field to specific page layouts, simply uncheck them, and then click the “Save” button.

Step-7: Testing

After successfully creating the formula field, you can begin using it. Let’s see an example:

• Open the “Product” object and create a new object, enter the value in the “Activation Number” field, and save the product.
• Once the product is saved, navigate to the product details page. Here, you will observe the “Activation Date” field displaying the result generated by the MID function.

Let’s clarify this with an example:

• Suppose you create a product with the activation number set as “20230920-123” and save it. In this scenario, the formula will efficiently extract the date from the product activation number, resulting in the “Activation Date” field displaying “9/20/2023” as the output.

With this, we have learned how to utilize the MID function in Salesforce Classic to extract specific characters from the string text with the help of an example.

Conclusion

In summary, we have now gained knowledge about the Salesforce MID function, including its syntax and practical uses. Furthermore, we have explored the step-by-step process of extracting substring in the formula field, both in Salesforce Lightning and Salesforce Classic with examples.

You may like to read the following articles:

• Salesforce REVERSE Function | How to reverse string in Salesforce
• LEN Function in Salesforce | How to calculate length of a text string in Salesforce