SharePoint Calculated Column CONCATENATE: Combine Text, Dates and Numbers

An HR team I worked with exported their employee list every week and spent twenty minutes building “Last, First” names and mailing addresses in Excel before a mail merge. A few calculated columns in the SharePoint list did the joining for them, and the export was ready to use as soon as it downloaded.

In this tutorial, I’ll show you how to combine columns in a SharePoint calculated column with CONCATENATE or the & operator: names, text with dates, numbers and currency, and full addresses. I’ll also explain why combining text with the item ID doesn’t work the way you’d hope.

Quick answer: Create a Calculated column with a formula such as =CONCATENATE([First Name]," ",[Last Name]) or =[First Name]&" "&[Last Name], and set the return type to Single line of text. To include a date, wrap it in TEXT, for example TEXT([End Date],"mm/dd/yyyy"); otherwise, the date appears as a number such as 46096.

How CONCATENATE works

=CONCATENATE(text1, text2, ...)

It joins up to 30 items, which can be column references in brackets, text in quotes, or numbers, into one text value. The & operator does the same thing without a limit on the number of pieces, and it’s the style Microsoft uses in its own examples:

Formula	Result for Carlos Carvallo
=[First Name]&" "&[Last Name]	Carlos Carvallo
=[Last Name]&", "&[First Name]	Carvallo, Carlos
=CONCATENATE([Last Name],",",[First Name])	Carvallo,Carlos

Always choose Single line of text as the return type, because the result is text.

Example 1: Combine first and last names

This Employee Details list has separate name columns, and a calculated Employee Full Name column joins them:

In the list, select + Add column > See all column types > Next, name the column and choose Calculated (calculation based on other columns):

Enter the formula, with a space in quotes between the names:

=CONCATENATE([Employee Name]," ",[Employee Surname])

If some people have an optional middle name, skip it when it’s empty so you don’t get double spaces: =[First Name]&" "&IF(ISBLANK([Middle Name]),"",[Middle Name]&" ")&[Last Name].

Example 2: Combine text and a date

This Project Timeline list has a title and start and end dates, and a calculated column shows the title with its deadline:

=CONCATENATE([Title]," - ",TEXT([Project End Date],"mm/dd/yyyy"))

The return type must be Single line of text, even though a date is involved, because the result is text. “Website Launch” with a 5/1/2026 end date becomes “Website Launch – 05/01/2026”:

Why the date shows as a number

If you leave out TEXT, SharePoint joins the date’s underlying serial number, the number of days since 1900. That’s why this column shows a five-digit number instead of a date:

Other formats you can use in TEXT: "mmmm d, yyyy" for March 15, 2026, "mmm d" for Mar 15, and "mm/dd/yyyy h:mm AM/PM" to include the time.

Example 3: Combine text and numbers or currency

Numbers are joined as plain digits, so format them with TEXT too:

Formula	Result
=[Title]&" costs "&TEXT([Price],"$#,##0.00")	Laptop costs $1,250.00
=[Rep]&" sold "&TEXT([Share],"0%")&" of the total"	Megan sold 40% of the total
="Due: "&TEXT([Due Date],"mmmm d, yyyy")	Due: October 15, 2026

Example 4: Build a full address

For a mailing address, join the address columns with commas and spaces, in US order:

=[Street]&", "&[City]&", "&[State]&" "&[ZIP Code]

For “100 Congress Ave”, “Austin”, “TX” and “78701”, that gives 100 Congress Ave, Austin, TX 78701. Store ZIP codes in a single line of text column; a Number column drops the leading zero from codes like 02108.

Why you can’t combine text with the ID

A common request is a code like PROJ-1, PROJ-2 from the item ID, with ="PROJ-"&[ID]. It seems to work at first, but new items show PROJ-0 or a blank, because the ID doesn’t exist yet when SharePoint calculates the column, and Microsoft doesn’t support referencing it.

Two better options: use JSON column formatting to display “PROJ-” plus the ID, or a Power Automate flow that fills in a text column after the item is created. See SharePoint JSON column formatting for the display approach.

Rules to keep in mind

• CONCATENATE takes up to 30 items; & has no such limit.
• You can reference single line of text, number, currency, date and time, choice, yes/no and calculated columns. Person, lookup, multiple lines of text and managed metadata columns can’t be used, so you can’t join a person’s name from a Person column.
• A formula can only use values from the same item.
• Text in quotes can be up to 255 characters, and must use straight quotes.
• Sites whose language uses commas for decimals separate arguments with semicolons.

These SharePoint calculated column tutorials go further:

• SharePoint List Calculated Column IF Statement [7 Examples]
• SharePoint Calculated Column Date Examples
• SharePoint Online Site Date Format
• How to Format a SharePoint Online List Column using JSON (Step‑by‑Step Guide)
• How to Add SharePoint Column Formatting with JSON?
• How to Validate Dates in SharePoint to Choose WeekEnds?(Saturdays & Sundays)

Frequently Asked Questions