WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » REPT Function in Excel

REPT Function in Excel

By Madhuri ThakurMadhuri Thakur | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel REPT Function

REPT function is also known as repeat function in excel and as the name suggests this function repeats a given data provided to it to a given number of times so this function takes two arguments one is the text which needs to be repeated and the second argument is the number of times we want the text to be repeated.

Syntax

Below is the REPT Formula in Excel.

REPT Formula in Excel

Arguments

  • text = It is a required parameter. It is the text to be repeated.
  • number_of_times = It is also a required parameter. It is the number of times the text in the parameter-1 is to be repeated. It must be a positive number.

Examples to use REPT Function in Excel

The REPT Function is a Worksheet (WS) function. As a WS function, the REPT function can be entered as a part of the formula in a cell of a worksheet. Refer to a couple of examples given below to know more.

You can download this REPT Function Excel Template here – REPT Function Excel Template

Example #1 – Repeat a set of characters

REPT Example 1

In the above example, REPT(“\n,” 3) “\n” is said to be repeated 3 times. As a result, cell B2 contains “\n\n\n\”.

Example #2 – Repeat a character

REPT Example 2

In the above example, REPT (“-,” 10) “-” is said to be repeated 10 times. As a result, cell C1 contains “———-.”

Example #3 – Equalize the length of the numbers

REPT Example 3

In the above example, column C contains a series of numbers from C3 to C7.

The number of digits each cell contains is different. So, the use case is to make all the numbers of the same length by adding zeros (0) in such a way that the value of the number doesn’t change, but all the numbers become of the same length.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Here, the length defined is 4. i.e., all the numbers should be of length 4. The formatted numbers are present in column D, starting from D3 to D7. So, the formatted number of C3 is present in D3, C4 in D4, C5 in D5, C6 in D6, C7 in D7, and so on. Each cell from D3 to D7 has a REPT Function applied to it.

Here, the REPT function is used to add leading or trailing zeros. Let’s analyze the REPT function written in the formula bar.

=REPT(“0”,4-LEN(C3))&C3

As shown in the above REPT formula in Excel, “0” represents a digit/text to be repeated, and the second parameter is the frequency with which zero is it to be repeated. Here, the length of the number in the C3 cell is deducted from number 4, which will give us the number of zeros to be repeated so as to format the number in 4-digit format.

E. g: If the length of the number present in C3 is 2, then 4-2=2, which is evaluated to – REPT (“0”,2) meaning repeat 0 two times. Further, the resultant number is ended with the original value, which here, is 12 and the result is 0012.

Example #4 – Create in-cell charts

REPT Example 4

In the above example, column E contains a series of marks obtained out of 100 from E3 to E7. The marks obtained are to be represented in a pictorial format without using the default Charts widget in Excel. This can be accomplished by using the REPT function, as explained below.

Here the REPT formula used is =REPT(CHAR(110), E3*0.05).

All characters have an ASCII code, which is understood by the computer. Small case ‘n’ has code 110. So, =CHAR (110) in Excel returns ‘n.’ When Wingdings font is applied on the same, it becomes a block displayed as – n.

Now, we have got the character to be repeated. Now, we must figure out how many times it is to be repeated. So, the REPT function is applied to calculate the number of blocks is: – E3*0.05, meaning one block would represent 1/5th part of the value. So, the number of blocks for marks 70 is shown as 3 repeated blocks in the result cell F3.

A similar formula is repeated on the remaining values, i.e., from E4 to E7, resulting in 4, 1, 2, and 5 blocks for marks 80, 20, 40, and 100 shown in the result cells F4, F5, F6, F7, respectively.

Things to Remember

  1. The 2nd parameter of the REPT function must be a positive number.
  2. If it is 0 (zero), the REPT function returns “” i.e. an empty text.
  3. If it is not an integer, it is truncated.
  4. The result of the REPT function cannot be longer than 32,767 characters. Else the REPT formula returns #VALUE! indicating an error with the value.

Recommended Articles

This has been a guide to REPT Function in Excel. Here we discuss the REPT formula and how to use it in excel along with examples and a downloadable excel template. You may also look at these useful functions in excel –

  • FLOOR Function Excel
  • MEDIAN Function in Excel
  • SLOPE in Excel
  • GROWTH in Excel
  • Spell Check in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Download Coursera IPO Financial Model

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download REPT Function Excel Template

Coursera IPO Financial Model & Valuation Free Download