I would like to autofill (if possible - am happy to do another way) a row that has a pattern shown here: I1A, I1B, I2A, I2B, I3A, I3B, … In more descriptive terms, the first character is the letter I, the second character contains numbers increasing from 1 on, but each one is repeated twice, and the third character alternates between the letter A and the letter B. I have no idea how to do this, so I am seeking assistance. Thanks!

  • Is the autofill across columns of a single row, or rows of a single column?
    – fixer1234
    Feb 3, 2019 at 4:03

3 Answers 3


This method works for a variety of problems.
We're going to take each of the three character positions as a column, then concatenate all of those into a fourth column -- a single field.

First column [="l"] or any constant character
- Just drop in a couple of "l" chars in successive rows.
- Select two or more and drag down on the fill handle (the square dot on lower right of the selection box)
Second column [= 1,1,2,2,3,3,...]
- Drop a numeric 1 in each of the first two data rows
- third row, set a formula to equal the FIRST row plus one
- fourth row, set a formula to equal the SECOND row
- Select the third and fourth row entries ONLY and drag the fill handle down.
Third column [=A,B,A,B...]
- Enter A and B in the first and second data rows, and then again in the third and fourth data rows
- Select all four and drag the fill handle down.
Fourth column [I1A, I1B, I2A, I2B, I3A, I3B ...]
- concatenate the other three columns
- drag fill down.

There are other ways to do this, but this is re-usable as all heck, and sounds appropriate to the level of the problem.

- Once you get a few rows sorted, you can simply drag fill this until doomsday. In fact, you can play around with dragging all four columns at once, but you'll have to find the right "height" of selection to start your drag from (probably four), and the right starting row.
- These simple formulas make it trivial to understand what's going on and to modify as needed for new tasks.
- This method lives in your head, not in a defined list somewhere.
- Takes up several columns, which may require an intermediate version of your sheet, if the thing is packed in there.


Here are a couple of methods. The question isn't clear as to whether you are replicating the formula across columns of a single row, or down rows of a single column. I'll show it for both.

Alternative 1

This approach looks at the previous cells to figure out what's needed. Since the pattern is based on two cells, you need to type in the first two (I1A and I1B). Enter this formula in the next cell.

If going across: If the first two cells are A1 and B1, this would go in C1:

="I" & IF(MID(A1,2,1)=MID(B1,2,1),1,0) + MID(B1,2,1) & RIGHT(A1,1)

If going down: If the first two cells are A1 and A2, this would go in A3:

="I" & IF(MID(A1,2,1)=MID(A2,2,1),1,0) + MID(A2,2,1) & RIGHT(A1,1)

This uses string functions to extract the characters that change, adjust them, and put the pieces back together (the ampersands concatenate the pieces). The first character is constant.

For the second character, it looks at whether the number (middle character) was the same for the previous two cells. If so, it's time to add 1. Otherwise it stays the same.

Since the last character repeats every other cell, it just uses the value from two cells earlier.

Once you have the formula in the third cell, you can copy it as far as you need it. Method 1 doesn't care where on the worksheet you put it.

Alternative 2

This approach uses some arithmetic tricks to figure out what goes in each cell.

The number in the middle increases by 1 for every two columns (or rows). The COLUMN function gives you the number of the column (A=1, B=2, etc.). The Row function does the same for row number. If you start in column A (or row 1), you already have the correct starting number. If this pattern starts in a later column (or row), you would need to subtract 1-less than the column (or row) number to yield a starting value of 1. Dividing by 2 and rounding up repeats the value for two columns (or rows), then increments.

If going across: If you start in column A, this piece of the formula would be:


If you start in say column C (the 3rd column), the formula would be:


If going down: If you start in row 1, this piece of the formula would be:


If you start in say row 3, the formula would be:


I'll show the simple versions in the whole formula below; just substitute the adjusted version if you start in a different column (or row).

The letter at the end alternates, so you can base that on odd vs. even column (or row) numbers.

If going across: If you start in an odd column, this piece would be:


If the starting column number is even, it would be:


If going down: If you start in an odd row, this piece would be:


If the starting row number is even, it would be:


The MOD function gives you the remainder after dividing the column (or row) number by 2. A column (or row) number that is even will have a remainder of 0, an odd one 1. So you sequence the "A" and "B" choices based on that. For the consolidated example below, I'll show the odd version.

Putting it together gives you:

If going across:

="I" & ROUND(COLUMN()/2,0) & IF(MOD(COLUMN(),2)=0,"B","A")

If going down:

="I" & ROUND(ROW()/2,0) & IF(MOD(ROW(),2)=0,"B","A")

Enter the appropriate version for the first cell, then copy it across or down for as many cells as you need.

  • both working properly, it looks simple but is bit complicated too. Perfect 10 ☺ Feb 4, 2019 at 10:13

First, put just 1, leave a cell blank below it, 2, leave a cell blank below it, like this


now select these 4 cells, and copy down as long as you want your numbers to be generated

This will give you 1, 2, 3, 4... with the gap of 1 blank cell in between

Second, in Column next to 1, enter ="I"&A1&"A", where A1 is cell having 1.

in the row below where you have entered the formula above, enter ="I"&A1&"B",

in effect, this will come to the cell next to the blank cell below 1

The cell B1 will now have I1A (first formula above) The cell B2 will now have I1B (second formula above), like this


Select B1:B2, copy down till where you had filled 1,2,3,... in first step

Your B column will have your list.


Excel file in which this is done

  • 1
    Works in Excel, but LO Calc isn't as smart. +1 even though this won't necessarily work in other spreadsheets.
    – fixer1234
    Feb 6, 2019 at 20:58
  • O.P. has tagged microsoft-excel so he only wants to find a solution for MS Excel. If he or someone wants for some other spreadsheet, they will say so and then I will see what can be done.
    – VSRawat
    Feb 6, 2019 at 21:04
  • 1
    Answers aren't just for the OP. For spreadsheets, it's always nice if a solution will work for users of other common "compatible" spreadsheets. Not every solution will. If someone discovers that a solution won't work in another app (or if the author knows that), it's good to indicate it in the answer or in a comment to save readers frustration trying to get it to work on another app. Still, the question is tagged Excel, and this works for Excel. Users of other apps can't always avail themselves of every solution. :-)
    – fixer1234
    Feb 6, 2019 at 21:14
  • 1
    I agree, which is why I upvoted it. There's nothing wrong with your answer. My point was just the purpose of the site is a knowledge base of solutions so people other than the OP can share them. People tend to think that "compatible" apps (Excel, LO Calc, Google Sheets, etc.) are identical, so they expect any solution to work on any app. They don't always. When we're aware that a solution won't work on "compatible" apps, it's a courtesy to note it so users of those apps don't waste time, or complain that it doesn't work (& maybe downvote), and then other users waste time figuring out why. :-)
    – fixer1234
    Feb 6, 2019 at 21:34
  • 1
    Not on this question, but I've done that on others. Google Sheets is off-topic; better for everyone if the question gets posted on the right site so answers will work for that app. Many solutions do work across apps, some don't. If the question is accurately tagged it's not a problem for the OP. The main issue is that users of other apps see a spreadsheet solution and expect it to work for them. It isn't an "uphill task" because we typically don't try for a universal solution. The solution is what it is. But if we know it's not compatible, it's a courtesy to note it.
    – fixer1234
    Feb 6, 2019 at 21:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .