by “REPT function” will return a text value that has a specific string repeated X number of times.
Usage
It follows a simple pattern.
- String to repeat
- The number of repetitions
Example:
=REPT("ABC",3)
The result will be
"ABCABCABC"
It doesn’t look handy but let’s think about a more practical case.
Let’s say that you want to have a project code with always the same size. For example, “AWESOME-0001”. It the number is higher than ten you want it to be “AWESOME-0011” and so forth. The formula to achieve this is:
="AWESOME-" & REPT("0", 4 - Len(<sequential number>)) & <sequential number>
Makes sense?
- We have the “AWESOME-” string that doesn’t change.
- Then we want to replicate zeros to fill in the empty spaces so it should be four minus the size of the number. If it’s one we want three zeros, for example, but if it’s 303 we want only one zero added.
- Finally, we add the sequential number
Limitations
The result cannot be longer than 2000 characters. If this happens, the function will not truncate but return an error message. Take this into consideration when building your formula to avoid errors shown to the end-user.
Recommendations:
- Always use an integer value. You can use a decimal value, but it will be truncated, meaning that, for example, “4.2” or “4.9” will always replicate four times.
- If the number of repetitions is 0, don’t use REPT. It complicates the formula with no added value.
- Be also sure that the number is positive. If it’s negative SharePoint will allow the formula to compile but return an error.
Sources:
Microsoft’s REPT Function Reference
Introduction to SharePoint formulas and functions
Back to the SharePoint’s list formula reference.
Featured Image by Francisco Arnela on Unsplash
