Know the Rept function in Excel

12
10402
Know the REPT function in Excel
Excel Spreadsheets

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.

Know the REPT function in Excel

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:

Know the REPT function in Excel

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.

Know the REPT function in Excel

If it were a common source the result would appear as follows:

Know the REPT function in Excel

This is the simplest way to build a chart in Excel, and based on the function several different types can be created.

Know the REPT function in Excel

Know the Rept function in Excel

Know the Rept function in Excel

Know the Rept function in Excel

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:

Know the Rept function in Excel

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!

Excel Spreadsheets

12 COMMENTS

  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]

    [http://a.disquscdn.com/1468276017/images/email/icon-gear.gif] Settings [http://a.disquscdn.com/uploads/users/15738/1916/avatar92.jpg?1468948632]

    A new comment was posted on Blog LUZ.vc | Spreadsheets and Excel

  7. 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 spreadsheet author has placed the year in question

  8. This example has the year ahead, is it possible to put the year too?

LEAVE AN ANSWER

Please, write your comment
Please enter your name