CHAR-CODE Function Combo & Character Autofill in Excel

If you have a bunch of text going AA1, AB2, AC3, or just text going A, B, C, D, there is no way for you to use Autofill to continue the alphabetic sequence. In order to work around this issue, I use a combination of CHAR, CODE, MID, and circular referencing between two columns. 

This is important because, in using Advanced Filtering for multiple columns, you are required to have the cell references preceding a condition from the columns you’d like to filter when trying to filter data from multiple columns at once.

For example, say you have a cell containing the text, “AA5”, and you’d like to fill down a column continuing AA5 with AB6, and so on until AZ30.

In order to do this, type =CHAR(CODE(MID(Cell Address of AA5,2,1))+1)&(MID(Cell Address of AA5,3,2)+1) into the cell at the bottom left of AA5. That cell should then read “B6”.

In order to help you extrapolate this information into other instances, I will explain what each part of this formula does.

There are three two parts to this formula, CHAR(CODE(MID(Cell Address of AA5,2,1))+1), (MID(Cell Address of AA5,3,2)+1), and then the &, that perform basically perform the same sequence except on two different substrings of a cell’s text. 

Part 1 goes like this:

1. MID returns a substring “A” from AA5 from the second A to itself.

2. The CODE function then returns a specific # for the A character. 

3. 1 is added to the returned number.

4. CHAR then translates the resulting number to “B” and returns it to the formula.

Part 2 goes like this:

1. MID returns a substring “5” from AA5 from the 5 to itself.

2. 1 is added to 5. Because the returned substring was a number, you can do arithmetic calculations with it.

3. 6 is returned to the formula.

Part 3:

1. The text concatenation operator combines B and 6.

2. B6 is returned.

In order to fill AA5 in a series down to AZ30, you have to use a circular reference. What you should do is enter ="A"&Cell Address of B6 into the cell under AAB and to the right of the cell you entered that last formula. The cell should read AB6.

You’re basically done now, you can just use the fill handle to Autofill the values down to AZ30. This works because the next left formula column will refer to the last formula you put in the right column, and so on until you reach AZ30.

If you just want to update a character down a couple rows (i.e. A-T) you can just use that left part of the formula and replace the MID function with LEFT(Cell Reference, 1) and then Autofill the formula down 19 rows.