# Know the Rept function in Excel

12
10402

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.

previous articleSimplifying the TEXT, CONCATENATE, and Excel Function
Next articleHow To Make Your Career Planning
Administrator and turismologist, passionate about the possibilities that Excel gives to managers, entrepreneurs and students. He is the managing partner of the spreadsheet department at LUZ - Spreadsheets (luz.vc) and is responsible for the development of spreadsheets with business and personal focus commercialized in its website. He also teaches online Excel courses (cursos.luz.vc) and likes to help thousands of planners through the LUZ Forum and Blog.

1. That way you want, I can only think via VBA itself, doing the counting every time a new name is added. A much simpler way I thought you were to create a spreadsheet in the column next to the names using CONT.SE. So whenever a new name is added it will be counted and you will know for each name the amount of repetitions

2. Hello, really good page. congratulations. Can I ask something about how to do it in excel?

I have a spreadsheet with several columns. One of these columns are names of people who repeat themselves several times and may be new names but they will repeat themselves as I add more. I would like to count how many times each name is repeated and keep counting as you put more names. but also that the name automatically appears when it is new. all in a new spreadsheet. ex..
WORKSHEET A
-Column-
Fulano
cyclane
pedro
Miguel
guy
John
guy
cyclane
Fulano

WORKSHEET B
In the other worksheet (created) I wish it were so
-Column a- -Coluna B-
Fulano 4
cyclane 2
pedro 1
Miguel 1
joao 1

If you add a different name in worksheet A - Automatic appears in worksheet B
And if you add a name already in the WORKSHEET A it does the count in WORKSHEET B.
remembering that the column in worksheet A is "infinite" (I'll always be adding names)

Thank you.
Thank you.

3. Hello Douglas, I'm going to take a look at your channel, yes, thanks!

4. in fact I think the order used was wrong. Try to use the concatenate before, in the CONCATENATE style (REPT ... 2011

5. Hello Rafael I tried, but I could not, I tried to join the two functions but the result was another

follows the way I did
= REPT ("g"; CONCATENATE ("2011"))

6. Cool, I'll try, thank you Rafael !!!

From: Disqus [mailto:[Email protected]]
Sent on: Wednesday, 20 2016 10 July: 26
To: [Email protected]
Subject: Re: Comment on Rept function in Excel

"In this case you can use the CONCATENATE function with the REPT function to have the part of the bars transformed and some more text at the end. In the case of the image, the author of the spreadsheet placed the year in question "
[Disqus]