by Allen Wyatt
(last updated March 3, 2018)
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, and 2016.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
When you are developing a worksheet for others to use, you may want to have entries in a particular cell (or cells) be ...Discover More
Excel supports several types of dashes. This tip describes those different types and explains how to enter them in a cell.Discover More
Do you need to change whether a particular reference in a formula uses a relative or absolute reference? If so, you may ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.