How to repeat a character n times in a cell in Excel?
When working with Excel, you may occasionally need to repeat a specific character multiple times within a cell, either for formatting, data visualization, or for meeting particular input requirements. For example, you might want to display a series of asterisks as a password mask, generate custom barcode - like strings, or simply fill a cell with repeated characters as a visual divider. At times, you may also wish to automate such tasks or apply them to multiple cells at once. This article explores several practical methods to repeat a character n times in a single cell or across multiple cells, catering to both simple and advanced needs in Excel.
Repeat a character n times in a cell with formula
Fill a cell with repeated character in Excel
Fill a cell with repeated * character and certain number in Excel
Fill cells with repeated characters using VBA code
Repeat a character n times in a cell with formula
When you need to repeat a certain character a specified number of times within a single cell, Excel’s REPT function provides a direct and easy solution. This is especially useful for visually marking text, creating fixed-length codes, or generating placeholders. Using a formula ensures consistency and flexibility, as you can adjust the repeated character and count as needed.
1. Select a blank cell where you want to display the repeated character, enter the following formula into the Formula Bar:
=REPT("*",4)Then press the Enter key to get the result. The cell will show four asterisks (“****”). Refer to the screenshot below for an example.
Tips and notes:
- In this formula,
"*"is the character to be repeated, and4is the number of times to repeat it. Both can be replaced as needed, for example=REPT("#",10). - If you want to make the formula dynamic based on cell values, for instance, repeat the character in column A, n times according to number in column B, you can use
=REPT(A1,B1). - If you enter very large numbers as the repeat count, Excel may display an error or stop at its maximum supported length (generally up to32,767 characters per cell).
This solution is straightforward and suitable for manual data entry or when the repeat logic is simple. However, it is less effective if you need to batch process dynamic inputs across many cells, in which case VBA automation can provide more flexibility.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Fill a cell with repeated character in Excel
If your requirement is to fill the entire width of a cell with repeating instances of a single character (such as “-” or “*”), Excel’s cell formatting options make this easy. This is commonly used for visual dividers or to create lines in tables. Unlike using a formula, this method relies on cell formatting, which automatically repeats the entered character to fill the visible area of the cell.
1. Click a blank cell where you’d like to display the repeating character, then press Ctrl + 1 to open the Format Cells dialog box.
2. In the Format Cells dialog, click the Alignment tab. Next, find the Horizontal drop-down list and select the Fill option. Click OK to save your change. Please see screenshot:
Now, when you type a single character (for example, "-") in this cell, Excel will automatically repeat this character to visually fill the entire cell’s horizontal space, as the example below shows:
Notes and application scenarios:
- This method is ideal for making separator lines, context dividers, or visual cues within tables.
- The content only fills visually – if you reference the cell, only the single character you entered is returned, not the whole repeated string.
- This formatting is best suited for display and may not be suitable for scenarios where you need the repeated value for calculations or text operations.
Fill a cell with repeated * character and certain number in Excel
In some cases, you may want to display a repeated symbol (for example, *), followed by a number, within the same cell. This is often used for formatting specific values, such as codes, product IDs, or passwords, giving a fixed-width appearance or emphasizing numeric data.
1. Select the target cell, then right-click and select Format Cells from the context menu as shown below:
2. In the Format Cells window, go to the Number tab, choose Custom under Category. In the Type input box, type **0, then click OK.
Now, whenever you type a number such as “45” in this cell, Excel automatically displays it as “**45” with the repeated asterisk character before the number, as shown below:
Considerations:
- You can adjust the pattern in the Type box, such as entering
"$$"0to add two dollar signs before numbers, or***0for three asterisks. - This only affects how the value is displayed, not the underlying value for calculations.
Fill cells with repeated characters using VBA code
If your task involves processing multiple cells, or automating the process for batch operations, using a VBA macro can provide more flexibility and efficiency than manual formulas. This method lets you process multiple cells at once and directly fills them with repeated characters as plain text - no formulas, no dragging needed.
Applications and considerations:
- Suitable for scenarios where you have to process or fill many cells at once, or when the repeated character/number of repeats are stored in different cells.
- VBA macros require macro-enabled workbooks and that you enable macros within Excel for execution.
- Remember to save your work before running macros, and consider backing up important data to prevent accidental overwriting.
Steps to automate repeating a character using VBA:
1. Open Excel, press Alt + F11 to open the Visual Basic for Applications editor window. Click Insert > Module to add a new module. Copy and paste the code below into the module window:
Sub RepeatCharBatch()
Dim rng As Range
Dim cell As Range
Dim RepeatChar As String
Dim RepeatTimes As Long
Dim Answer As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the range to process:", xTitleId, rng.Address, Type:=8)
RepeatChar = Application.InputBox("Enter the character to repeat:", xTitleId, "", Type:=2)
RepeatTimes = Application.InputBox("How many times to repeat the character?", xTitleId, 1, Type:=1)
If rng Is Nothing Or RepeatChar = "" Or RepeatTimes <= 0 Then
MsgBox "Cancelled or invalid input.", vbExclamation
Exit Sub
End If
For Each cell In rng
cell.Value = String(RepeatTimes, RepeatChar)
Next cell
End Sub2. To execute the macro, press F5 or click the Run button 
. A dialog box will prompt you to select the target cell(s), specify the character to repeat, and enter the number of repetitions. Click OK to process.
Troubleshooting and tips:
- If "How many times" input is not a positive integer, the macro will not proceed.
- You can use it on a single cell or select a batch area for bulk processing.
- All cells in your chosen range will be reset with only the repeated character output—pre-existing data will be overwritten.
- Save your workbook before running the macro to avoid unintentional data loss, especially when applying to large ranges.
- This approach supports repeating not just symbols (e.g. "*") but also alphanumeric characters to form patterns (such as "A", "0", "#", etc.).
With this VBA macro, you can quickly and flexibly automate character repetition in Excel across various cells or sheets, adjusting both the character and the repeat count on the fly.
Related articles:
- How to repeat a cell value until new value is seen or reached in Excel?
- How to repeat rows on top of every printout except the last page in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in