How to use REPT Function in Microsoft Excel

0
220

This article demonstrate the use of Excel REPT Function, its’ syntax and arguments with the live examples.

REPT Function

The Excel REPT function is one of less used function yet very useful. The function repeats certain character a specified number of times and can be combined with other Excel functions to do wonders.

The function can be used with other Excel functions to create Inline Charts, adding zeros at beginning, extracting words from text string etc.

Syntax:

REPT ( text , number_times )

Arguments:

  • text – The argument specifies text to repeat.
  • number_times – To repeat the text argument specific number of times.

Examples: – REPT Function

By now you must be clear with the syntax, argument and use of REPT function. So let’s have a look at the live examples to know more about this function.

Repeat a text multiple times?

Certain time you may require specific character or multiple characters to be repeated several times. Rather than thinking of doing manually, REPT function can be used to do the task in seconds only.

In the following image, the first column (column A) contains character that has to be given number of times in column B.

formula to repeat

Since it is very easy to apply this function, jump on to cell where you want the result to appear and write function followed by required arguments as follow:

=REPT ( A5 , B5 )

The REPT function entered in cell C5 can be seen repeating the character ‘n’ 9 times in cell.

Tip: A cell can maximum store 32,767 characters in it. So make sure the repeated text is not exceeding the limit of maximum characters.

You can further drag the function down to repeat rest of the cell in range by given number of times.

Create In-cell Bar chart

One of the interesting use you can see of REPT function to create in-cell bar chart. The trick is very simple and impressive. The In-cell bar can be really good to measure productivity at times.

In the following screenshot, agents’ names are given in column A and number of Sales in column B. Although we have small data, but reading numbers can be difficult certain times. The Chart option is considered as the best alternative and in this case with simple REPT function, you can build a good looking in-cell bar chart as shown below and measure the performance of each agent.

Let’s understand how this function is working and returning in-cell bar chart here.

  • The function is supplied in cell C5 with having vertical slash ( “|” ) in the first argument and B5 in second argument which holds the number of sales made.

=REPT ( “|”, B5)

  • Once function is applied, go back to cell C5 and change the font style to ‘Nyala’
  • Copy the function and drag it to last active cell

The above steps would convert the repeated vertical slashes ( “|” ) in In-cell bar chart. However, the In-cell bar chart would be in the default text font color (most likely gray). You can change the color of the same by going on to Font Color option.

Tip: – You can also use Fonts ‘Aharoni’, ‘David’, ‘Haettenschweiler’, ‘Playbill’, ‘Stencil’ ‘Shonar Bangla’, etc. to compress vertical slashes and give a shape of In-cell bar chart.

Important Notes: –

  • Use REPT with LEN function to add leading zeros in numbers.
  • A maximum of 32,767 characters can be repeated and stored in any cell.
  • Function returns #VALUE! error as soon as the cell exceeds 32,767 character
  • REPT function can also be used to create ‘Mirror Chart’
  • REPT along SUBSTITUTE function can be used together to extract last word or name

Related Articles

CLEAN | TRIM | UPPER | LOWER | PROPER

LEAVE A REPLY

Please enter your comment!
Please enter your name here