Folks I'm designing a spreadsheet to hold log-on numbers for temporary
workers. The format of the numbers is MWI***Z, where *** is a 3-digit
number. Is there any way I can use an increment function to automatically
complete a long list of numbers eg MWI001Z, MWI002Z, without having to type
them all in. I've tried using the fill handle, but it doesn't work on this -
presumably because its a mix of letters and numbers.
Thanks for any help.
Janet
well, I'm no guru at this (yet) but I would say in a new worksheet in
column A type in MWI0001, MWI0002...etc then use the fill handle to fill as
far as you need
Incomumn B type Z and autofill it
then column C =CONCATENATE(A1,B1)
copy row C into your original worlsheet. Tis may be a "quick and dirty" for
some, but it works.
"Janet T" wrote:
> Folks I'm designing a spreadsheet to hold log-on numbers for temporary
> workers. The format of the numbers is MWI***Z, where *** is a 3-digit
> number. Is there any way I can use an increment function to automatically
> complete a long list of numbers eg MWI001Z, MWI002Z, without having to type
> them all in. I've tried using the fill handle, but it doesn't work on this -
> presumably because its a mix of letters and numbers.
> Thanks for any help.
> Janet
Oh make sure you "paste special - values" from column C
"Janet T" wrote:
> Folks I'm designing a spreadsheet to hold log-on numbers for temporary
> workers. The format of the numbers is MWI***Z, where *** is a 3-digit
> number. Is there any way I can use an increment function to automatically
> complete a long list of numbers eg MWI001Z, MWI002Z, without having to type
> them all in. I've tried using the fill handle, but it doesn't work on this -
> presumably because its a mix of letters and numbers.
> Thanks for any help.
> Janet
Assuming 2 header rows, in A3 put
="MWI"&TEXT(ROW()-2,"000")&"Z"
and fomula-drag that as far down as you need.
Adjust the Row()-2 if you don't start on Row 3
Originally Posted by Janet T
If you format A1:
Format > Cells... > Number Custom > "MWI"000Z
then the cells will appear as you want and you can still increment normally:
=A1+1 in A2, etc.
--
Gary's Student
"Janet T" wrote:
> Folks I'm designing a spreadsheet to hold log-on numbers for temporary
> workers. The format of the numbers is MWI***Z, where *** is a 3-digit
> number. Is there any way I can use an increment function to automatically
> complete a long list of numbers eg MWI001Z, MWI002Z, without having to type
> them all in. I've tried using the fill handle, but it doesn't work on this -
> presumably because its a mix of letters and numbers.
> Thanks for any help.
> Janet
Ufo & Bryan - thanks very much for your help - they both worked wonderfully :-)
Bryan
This worked fine until we got to MWI499z - for some reason the next number
turned to mwi4100Z. Any ideas?
Thanks
Janet
"Bryan Hessey" wrote:
>
> Assuming 2 header rows, in A3 put
>
> ="MWI"&TEXT(ROW()-2,"000")&"Z"
>
> and fomula-drag that as far down as you need.
>
> Adjust the Row()-2 if you don't start on Row 3
>
>
> Janet T Wrote:
> > Folks I'm designing a spreadsheet to hold log-on numbers for temporary
> > workers. The format of the numbers is MWI***Z, where *** is a 3-digit
> > number. Is there any way I can use an increment function to
> > automatically
> > complete a long list of numbers eg MWI001Z, MWI002Z, without having to
> > type
> > them all in. I've tried using the fill handle, but it doesn't work on
> > this -
> > presumably because its a mix of letters and numbers.
> > Thanks for any help.
> > Janet
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=483091
>
>
The only way I can think of this happening is for the character "4" to have
been inadvertently included as part of the text formatting - that is,
"MWI4"00Z instead of "MWI"400Z
"Janet T" <[email protected]> wrote in message
news:[email protected]...
> Bryan
> This worked fine until we got to MWI499z - for some reason the next number
> turned to mwi4100Z. Any ideas?
> Thanks
> Janet
>
>
> "Bryan Hessey" wrote:
>
>>
>> Assuming 2 header rows, in A3 put
>>
>> ="MWI"&TEXT(ROW()-2,"000")&"Z"
>>
>> and fomula-drag that as far down as you need.
>>
>> Adjust the Row()-2 if you don't start on Row 3
>>
>>
>> Janet T Wrote:
>> > Folks I'm designing a spreadsheet to hold log-on numbers for temporary
>> > workers. The format of the numbers is MWI***Z, where *** is a 3-digit
>> > number. Is there any way I can use an increment function to
>> > automatically
>> > complete a long list of numbers eg MWI001Z, MWI002Z, without having to
>> > type
>> > them all in. I've tried using the fill handle, but it doesn't work on
>> > this -
>> > presumably because its a mix of letters and numbers.
>> > Thanks for any help.
>> > Janet
>>
>>
>> --
>> Bryan Hessey
>> ------------------------------------------------------------------------
>> Bryan Hessey's Profile:
>> http://www.excelforum.com/member.php...o&userid=21059
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=483091
>>
>>
Janet,
There is no apparent reason except to agree with Stephen and think that either the formula was corrupted, or you have rows 500 to 4101 Hidden.
The formula is based on the Row number minus 2 (if you started in row 3) - you would need 'Row()+3600', or 'Row(A4102)-2' to produce 4100 in Row 502
What is the next row number after you see MWI499Z ?
and what is the formula there?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks