|
Incrementing Mixed text & numbers
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 |
Incrementing Mixed text & numbers
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 |
Incrementing Mixed text & numbers
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 |
Incrementing Mixed text & numbers
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 |
Incrementing Mixed text & numbers
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 |
Incrementing Mixed text & numbers
Ufo & Bryan - thanks very much for your help - they both worked wonderfully :-)
|
Incrementing Mixed text & numbers
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 |
Incrementing Mixed text & numbers
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" wrote in message ... 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 |
Incrementing Mixed text & numbers
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? Janet T Wrote: 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 -- 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 |
| All times are GMT +1. The time now is 04:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com