How to Split a String using SharePoint Calculated Column?

Recently, I worked on a SharePoint project where I needed to split and replace the text string values present in a SharePoint list column. We can achieve this using a SharePoint calculated column.

This SharePoint tutorial explains how to split strings using a SharePoint calculated column with different text functions, such as:

• LEFT() Function
• MID() Function
• RIGHT() Function

I will also show you how to use the LEFT(), MID(), and RIGHT() functions to remove characters from text using the SharePoint calculated column.

I will also explain here how to extract characters from Text in a SharePoint calculated column.

SharePoint Calculated Column Split String

Let’s see how to split a string in the SharePoint calculated column using the REPLACE and FIND functions.

Below, you can see the Employee Details list, which includes the Employee ID column containing values like [PTG-1324]. I want to split the string at the hyphen (-) and replace it with a comma followed by a space (, ).

This can be done by using the REPLACE and FIND functions in a SharePoint calculated column.

To achieve it, follow the steps below:

1. Open a SharePoint Online list, then click on the + Add column, and click on the See all column types -> Click on Next.
2. Inside this Create column tab, enter the Column name and choose the type of information in this column: Calculated (calculation based on other columns).

3. Then enter the formula by choosing the column under Additional Columns Settings, select the data type returned from this column to “Single line of text”, and click OK as shown below.

=REPLACE([Employee ID],FIND("-",[Employee ID]),1," , ")

4. Now, you can see that the calculated column has been created and displays the output. Take a look at the image below.

This is how to split a string using a SharePoint Online calculated column.

Check out Create a Hyperlink using SharePoint Calculated Column

Split a String using SharePoint Calculated Column with the LEFT Function

Suppose you have a SharePoint list named ‘Employee Directory’ with different columns, and you want to split the ‘First Name’ value from the ‘Employee Full Name’.

This can be done by using a calculated column in SharePoint with the help of the LEFT() Function.

To achieve this, follow the steps below.

1. Open your SharePoint list, click + Add column -> Select See all column types, then click Next.

Then, it will navigate to the Create Column page, where you can add different types of columns to the SharePoint list.

2. On the Create column page, enter the column name and select the type of information in this column as Calculated (calculation based on other columns).

3. Enter the formula in the formula box by choosing a column from the Insert column. Choose the data type returned from this column to Single line of text, then click OK.

The formula is presented in the code below:

=LEFT([Employee Full Name],FIND(" ",[Employee Full Name])-1)

The calculated column is created at this time.

4. Now, return to your SharePoint list, where you can view the newly created calculated column, [First Name].

Note:

You can also get the same output by using this formula: =TRIM(MID([Employee Full Name],1,FIND(“, “,[Employee Full Name],1)-1)

This way, you can split a string by using the LEFT() function in a calculated column.

Check out SharePoint List Calculated Column IF Statement

Split String in SharePoint Calculated Column using MID() Function

Let’s say I have an ‘Employee Directory’ list, where it has Employee ID in a specific format like EMP-1324-SP. I need to split the unique ID numbers from the Employee ID column.

To do this, I will create a calculated column in the SharePoint list by using the MID function.

Now, let’s start with the following step:

1. Now, enter the expression in the Formula box under Additional Columns Settings. Select the data type returned from this column as Single line of text, then click OK.

The formula is listed below:

=MID([Employee ID],FIND("-",[Employee ID])+1,FIND("-",[Employee ID],FIND("-",[Employee ID])+1)-FIND("-",[Employee ID])-1)

Output:

This is how to split a string in a SharePoint calculated column using the MID function.

Read SharePoint List Calculated Column Concatenate() Function

SharePoint Calculated Column: Split String using RIGHT() Function

Here, I will demonstrate how to split a string using a calculated column in SharePoint using the RIGHT() function.

I will replicate the above example by using the RIGHT() function in a calculated column.

Follow the steps below:

1. Go to the Create column page in SharePoint, and provide the Column name, then select the Calculated column type.
2. Under the Additional column settings, enter the formula in the Formula box.

=RIGHT(LEFT([Employee ID], FIND("-", [Employee ID], FIND("-", [Employee ID]) + 1) - 1), LEN(LEFT([Employee ID], FIND("-", [Employee ID], FIND("-", [Employee ID]) + 1) - 1)) - FIND("-", [Employee ID]))

Ensure that you select the data type returned from this column as ‘Single line of text’, then click OK.

Output:

Have a look at the reference image below, which splits the string using a calculated column in SharePoint.

You can use the LEFT(), MID(), and RIGHT () functions in a SharePoint calculated column to split the strings.

Remove Characters from Text using a SharePoint Calculated Column

Now, let me show you how to remove characters from text in the SharePoint calculated column, especially using the LEFT(), RIGHT(), and MID() functions.

Using the RIGHT() function

You can remove characters from a text in a SharePoint calculated column using the RIGHT() function.

In the RIGHT() function, the characters from a text are removed from left to right. To achieve it, follow the steps below.

• Open a SharePoint Online list, click on the + Add column, and select See all column types -> Click on Next. Then enter the Column name and select Calculated (calculation based on other columns).
• Insert the formula to remove characters in the SharePoint calculated column in the Formula box, then click OK.

Syntax to remove characters from a text in a SharePoint calculated column using the RIGHT() function:

=RIGHT([Column1], LEN([Column1]))-5)

The formula used to remove characters from a text in a SharePoint calculated column using the RIGHT function. Here, I have used the Title column.

