Naimul Hasan Arif
It will be very supportive if we can AutoFill sequential letters in Excel instead of putting them one at a time. In this article, I will try to show some practical ways on how to AutoFill sequential letters in Excel.
For clarification, I am gonna use a dataset of a company where the Gate No is mentioned according to the Watchman’s Name.
Download Practice Workbook
5 Ways to AutoFill Sequential Letters in Excel
1. Using CHAR & CODE Functions to AutoFill Sequential Letters in Excel
The use of the CHAR function and CODE function is one of the easiest ways to AutoFill sequential letters in Excel.
Steps:
- We need to input the first alphabet which is A.
- I inserted the first Alphabet A.
- Input the following Formula in cell C6.
=CHAR(CODE(C5)+1)
Formula Breakdown
- CODE(C5) —–> It returns the code for the value of the C5 cell.
- Output : 65
- CHAR(CODE(C5)+1)
- CHAR(65+1) —–> The CHAR function will return the character depending on the ASCII table.
- Output : “B”
- Press ENTER.
Then, we will find the sequential value which is B.
- Select the output cell and drag it to the required cell. Here Fill Handle will AutoFill the formula for the rest of the cells.
We will be able to see the values sequentially.
Note: If we keep AutoFilling, it will work perfectly till Z. But after that, it will not give sequential letters. It will give the corresponding character against the code.
Rather, it will give value according to the ASCII table.
Read More: How to Use Autofill Formula in Excel (6 Ways)
2. Employing SUBSTITUTE Function to AutoFill Sequential Letters in Excel
Using the SUBSTITUTE function along with the ADDRESS function and the ROW function to AutoFill sequential letters in Excel.
Steps:
- We need to input the following formula in cell C5.
=SUBSTITUTE(ADDRESS(1,ROWS($A$1:$A1),4),1,"")
Formula Breakdown
- ROWS($A$1:$A1) —> The ROWS function returns the value based on row numbers.
- Output : 1
- ADDRESS(1,1,4) —> The ADDRESS function will return the address depending on the row_num and col_num.
- Output : “A1”
- SUBSTITUTE(ADDRESS(1,ROWS($A$1:$A1),4),1,” “)
- SUBSTITUTE(‘A1’,1,””) —–> Here, the SUBSTITUTE function will substitute 1 character from the left side of the text.
- Output: A
- Press ENTER.
Then, we will have the first letter A.
- Next, we will use the Fill Handle to AutoFill the formula for the rest of the cells.
We will see the results on the datasheet.
Note: In this process, the cell will be filled AA,AB,AC…………….. in this fashion whenever the values are greater than 26.
Read More: How to Enter Sequential Dates Across Multiple Sheets in Excel
3. Implementing LEFT Function to AutoFill Sequential Letters in Excel
A very efficient way is the use of the LEFT function including ADDRESS function and ROW function to AutoFill sequential letters in Excel.
Steps:
- Input the following Formula in cell C5 :
=LEFT(ADDRESS(1,ROW(A1),4,1),(ROW(A1)>26)+1)
Formula Breakdown
- ROW(A1) —–> The ROW function returns the row number of the referenced cell.
- Output : 1
- ADDRESS(1,1,4,1) —–> The ADDRESS function will return the address depending on the row_num and col_num.
- Output : “A1”
- (ROW(A1)>26) —–> It will check whether the row number is greater than 26 or not.
- Output : FALSE
- FALSE+1 —–> becomes
- Output : 1
- LEFT(ADDRESS(1,ROW(A1),4,1),(ROW(A1)>26)+1)
- LEFT({“A1”},{1}) —–> Here, the LEFT function will extract 1 character from the left side of the text.
- Output : A
- Explanation : it returns the left 1 character from A1 which is A.
- Press ENTER.
You will have the first alphabet A.
- Then, use the Fill Handle to AutoFill the formula for the rest of the cells.
The rest cells will be sequentially filled.
The increase of letters will be in AB, AC, AD…….. Format whenever the value is greater than 26.
Read More: How to AutoFill from List in Excel (8 Quick Ways)
Similar Readings:
- Drag Number Increase Not Working in Excel (A Solution with Easy Steps)
- AutoFill Not Incrementing in Excel? (3 Solutions)
- Excel VBA: Autofill Method of Range Class Failed (3 Solutions)
- How to Autofill a Column in Excel (7 Easy Ways)
- Perform Predictive Autofill in Excel (5 Methods)
4. Application of Combined Functions to AutoFill Sequential Letters in Excel
Another way for two letters is to apply Combined Function. This combined function includes IF function, RIGHT function, CHAR function, CODE function, LEFT function, and MOD function.
Steps:
- We have given the first input which is AB.
- Write the following Formula in cell C6 :
=IF(RIGHT($C5,1)="Z",CHAR(CODE(LEFT(C5,1))+1),LEFT(C5,1))&CHAR(65+MOD(CODE(RIGHT(C5,1))+1-65,26))
Formula Breakdown
- RIGHT($C5,1) —–> The RIGHT function extracts the characters from the right.
- Output : B
- RIGHT($C5,1)=”Z”—–> The RIGHT function checks if the second letter of the first input is equal to “Z” or not.
- “B”=”Z”
- Output : FALSE
- CHAR(CODE(LEFT(C5,1))) —–> It will return the character based on code value.
- CHAR(CODE(“A”))
- CHAR(65)
- Output : A
Note: If the output of RIGHT($C5,1)=”Z” be TRUE,
Then,
- CHAR(CODE(LEFT(C5,1))+1) —–> It will return the character based on code value.
- CHAR(CODE(“A”)+1)
- CHAR(65+1)
- CHAR(66)
-
-
- Output :B
-
Note: If the output of RIGHT($C5,1)=”Z” be FALSE,
Then,
- LEFT(C5,1) —–> Here, the LEFT function will extract 1 character from the left side of the text.
- Output : A
- CHAR(65+MOD(CODE(RIGHT(C5,1))+1-65,26)) —–> becomes
- CHAR(65+MOD(CODE(RIGHT(“AB”,1))+1-65,26))
- CHAR(65+MOD(CODE(“B”))+1-65,26))
- CHAR(65+MOD(66+1-65,26))
- CHAR(65+MOD(67-65,26))
- CHAR(65+MOD(2,26))
- CHAR(65+2)
- CHAR(67)
- Output : C
- IF(RIGHT($C5,1)=”Z”,CHAR(CODE(LEFT(C5,1))+1),LEFT(C5,1))&CHAR(65+MOD(CODE(RIGHT(C5,1))+1-65,26))
- “A”&”C”
- Output : AC
- Then, press ENTER.
- To AutoFill the formula for the rest of the cells, use the Fill Handle.
Related Content: Applications of Excel Fill Series (12 Easy Examples)
5. Employing User Defined Function to AutoFill Sequential Letters in Excel
We can also use VBA to AutoFill sequential letters in Excel.
Steps:
- Go to the Developer tab and select Visual Basic.
We can also press Alt + F11 as an alternative way.
- From the Insert option, select Module.
- Write the following code in the Module.
Function AutoFill(b As Long) As String
AutoFill = Replace(Cells(1, b).Address(False, False), "1", "")
End Function
Here, I’ve created a User Defined Function AutoFill
Then, used the Replace.Address property to determine the cell value.
- Save to code and go back to the worksheet to use the function.
- Select cell C5 and type the following formula.
=AutoFill(ROW())
The result will appear according to the row_num. As the row number of C5 cell is 5 it is returning the 5th letter which is E.
- Use Fill Handle to AutoFill the rests.
Note:
In order to get the value from the first alphabet, we have to start working from the first row.
Related Content: [Fixed!] AutoFill Formula Is Not Working in Excel Table (3 Solutions)
Practice Section
You can practice here for further expertise.
Conclusion
In this article, I explained 4 ways to AutoFill Sequential Letters in Excel. Anyone can choose any process according to their choice. For further queries, leave your thoughts in the comment section.
