how do i use Fill in Excel with numbers and letters

[BostonBiker]

I'm am sure this is an easy thing to do in excel, but I can't seem to find the right way of using the Fill feature in Excel.

I want to know how to drag three cells that have the following in them:

001A
001B
001C


so the next cell below will have 001D then below that cell it will go in order:

001A
001B
001C
001D
001E
001F


I am sure it can be done. Thanks for your help.

 

[bangbangchicken]

Try experementing with the format of the cells. It may defaulty be set to number or general.

 

[Bowers74]

Excel will increment numbers and dates for you, but not letters, so you will have to use a formula for what you want to do.

In cell A1 enter the following formula:

="001"&CHAR(64+ROW())

This will only work for "001A" to "001Z" in rows 1 through 26. If you want to have the number increment as well (i.e. "001Z" then "002A&quot you will have to extend the formula a bit.

=TEXT(CEILING(ROW(),26)/26,"000&quot&CHAR(64+ROUND(MOD(ROW(),26.1),0))

This will work for "001A" to "005Z" in rows 1 through 130. I am working on one that will work for all 65536 rows thought! ;-)

I hope this helps so far though!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

OK, Now I've got it . . .

This will work for all 65,536 rows if you want to use it:

=TEXT(CEILING(ROW(),26)/26,"000&quot&CHAR(90+(ROW()-CEILING(ROW(),26)))

Put the formula into cell A1 and AutoFill down as far as you want to. I will return "001A" to "2521P".

Enjoy! ;-)



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Dreamboat]

Hey, Mike. Looks like you do dern well with text formulas. Can you help me out here? I'm not very good at getting dates and stuff to be seen properly, tho I can do all the left, mid, rights, etc... I'd appreciate any help.

thread68-653393

Anne Troy
Way cool stuff:

http://www.cafeshops.com/OfficeExperts