Results 1 to 12 of 12

20080314, 17:03 #1
 Join Date
 Sep 2006
 Location
 Illinois, USA
 Posts
 22
 Thanks
 0
 Thanked 0 Times in 0 Posts
Excel & different numbering systems (2003)
I'm looking to be able to produce a spreadsheet that can calculate serial numbers in different numbering systems i.e. base33, base34, base36 etc.. These numbers would then be printed on rolls of labels. I would like to be able to enter the following data: 1. starting number (000001) 2. # of labels on a roll (10,000), 3. step # (increment by 1) 4. total number of labels to print (100,000). After entering these parameters, the output should look like this:
ROLL# START# END #
1 000001 009999
2 100000 109999
3 110000 119999 etc......for however many rolls the job entails (total qty 100,000 / labels on roll 10,000) = 10 rolls
I've been to several websites and see UDF's to create serial numbers in alphanumeric sequences but I can't quite get the whole picuture to produce the output I'm looking for.....
Any ideas will be much appreciated.
Thanks,
 BOB 

20080314, 17:28 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Excel & different numbering systems (2003)
Can you explain what the different number systems (base 33 etc.) have to do with the problem of finding the start and end numbers for the rolls?
The attached workbook shows how you can use formulas to compute the start and end numbers.
If you modify the values in the cells with a green background, the cells with a blue background will update themselves automatically.

20080314, 17:36 #3
 Join Date
 Sep 2006
 Location
 Illinois, USA
 Posts
 22
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel & different numbering systems (2003)
Hi Hans,
Thanks for the reply. I was able to use the formula you created last year. I can get the first line to work but then when trying to calculate the second line, the formula bombs out because an entry like 0001F4 is not recognized as a number. I should have put in my example some numbers showing both numeric and alpha entries. Maybe I'm using the function you created correctly.

20080314, 17:43 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Excel & different numbering systems (2003)
Er, "the formula you created last year" is a bit vague.

20080314, 18:06 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel & different numbering systems (2003)
Perhaps he meant the UDF in <post:=623,687>post 623,687</post:>
Steve

20080314, 18:14 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel & different numbering systems (2003)
If you use the UDFs that hans created in <post#=623,687>post 623,687</post#>
You could increment a value in A1by 1 using:
<pre>=RIGHT("000000" & to33(from33(A1)+1),6)</pre>
Steve

20080314, 18:16 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Excel & different numbering systems (2003)
Unfortunately I fail to see what that has to do with the question in this thread... <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

20080314, 18:34 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel & different numbering systems (2003)
I couldn't completely either which is why I stuck with trying to directly answer the followup question with the UDF (I think) he alluded to... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
Steve

20080314, 18:57 #9
 Join Date
 Sep 2006
 Location
 Illinois, USA
 Posts
 22
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel & different numbering systems (2003)
You're correct, that's the post I'm refering to. I can increment the value by changing the "+1+" to other another value. I'm trying to create a "schedule" of numbers for each roll of labels to be printed and only need to show a starting and ending number for each roll. So if the next row has a starting number that includes alpha characters (i.e. 00014F), the formula doesn't function any more because the 00014F is not recognized as a number.

20080314, 19:03 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Excel & different numbering systems (2003)
Try the attached version.

20080317, 16:54 #11
 Join Date
 Sep 2006
 Location
 Illinois, USA
 Posts
 22
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel & different numbering systems (2003)
Hi Hans,
I tried this version and it works to calculate the starting and ending # of each roll just fine as long as the number is base 10. I didn't see it calculate anything when I tried a number like 1000F4 (base33). Thanks very much for the efforts but I'm just not skilled enough to figure out how to use your formulas or functions.
 BOB 

20080317, 17:01 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Excel & different numbering systems (2003)
Use the From33 function. See attached version.