=RIGHT([Title], LEN([Title]))-5)

• The screenshot below shows the output where characters are removed from left to right direction.

This is how to remove characters in a text in a SharePoint calculated column using the RIGHT function.

Using the LEFT() function

Let’s see how to remove characters from a text in a SharePoint Online calculated column using the LEFT() function.

Here, the characters from a text are removed from right to left. To do it, follow the steps below.

• Go to the Edit column tab using the steps of the previous section, then insert the formula in the Formula box.

The syntax used to remove characters from a text in a SharePoint calculated column using the LEFT() function is listed below.

=LEFT([Column1], LEN([Column1])-8)

The formula used to remove 10 characters from a text in a SharePoint calculated column using the LEFT function is given below. Here, I have replaced Column 1 with the Title as needed.

=LEFT([Title], LEN([Title])-10)

• Then click OK, and now you can see the output as I have removed the 10 characters from the left side of the text in the SharePoint calculated column below.

This is how to remove characters in a text in a SharePoint Online calculated column using the LEFT function.

Using the MID() function

I will show you how to remove characters in a text in the SharePoint calculated column using the MID function.

• Go to the Edit column tab using the steps of the previous section, then enter the formula in the Formula box. I have changed the number as needed. Click the OK button.

The syntax used to remove characters from a text in a SharePoint calculated column using the MID() function is listed below.

=MID([Column 1], 4, LEN([Column 1])-7)

The formula used to remove characters from a text in a SharePoint calculated column using the MID function is listed below:

=MID([Title], 4, LEN([Title])-11)

• This is how SharePoint calculated column removes characters from a text using the MID function.

This is how to remove characters from a text in a SharePoint calculated column using the LEFT(), RIGHT(), and MID() functions.

Extract Characters from Text in SharePoint Calculated Column

While working on a client’s requirement, I was assigned a task to extract characters from a text string in SharePoint.

SharePoint supports text functions, such as LEFT(), RIGHT(), and MID() functions, to retrieve string characters using calculated columns.

I will provide examples of extracting characters from text in SharePoint calculated columns using the text functions listed below.

• Extract First Characters in String – LEFT() Function
• Extract Last Characters in String – RIGHT() Function
• Extract Specific Characters in String – MID() Function

Extract First Characters in Text SharePoint Calculated Column using the LEFT() Function

This example will show you how to extract characters from the beginning of a text string using a calculated column in SharePoint.

Suppose I will take a SharePoint list “Project Assignment” where it has Project name and Project code columns. The Project Code is in the format of “PRJ-9845-US“. I want to extract the first three characters from the Project Code, i.e, PRJ.

To do this, you can use the LEFT() function in a SharePoint calculated column.

Follow the steps below.

1. On a SharePoint Online list, click on the + Add column, and select See all column types -> Click on Next.

2. On the Create Column page, enter the Column name and select Calculated (calculation based on other columns).

3. Under the Additional column settings, enter the formula provided in the Formula box.

=LEFT([Project Code],3)

Ensure that you select the data type returned from the formula as “Single line of text” and click the OK button at the bottom of the page.

At this point, the calculated column has been created. Go back to your SharePoint list.

4. Now you can see the output of the extracted characters in a calculated column [Project Prefix].

This is a way you can use the LEFT function to extract characters from a string using a SharePoint calculated column.

Extract Last Characters in String in SharePoint Calculated Column using the RIGHT() Function

Here, let’s see how to extract end characters from a string using a calculated column in SharePoint.

I will consider a SharePoint list named ‘Project Assignment, which has a ‘Project Code‘ column that consists of different suffixes that represent the country code. I wanted to retrieve the last two characters from the Project Code.

This can be easily achieved by using the RIGHT() function in SharePoint.

Follow the steps below:

1. Insert the following formula in the Formula box under Additional column settings, then click OK.

=RIGHT([Project Code],2)

Output:

2. The screenshot below shows the output where the characters have been extracted from the end of a string.

This is how to extract characters in a text in a SharePoint calculated column using the RIGHT function.

Extract Specific Characters in String using the MID() function in a SharePoint Calculated Column

I will illustrate this example, I’m using the same SharePoint lists mentioned in the above examples.

In the SharePoint list [Project Assignment], I have a column named Project Code. I want to extract only the project number part. Assume the Project Code string as follows: “
PRJ-9845-US.” I wanted to extract specific characters, ie,[9845].

To achieve this, we can use the MID() function in a SharePoint calculated column.

Do follow the steps below to know how to extract specific characters from a string in SharePoint:

1. Enter the following expression in the Formula box. Then, select the data type returned from that formula as “Number”. Then, click OK.

=MID([Project Code], 5, LEN([Project Code])-7)

The calculated column will be created. Navigate to your SharePoint list and check out the output.

Output:

This is how to use a SharePoint calculated column to extract characters from a text using the MID function.

You can use the LEFT(), RIGHT (), and MID() functions to extract characters from a text string in SharePoint calculated columns.

Conclusion

Here, I have explained how to use the LEFT(), RIGHT(), and MID() functions in a SharePoint calculated column to split a string or remove characters from a text. I have used these text functions in many SharePoint projects. Do let me know if you still have any questions in the comments below.

You may also like the following tutorials:

• LEN() Function in SharePoint Calculated Column
• SharePoint Calculated Column Round Up to Decimal Places
• SharePoint Calculated Column Date Examples
• Extract Email from SharePoint Person or Group Field using JSON

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.