Incrementing Mixed text & numbers

J

Janet T

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
 
U

ufo_pilot

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.
 
B

Bryan Hessey

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
 
G

Gary''s Student

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.
 
J

Janet T

Bryan
This worked fine until we got to MWI499z - for some reason the next number
turned to mwi4100Z. Any ideas?
Thanks
Janet
 
S

Stephen

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
 
B

Bryan Hessey

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?
 
Top