Bookmark

“REPT function” will return a text value that has a specific string repeated X number of times.

Usage

It follows a simple pattern.

  1. String to repeat
  2. 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?

  1. We have the “AWESOME-” string that doesn’t change.
  2. 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.
  3. 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:

  1. 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.
  2. If the number of repetitions is 0, don’t use REPT. It complicates the formula with no added value.
  3. 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

Manuel Gomes

I'm a previous Project Manager, and Developer now focused on delivering quality articles and projects here on the site. I've worked in the past for companies like Bayer, Sybase (now SAP), and Pestana Hotel Group and using that knowledge to help you automate your daily tasks

View all posts by Manuel Gomes →

Leave a Reply

Your email address will not be published.