How to Use the REPT Function to Repeat Text Strings in Excel
=REPT(text, number_times)The REPT function is a fundamental text manipulation tool in Excel that allows you to repeat a text string a specified number of times. This function is particularly valuable when you need to create visual representations, generate repetitive patterns, or build dynamic text strings based on numerical values. Whether you're creating progress bars, generating formatting patterns, or building concatenated text sequences, REPT provides a simple yet powerful solution. Understanding the REPT function opens up numerous possibilities for text automation in your spreadsheets. From creating visual indicators in dashboards to generating formatted lists, this beginner-friendly formula integrates seamlessly with other Excel functions. The REPT function has been available since Excel 2007 and remains consistent across all modern versions including Excel 2019 and Microsoft 365, making it a reliable choice for any Excel user looking to streamline text operations.
Syntax & Parameters
The REPT function uses a straightforward syntax: =REPT(text, number_times). The first parameter, 'text', is required and represents the text string you want to repeat. This can be a single character, a word, a phrase, or even a cell reference containing text. The second parameter, 'number_times', is also required and specifies how many times the text should be repeated. This parameter must be a positive integer; if you provide a decimal number, Excel will truncate it to a whole number. When working with the 'text' parameter, you can use literal text enclosed in quotation marks (such as "*" or "ABC") or reference a cell containing text. The 'number_times' parameter accepts numbers, cell references, or formulas that evaluate to numbers. If number_times is zero or negative, REPT returns an empty string. If number_times is not an integer, Excel automatically converts it by removing the decimal portion. For optimal results, ensure your number_times value is reasonable, as extremely large repetitions may create very long strings that could impact performance.
textnumber_timesPractical Examples
Creating a Visual Progress Bar
=REPT("█",ROUND(A2*10,0))&REPT("░",10-ROUND(A2*10,0))This formula multiplies the completion percentage in cell A2 by 10, rounds it, and repeats a filled block character that many times. It then concatenates empty block characters for the remaining portion, creating a visual progress indicator.
Generating Repetitive Customer IDs
=REPT("BATCH"&TEXT(ROW(),"000"),B2)This formula combines the REPT function with TEXT to format a batch number and repeat it based on the quantity in column B. Each row generates a unique batch ID repeated the specified number of times.
Creating Separator Lines for Reports
=REPT("-",80)This simple formula repeats the dash character 80 times to create a consistent separator line. The analyst can adjust the number to match their desired line length.
Key Takeaways
- REPT is a text function that repeats a specified text string a given number of times, with syntax =REPT(text, number_times)
- Both parameters are required; the text parameter accepts literal strings or cell references, while number_times must be a positive integer
- REPT is ideal for creating visual indicators, progress bars, repetitive patterns, and formatted separators in spreadsheets
- The function works across all Excel versions from 2007 to 365 and integrates seamlessly with other text and logical functions
- Results are limited to 32,767 characters per cell; extremely large repetitions should be tested for performance impact
Pro Tips
Use REPT with CHAR function to create special formatting characters. For example, =REPT(CHAR(9), 5) creates tab characters for indentation, and =REPT(CHAR(10), 2) creates line breaks within a cell.
Impact : Enables advanced text formatting and layout control without manual character input, saving time and ensuring consistency.
Combine REPT with ROUND and division to create proportional visual indicators. For example, =REPT("█", ROUND(A1/B1*20, 0)) creates a bar proportional to the ratio of A1 to B1, perfect for dashboards.
Impact : Creates professional-looking data visualizations within cells, eliminating the need for separate charts in some scenarios.
Use IFERROR with REPT to handle edge cases gracefully. For example, =IFERROR(REPT(A1, B1), "") prevents errors if B1 is negative or non-numeric, displaying an empty string instead.
Impact : Improves spreadsheet reliability and prevents error propagation, making formulas more robust in production environments.
Remember that REPT respects Excel's 32,767 character limit per cell. For large repetitions, test your formula with sample data first and consider breaking results across multiple cells if needed.
Impact : Prevents unexpected truncation of data and ensures your formulas work as intended even with large repetition counts.
Useful Combinations
REPT with CONCATENATE for Dynamic Messages
=CONCATENATE("Rating: ", REPT("★", A1), REPT("☆", 5-A1))This combination creates a star rating display. If A1 contains 3, it displays "Rating: ★★★☆☆". The REPT function repeats filled stars based on the rating value and empty stars for the remainder, creating a visual rating system.
REPT with IF for Conditional Repetition
=IF(A1>0, REPT("✓", A1), REPT("✗", ABS(A1)))This formula uses IF to conditionally repeat different characters based on whether the value in A1 is positive or negative. Positive values show checkmarks, negative values show X marks. The ABS function ensures the repetition count is always positive.
REPT with LEN for Character-Based Formatting
=REPT("-", LEN(A1))This formula repeats a dash character as many times as there are characters in cell A1. If A1 contains "Hello" (5 characters), it produces "-----". Useful for creating separator lines that match text length.
Common Errors
Cause: The number_times parameter is negative, non-numeric, or contains text that cannot be converted to a number. For example: =REPT("text", "five") or =REPT("text", -3)
Solution: Verify that number_times is a positive integer or a cell reference containing a positive number. Use INT() or ROUND() to ensure the value is numeric. For negative values, use MAX(0, number_times) to return an empty string instead of an error.
Cause: The function name is misspelled or not recognized by Excel. For example: =REP("text", 5) or =REPEAT("text", 5) instead of =REPT("text", 5)
Solution: Double-check the function spelling. The correct function name is REPT (not REP or REPEAT). Ensure there are no extra spaces or characters in the formula.
Cause: A cell reference used in the formula has been deleted or moved. For example: =REPT(A1, B1) where column A or B has been deleted, or the referenced cells are in a closed workbook.
Solution: Verify that all cell references in the formula still exist and point to valid cells. If cells were moved, update the references accordingly. Use absolute references ($A$1) if you plan to copy the formula to prevent reference shifts.
Troubleshooting Checklist
- 1.Verify the text parameter is enclosed in quotation marks if using literal text, or correctly references a cell containing text
- 2.Ensure the number_times parameter is a positive integer or evaluates to a positive number; check for negative values or text entries
- 3.Confirm the function name is spelled correctly as REPT (not REP, REPEAT, or other variations)
- 4.Check that the result doesn't exceed 32,767 characters per cell; if it does, reduce the repetition count or split across multiple cells
- 5.Verify all cell references are valid and haven't been deleted; update references if columns or rows have been moved
- 6.Test the formula with simple values first (e.g., =REPT("A", 5)) before implementing complex combinations with other functions
Edge Cases
number_times equals zero
Behavior: REPT returns an empty string ("") instead of an error
Solution: This is expected behavior. Use IF to handle this case if needed: =IF(B1=0, "N/A", REPT(A1, B1))
Useful for creating conditional text where zero repetitions means no output
text parameter is an empty string
Behavior: REPT returns an empty string regardless of number_times value
Solution: Check that the text parameter contains actual content. Use IF to verify: =IF(A1="", "[Empty]", REPT(A1, B1))
Empty strings combined with any repetition count always produce empty results
number_times is very large (e.g., 100,000)
Behavior: REPT may produce a string exceeding the 32,767 character limit, resulting in truncation without warning
Solution: Use MIN to cap the repetition count: =REPT(A1, MIN(B1, 1000)) or validate input before using REPT
Excel silently truncates results exceeding the character limit; always validate large repetition counts
Limitations
- •REPT is limited to 32,767 characters per cell, which limits the maximum repetitions for longer text strings. For example, repeating a 10-character string more than 3,276 times will exceed this limit.
- •REPT returns a single text string, not an array or list. To create arrays or multiple values, you must use additional functions or split the result across multiple cells.
- •The number_times parameter must be a positive integer or zero; negative values or non-numeric entries produce #VALUE! errors. Decimals are truncated rather than rounded.
- •REPT does not support conditional logic within the function itself; you must use IF, IFS, or other conditional functions to create conditional repetition patterns.
Alternatives
Compatibility
✓ Excel
Since 2007
=REPT(text, number_times) - Identical syntax across Excel 2007, 2010, 2013, 2016, 2019, and 365✓Google Sheets
=REPT(text, number_times) - Fully compatible with Google SheetsGoogle Sheets supports REPT with identical syntax and functionality. Works seamlessly with Google Sheets' text functions and integrates with ARRAYFORMULA for array operations.
✓LibreOffice
=REPT(text, number_times) - Fully compatible with LibreOffice Calc