Written by Allen Wyatt (last updated April 6, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
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, Excel in Microsoft 365, and 2021.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
When entering information in a worksheet, it is common to also note a date or time corresponding to the entry. There are ...
Discover MoreWouldn't it be great if you could have Excel display some text in a cell only when that cell is empty? Unfortuantely, ...
Discover MoreEntering data in a worksheet can be time consuming. One of the tools that Excel provides to make entry easier is ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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 © 2024 Sharon Parq Associates, Inc.
Comments