Sooner or later you need to glue a few pieces of data together into one tidy value. A product code built from a couple of columns, a full name from a first and last, a label that mixes text with a number. That is exactly what the "CONCATENATE" function does in a SharePoint calculated column.
I like the "CONCATENATE" function because it is not fussy about what you feed it. You can mix text, numbers, and dates without converting everything to a string first, and that makes it a great little helper for building codes and readable labels. Let's take a look.
Where to find it?
Formulas like this live inside a calculated column. Go to your list settings, choose to create a new column, and pick the "Calculated (calculation based on other columns)" type. That gives you a formula box, and the function goes right in there alongside any columns you reference in square brackets.
SharePoint also lets you join values with the "&" operator, so =[First]&" "&[Last] does the same job as =CONCATENATE([First]," ",[Last]). They behave the same way. I reach for the function when there are several discrete pieces to join, and for the "&" when it is just two or three.
Usage
It follows a simple pattern.
- The first value to join (a string in quotes, a number, or a column in square brackets)
- The next value, separated by a comma, and so on
| Parameter | Required | Type | Description |
|---|---|---|---|
| text1 | Yes | Text, number, date, or column | The first value to join |
| text2... | No | Text, number, date, or column | Up to 30 values in total |
The examples here use commas to separate arguments. In regions where the comma is the decimal mark, SharePoint expects semicolons instead (=CONCATENATE([First];" ";[Last])). SharePoint translates the separator automatically for readers in other regions, so a formula authored one way still works everywhere.
The simplest case joins two strings together.
=CONCATENATE("ABC","DEF")
will return
"ABCDEF"
Numbers come along without any conversion on your part.
Number
28
=CONCATENATE([Seller]," sold ",[Units]," units.")
will return
"Yang sold 28 units."
I especially like this because you can build codes that mix functions and arithmetic. Here is the formula I reach for to create padded codes, using REPT to add leading zeros and LEN to measure the number.
=CONCATENATE(REPT("0",4-LEN([Int Number])),[Int Number])
If we want to build the code 0103 from the number 103, it works as follows:
- Generate the leading zeros. We take four (the length we want) minus the length of the number.
REPT("0",4-LEN([Int Number]))
with [Int Number] = 103 the result is
0
- Then we join the number itself.
0 concatenated with 103
0103
I give this example all the time because it shows how much you can do inside the function. We're nesting formulas (with "REPT" and "LEN"), reading from a Number column, and the result still comes out as a clean string.
This padding trick assumes the number stays the same length. A value like 9 gives a three-zero pad ("0009"), but once it rolls over to a longer number the math behind the pad changes, so test it across the full range of numbers you expect. Quote the zero as REPT("0",...) rather than REPT(0,...) so SharePoint treats it as text.
Real-world examples
A readable full name
Joana stores first and last names in separate columns but wants one display column for views and exports.
=CONCATENATE([Last],", ",[First])
will return
"Carvalho, Joana"
A formatted statement label
A date joined directly comes out as a serial number, so we wrap it in "TEXT" to keep it human.
=CONCATENATE("Statement date: ",TEXT([Date],"d-mmm-yyyy"))
will return
"Statement date: 5-Jun-2026"
Non-intuitive behaviors
Dates and percentages turn into numbers
If you concatenate a date or a percentage straight away, SharePoint joins the underlying value, which is a serial number for dates and a decimal for percentages. Wrap the value in the "TEXT" function with a format string to get what you expect.
=CONCATENATE("Due ",[DueDate]) gives you something like "Due 46178". Use =CONCATENATE("Due ",TEXT([DueDate],"d-mmm-yyyy")) instead.
Blank values leave gaps
Blank columns join as empty strings, so a missing middle value can leave doubled separators like "Smith, , John". Guard the gaps with the IF function when a value might be empty.
The argument separator follows the region
As noted above, the separator is a comma or a semicolon depending on the regional settings when the column was created. SharePoint converts it for other regions automatically, so do not panic if a shared formula shows semicolons.
Limitations
Up to 30 values
The function accepts 1 to 30 text items. That is the documented ceiling it inherits from Excel, and it is plenty for almost any list.
A 255 character limit and 8 levels of nesting
Calculated columns cap string constants at 255 characters and allow up to 8 levels of nested functions. Long concatenations can quietly run into the character limit, so keep an eye on the total length.
Only some column types can be referenced
You can reference single line of text, number, currency, date and time, choice, yes/no, and other calculated columns. You cannot reference multiple lines of text, Person or Group, Lookup, Managed Metadata, hyperlink, or multi value choice columns. If the column you want is not selectable, this is usually why.
No "CONCAT" or "TEXTJOIN" here
Excel moved on to "CONCAT" and "TEXTJOIN", but SharePoint calculated columns support only a fixed set of functions, and those two are not in it. In SharePoint, "CONCATENATE" is not deprecated, it is still the function to use.
Current row only
A formula can only see the current row. It cannot reference other rows, other lists, aggregate values, or the ID of a brand new item (the ID does not exist yet when the formula runs).
Recommendations
Here are some things to keep in mind.
Prefer the function for readability
Although the "&" operator does the same job, I prefer the function when there are several pieces to join. It reads more clearly and makes your intent obvious, even if it is a touch longer.
Keep the list of values manageable
SharePoint allows up to 30 values, but I would stay well below that. If you find yourself joining a dozen things, split the work into hidden calculated columns. Each one is easier to read, easier to debug, and easier to change later.
Do not go overboard with nesting
I recommend never going more than two levels deep. If you have a complex expression, build it up in additional hidden columns, check that each one returns the right result, and then concatenate those. It makes the whole thing far more readable.
Always deal with errors and blanks
SharePoint often will not stop you from saving a broken formula. It saves fine and then shows "#NUM!" or "#VALUE!" in the rows with bad data, and the error cascades into every other column that builds on this one. Wrap risky inputs with IF or ISBLANK so a missing value does not break the whole column.
Final Thoughts
The "CONCATENATE" function is one of those small tools that quietly does a lot, joining text, numbers, and dates into clean values for codes and labels. Remember to wrap dates in "TEXT", watch out for column types it cannot read, and keep your formulas short enough to debug. Do that, and it will serve you well.
Sources
- CONCATENATE function (Microsoft Support)
- Introduction to SharePoint formulas and functions
- Calculated Field Formulas (Microsoft Learn)
- Examples of common formulas in lists
Back to the SharePoint List Function Reference
Photo by Martin Sanchez on Unsplash
No comments yet
Be the first to share your thoughts on this article!