| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I auto format data so that the middle letter changes. EG 2.
I am trying to set up an exel spread sheet so that I can change the data by
dragging down instead of re-typing each one, so for example I want accross the top to be: 21A01 21A02 21A03 etc which I can just drag and it will automatically change the number digit but I am trying to change the down collum to read 21A01 21B01 21C01 etc etc |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I auto format data so that the middle letter changes. EG 2.
If your data starts in row 1, you could use a formula like:
="21"&char(row()+64)&"01" (it'll be good for about 26 cells <vbg.) =char(65) is the A character. So if you're starting on row 32, you could use: ="21"&char(row()+64-32+1)&"01" I'd convert it to values right after I filled the range. pc.. wrote: I am trying to set up an exel spread sheet so that I can change the data by dragging down instead of re-typing each one, so for example I want accross the top to be: 21A01 21A02 21A03 etc which I can just drag and it will automatically change the number digit but I am trying to change the down collum to read 21A01 21B01 21C01 etc etc -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I auto format data so that the middle letter changes. EG 2.
Paste this formula and drag it for the remaining cells.
="21"&CHAR(CODE("A")+ROW(Z1))&"01" Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "pc.." wrote: I am trying to set up an exel spread sheet so that I can change the data by dragging down instead of re-typing each one, so for example I want accross the top to be: 21A01 21A02 21A03 etc which I can just drag and it will automatically change the number digit but I am trying to change the down collum to read 21A01 21B01 21C01 etc etc |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I auto format data so that the middle letter changes. EG 2.
If you want more than 26 letters drag this down column A
="21"&SUBSTITUTE(ADDRESS(1,ROW(A1),4),"1","")&" 01" Will return 256 letters in 2003 Gord Dibben MS Excel MVP On Wed, 13 Jan 2010 17:37:01 -0800, pc.. wrote: I am trying to set up an exel spread sheet so that I can change the data by dragging down instead of re-typing each one, so for example I want accross the top to be: 21A01 21A02 21A03 etc which I can just drag and it will automatically change the number digit but I am trying to change the down collum to read 21A01 21B01 21C01 etc etc |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto Capital Letter in a cell | Excel Worksheet Functions | |||
| Auto capital letter in a cell | Excel Worksheet Functions | |||
| How do I set a drop-down to auto-pop. by typing first letter? | Setting up and Configuration of Excel | |||
| New Validation option to format 1st letter as Capital letter | Excel Discussion (Misc queries) | |||
| Auto format rows according to field data | Excel Discussion (Misc queries) | |||
Linear Mode
