Excel is one of the most comprehensive tools for monitoring and analyzing data. To facilitate the work of those who use it, many of the functions and formulas available in the program exist precisely to automate some moment of the process. One of these functions is REPT, and it is precisely this function that we will unravel in this post, showing how it works and giving some examples of how it can be used in everyday life.
What is the REPT function in Excel
Basically what the REPT function does is very simple: it repeats a text a certain number of times. Simple, right? To make the thing more visual, the syntax of the formula is as follows:
REPT (text, number of times)
Translating the meaning of each of the terms:
1 - text: Required and represents the text you want to repeat
2 - number_times: Also required, and represents a positive number that determines the number of times you want to repeat the text.
Equally important points to emphasize about the syntax of the formula and its completeness:
1 - If num_times is zero, REPT returns empty.
2 - If num_times is not an integer, it will be truncated.
3 - The result of the REPT function can not be greater than 32.767 characters; otherwise REPT will return #VALUE! ..
To make it easier to understand the function, its syntax and function, see the example below.
1 Example: Creating Sparklines
Excel by nature offers a function that creates sparklines, but if you want to create graphics in a faster and more practical way, you can do this with the REPT function. Let's go step by step?
For this example, consider the following table:
Our goal here is to place next to each of the lines a bar chart that visually represents each of the values in the table. For this we will represent with a slash, using the font Webdings symbols.
You can use any source of your choice and any character to be shown and repeated. In the example we use the letter g, which due to the chosen font gave the impression of a bar.
If it were a common source the result would appear as follows:
This is the simplest way to build a chart in Excel, and based on the function several different types can be created.
Of course, due to the technical limitations of the tool, a very sharp visual result can not be expected since this is not the objective of this program. Regardless of this, the function helps to solve some small problems related to data presentation.
2 Example: Creating Fixed Size Fields
Another activity that can be done using the REPT function is to create fixed-length fields. Let's say that a given column needs only numbers with 10 characters, and if one of them has less than 10 the remaining space must be filled by 0, like the image below:
For this we use together the NUM.CARACT function to know how many characters have already been used in the first column. With this we only subtract from the total of 10 characters the quantity that was used, determined by the NUM.CARACT. If your Excel is in English the corresponding function is LEN.
In the example, the A2 cell used two characters (1 and 5). Then with the formula we subtract 2 from 10, leaving 8 spaces to be occupied by the 0 character, which is indicated in the formula. The & A2 at the end indicates that in addition to the 8 zeros, you must also include the value corresponding to the A2 cell.
Was this article helpful to you? Follow our publications with Excel tips!