SharePoint: CONCATENATE Function

“CONCATENATE function” will group a series of values into a unique string. I like the concatenate function since you can use more than strings.

Usage

It follows a simple pattern.

Value to append separated by comma (please check your regional settings because this may change from country to country)

Example:

=REPT("ABC","DEF")
The result will be
"ABCDEF"

I especially like this formula because you can use it with other data types without converting them. Let’s look at the formula that we use to create codes.

=CONCATENATE(REPT(0,4-LEN([Int Number])),[Int Number]+1)

You can see more details regarding REPT and LEN in their sections.

If we want to build the number 0103, the above will work as follows:

Generate the left zeros. We have to get the length of the number minus four since we want a four-character code.

REPT(0,4-LEN([Int Number]))
The result would be:
0

Then we would add the number + 1

[Int Number]+1
The result would be:
102+1=103

The result would be:

0 concatenated with 103
0103

I give this example all the time because it demonstrates that we can do a lot inside the function. We’re nesting formulas (by using LEN), we’re using Int columns (Int Number) and doing arithmetic (by adding 1 to the number).

Limitations

You have the limitation of 30 items. If you have to concatenate so many pieces, I recommend doing them in batches; otherwise, it would be impossible to debug.

Recommendations:

Although Microsoft enables you to use the “&” to do the same, I recommend using the function. It will make your code more readable even if it’s a little bit longer.
Don’t concatenate more than 15 items. If you have to do so many things, split them into hidden columns to enable for better traceability and debug in case something doesn’t show up as you would expect. Also, in case of changes, you can change each hidden column with sub formulas instead of the whole formula.
Don’t go overboard with nested formulas. I recommend never going below two levels. If you have complex expressions, create additional hidden columns, debug them and check if the result is the correct one and then concatenate the result. It will make your job a lot easier and make things much more readable.