How do I auto format data so that the middle letter changes. EG 2.

's Computer Specifications

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

's Computer Specifications

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

's Computer Specifications

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
>

's Computer Specifications

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.. <pc..@discussions.microsoft.com>
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

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
in middle of letter Runtime error and must close	=?Utf-8?B?TWljaGFlbCBCYWxkaWdhcmE=?=	Microsoft Word Document Management	1	30th Mar 2007 06:18 AM
How do you find a capital letter in the middle of string or field	=?Utf-8?B?UCBzbWF1ZGVy?=	Microsoft Access	1	17th Apr 2006 07:51 PM
Cover letter template for middle school teacher	=?Utf-8?B?U3dlZXA=?=	Microsoft Word Document Management	1	8th Jan 2006 09:37 AM
How do I do a monogram(raise or lower the middle letter)	=?Utf-8?B?SmFja2ll?=	Microsoft Word Document Management	4	17th Oct 2005 05:43 AM
Letter format drop-down list (Data Validation)	Hans	Microsoft Excel Misc	2	3rd Mar 2004 06:28 PM