Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Creating alpha numeric sequence

0

Hi There,

I would like to create a the following sequence:

1A
1B
1C
1D
1E
1F
1G
1H
2A
2B
2C
2D
etc etc up to 250H

Anyone have an idea on this?

excel
posted by: dbandit on Tue Jul 13, 2021 at 7:55 am
Answer
Discuss

Answers

0

Hi and welcome to the Forum

There may be more elegant solutions but copy this in cell A1 and copy down:

=INT(ROW(A1)/9)+1 & INDEX({"A","B","C","D","E","F","G","H"},MAX(MOD(ROW(A1),9),1))
Tip: You can use FillDown in the Editting portion of the Home ribbon to do that quickly- select A1 but where the cell name A1 appears (upper left above the sheet), type ":A2249" at the end and press Return (to select A1 to A2249) then press Fill/Down and the formula will copy down. With the range still selected, copy/ paste special (text only to fix the values).
posted by: John_Ru (rep: 6142) on Tue Jul 13, 2021 at 9:03 am
Discuss

Discussion

Oops, just corrected my answer a second time (since I'd posted only half of the formula then the formula for the last cell!)
John_Ru (rep: 6142) Jul 13, '21 at 9:32 am
Did you have problems with the solution?
John_Ru (rep: 6142) Jul 15, '21 at 4:30 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login