You can use the concatenate() function to combine two or more text strings or characters in a SharePoint calculated column. This is useful when you want to join two list/library columns in SharePoint.
Syntax:
=CONCATENATE(text1,text2,...)Parameters:
- Include text, characters, or numbers, or column references to join.
Output Returned:
- Returns one text string by joining all the strings provided.
Additional Notes:
- We can add up to 30 text items inside a single Concatenate()function of a SharePoint Online list calculated column.
- The “&” operator can be used in a SharePoint calculated column instead of the CONCATENATE() function to join text items.
SharePoint Calculated Column Concatenate() Examples
Let’s see some examples of the concatenate() function in a SharePoint calculated column.
Example 1: Concatenate Two Fields in SharePoint Calculated Column
Below, I have a SharePoint list ” Employee Details”, which has different columns, and I wanted to concatenate “Employee Surname & Employee Name” fields in a calculated column.
| Column Name | Column Type |
|---|---|
| Employee Surname | Single line of text |
| Employee Name | Single line of text |
| Joining Date | Date & Time column |
You can use the following formula for it:
=CONCATENATE([Employee Name]," ",[Employee Surname])Here are the steps to follow to create a calculated column:
- Open your SharePoint list/ library, where you want to add a calculated column. In my case, I was taking a SharePoint list.
- Next, click on +Add Columns -> Select See all column types. Then, click on the Next button.
- It will navigate to the Settings page of Create Column, where you can create a column according to your preference.
- On the Create Column page, enter a name for a column and choose the type as Calculation (calculation based on other columns).
- In the Formula box, enter the following formula. Select the data type returned from this formula as ”Single line of text“. Scroll down to the bottom of the page to click on the OK button.
=CONCATENATE([Employee Name]," ",[Employee Surname])In the formula, I have joined two SharePoint list fields separated by a space. Replace your column names in the formula.
- Now, the calculated column has been created in SharePoint by combining two fields. You can have a look at the image below:
This is one example of the concatenate function() in a SharePoint calculated column. Let’s see a few more examples related to this.
Check out SharePoint Calculated Column Date Examples
Example 2: Concatenate Date and Text in SharePoint Calculated Column
In this example, let’s see how to combine a date and a text string in a calculated column of a SharePoint list.
Suppose you have a SharePoint list named “Project Timeline” with a couple of columns as shown below:
| Column Name | Column Type |
|---|---|
| Project Title | Title column – Renamed to Project Title |
| Project Start Date | Date and Time |
| Project End Date | Date and Time |
If you want to show the “Project Title and Project End Date” in one column, then you can use the concatenate() function to join them.
If you have followed Example 1, you already know the steps to create a calculated column in a SharePoint list or library. Let’s start with the following steps.
- On the Create Column page, provide a name for a column and select the type of information in this column is: Calculated (calculation based on other columns).
- Enter the following formula in the Formula box. Select the data type returned from this formula as “Date and Time”.
- Then, click the OK button at the bottom of the page.
=CONCATENATE([Title]," - ",TEXT([Project End Date],"mm/dd/yyyy"))In the formula, [Title] represents Project Title.
- After that, a calculated column will appear when you navigate back to your SharePoint list. Have a look at the reference image:
Important Thing to know:
When you concatenate the text and date fields in a calculated column, the date will be returned in the form of a token without displaying in a date format. When concatenating, you need to provide the date format as “mm/dd/yyyy”.
Formula: =CONCATENATE([Title]," ",[Project End Date])
Check out SharePoint Calculated Column Round Up to Decimal Places
Example 3: Concatenate ID + String in SharePoint Calculated Column
In this section, let’s see how to concatenate the ID column with a string in a SharePoint calculated column.
I will use the same SharePoint list that is mentioned in Example 2. Here, I wanted to create a “Project ID ” column that combines a string and the ID value of a list item [PROJ_ID_ + ID] and gives [PROJ_ID_1].
Note:
You can use
=[ID]to retrieve the ID value in a SharePoint calculated column. Because, the ID column is not present inside the “Insert Column” list box.
Follow the steps:
- On the Create Column page, create a column by providing a column name and type as Calculated(calculation based on other columns).
- Enter the mentioned formula in the Formula box. Choose the data type returned from this formula as “Single line of text“. Click on the OK button.
=CONCATENATE("PROJ_ID_",ID)
- Then, a calculated column will appear in a SharePoint Online list by displaying the Project ID.
Additional Notes on ID in SharePoint Calculated Column:
- The ID in the SharePoint calculated column won’t be updated for newly created SharePoint list items.
- For the newly added item, it will display [PROJ_ID_0]. This is because the SharePoint ID field is not calculated in the calculated column until you manually update the formula.
- The existing items in the SharePoint list will only display this unique ID created from the SharePoint calculated column.
I suggest using JSON formatting or a Power Automate flow to create unique IDs instead of relying on calculated columns in SharePoint.
Read LEN() Function in SharePoint Calculated Column
Example 4: SharePoint Calculated Column Concatenate Multiple Values
Below, you can see a SharePoint list ” Employee Details” with a couple of columns, and I want to concatenate multiple list columns’ values in a SharePoint calculated column [Permanent Address].
| Column Name | Column Type |
| Employee Full Name | Single line of text |
| City | Single line of text |
| State | Single line of text |
| Country | Single line of text |
| ZIP Code | Single line of text |
Check out the steps below:
- On a Create column page, provide a name for a column and choose the data type as Calculated (calculation based on other columns).
- In the Formula box, copy and paste the following formula. Choose the data type returned from this formula: Single line of text. Click the OK button at the bottom.
=CONCATENATE([Employee Full Name],", ",[City],", ",[State],", ",[Country]," - ",[ZIP Code])
- Now, the calculated column will appear on the SharePoint list. You can see the image below for your reference:
This is all about the concatenate() function in SharePoint calculated column.
In this article, I have shown you four examples of the concatenate() function that you can use in a SharePoint calculated column.
I hope you found this tutorial helpful. Please let me know in the comments section if you have any questions or suggestions.
You may also like the following tutorials:
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.
