# Thread: Excel & different numbering systems (2003)

1. ## 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 -

2. ## 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.

3. ## 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.

4. ## Re: Excel & different numbering systems (2003)

Er, "the formula you created last year" is a bit vague.

5. ## Re: Excel & different numbering systems (2003)

Perhaps he meant the UDF in <post:=623,687>post 623,687</post:>

Steve

6. ## 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

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

8. ## 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

9. ## 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.

10. ## Re: Excel & different numbering systems (2003)

Try the attached version.

11. ## 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 -

12. ## Re: Excel & different numbering systems (2003)

Use the From33 function. See attached version.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•