Written by Allen Wyatt (last updated March 3, 2018)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
Todd is setting up a comic book inventory list and would like each cell in column A to contain the text "Batman (1940) 0" repeated 3 times, followed by "Batman (1940) 1" repeated three times, "Batman (1940) 2" repeated 3 times, all the way through "Batman (1940) 700" repeated 3 times. That is 2,103 individual lines, but Todd isn't sure how to go about repeating patterns like this when copying or filling cells.
There are actually a few ways you can approach a task such as this. One way is to simply create a single sequence and then copy and paste twice. Here's the general idea:
This whole process goes very quickly; I was done with it in about 45 seconds. There is a drawback, though: After sorting, you won't get the sequence of cells in numeric order based on the number at the end of the text. If you want to ensure that, you'll want to enter your first comic book (in cell A1) as "Batman (1940) 000". When you use three digits for the ending number, the sorting will come out just fine.
There's another approach you can use that relies on the fill handle, as well. Follow these general steps:
Figure 1. The Go To Special dialog box.
A third approach—one which I find very quick to accomplish—is to use a formula from the get-go:
If you need to create sequences such as this quite often, then you'll appreciate a macro-based approach. The following is simple, placing the desired text sequence into column A:
Sub ComicSequence() Dim sTemp As String Dim J As Integer Dim K As Integer sTemp = "Batman (1940) " For J = 0 To 700 For K = 1 To 3 Cells(J * 3 + K, 1).Value = sTemp & J Next K Next J End Sub
To use a different preface to your cell values, all you need to do is to change the value you assign to the sTemp string.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9410) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
As you use Excel to collect data over time, sometimes winnowing out the latest data can present a challenge. Here are a ...
Discover MoreNeed to figure out if a cell contains a number so that your formula makes sense? (Perhaps it would return an error if the ...
Discover MoreLimiting what can be entered in a cell can be an important part of developing a worksheet that other people use. Here are ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-02-27 13:49:27
Steve Schmidt
Is there a way in excel to repeat a cells information across the rows by time? ie setting up a copying a "service due" cell every 3/6/9/12 months
2018-03-05 04:01:51
Thomas Papavasileiou
For my taste, in cases of sequences with a given number of characters (in this case we want tree digits), I would prefer ...001, ...002 to ...700.
This can be done by replacing:
Cells(J * 3 + K, 1).Value = sTemp & J
by
Cells(J * 3 + K, 1).Value = sTemp & Format(J, "000")
2018-03-04 10:52:40
Dave Bonin
Another approach would be to apply a custom number format such as:
"Batman (1940) "0;@
This will visually appear as requested, even when using a filter.
To go a little fancier, you could use:
"Batman (1940) "??0;@
This would make all of the digits line up.
2018-03-03 05:32:26
Another way:
Sub RepeatBatman()
Dim strBatman As String
Dim i, j
i = 1
j = 0
strBatman = "Batman (1940) "
For i = 1 To 2103
Range("A" & i).Value = strBatman & j
If i Mod 3 = 0 Then j = j + 1
Next
End Sub
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments