• 100% Satisfaction Guarantee
flopcat98, Consultant
Category: Microsoft Office
Satisfied Customers: 9006
Experience:  degree in comp sci. 20+ years install work with networks for small and large companies
23372949
Type Your Microsoft Office Question Here...
flopcat98 is online now

# How do I write a formula to make a serialized number - base

How do I write a formula to make a serialized number - base 31 - in excel.

do you mean converting it to something where ten would be A and 20 would be L 30 would be w and 32 would be 11 ?

I would have to play with it a bunch to make it work. excel is not the best format to do something like that. but it might be possible I dont want to try until i confirm that is what you want to do.

You would basically do it by creating cells for each iteration up as high as you expect the number to go

ie 1 column 31 column 961 column 29791 column etc

then divide the number to see how many times it went it to each column from the highest downwards and use that number for the first column and the remainder gets dropped into the next column to be divided

since you would have have numbers greater than 10 you would put each of them into a lookup table where 10 matched A 11 - B etc and replace that for each column

then concatanate them together.

This post shows basically how to do it. If that is not enough i might be able to do it for you but i would probably have to get back to you tomorow

Customer: replied 4 years ago.

Thanks but this answer is not what I am trying to do.

Customer: replied 4 years ago.

Did you get the other explanation about the actual barcode sequence?

Customer: replied 4 years ago.
Relist: Other.
Didn't really answer my question. Gave me information on something other than what I was looking for.
Customer: replied 4 years ago.

I need to print serialized barcodes reading off an excel spreadsheet. The numbers are base 31 (0-9, A-Z, no vowels). Cell A1 would be MA050000000, Cell A2 would be MA050000001, etc. Maybe this explains what I need better.

so you already have the number in the correct format you just need it printed in barcode format ?

Do you know what barcode type you require ? There are several standard barcodes and many add in programs that will print them.

This company produces several that work as excel add ons

Customer: replied 4 years ago.

I already have a barcode program that I use but I want it to read off an excel spreadsheet. What I need to know is how to write the formula in excel so I can fill each cell for 75,000 numbers - starting with MA050000000 in cell A1, MA050000001 in A2 going through MA050000009 then the next number would be MA05000000B going through MA05000000Z (not using vowels). Then the next number would be MA050000010 through MA050000019.

i would think the way to do it would be to have 31 columns

and do a concatanate of the last digit on the end of each string for each column

and then have the one going down the side increment the same way

are you sure that the barcode program needs them already formatted that way and are you locked into that barcode program because i am pretty sure that there are barcode programs that will convert a base 10 number to the proper barcode even if the barcode itself is in base 31.

If you want to give me a day i will see if i can write it for you. you would have to use string functions rather than numerical ones.

Customer: replied 4 years ago.

I have used this barcode program for a while now. Ususally I set up the program itself to serialize but their tech support is telling me that I would need to know visual basic for this particular sequence and they aren't allowed to show me how. I have printed from excel before (serialized numbers, addresses, etc) but nothing as large as this. I was hoping to be able to just put in a formula and have it fill the entire column so I didn't have to manually enter 75,000 numbers! I googled alphanumeric serialization in excel and got some information but it was code and I am not sure how to get it into excel. Any ideas? Thanks!

base 31 is just not a good base for excel to do easily. It has automatic functions for base 16 base 8 base 2 but thats it.

You would need to either use some sort of vba macro or a bunch of string functions. but once you have them set up it should work. You or i would have to spend an hour setting up the sequence and then it would repeat it self and create them all.

Again, there is probably a barcode program out there that is more designed for the use that you want. This one is obviously designed to take a product number etc and print it. Not to print thousands of barcodes in sequence. Are you locked into that one or can you use a different one. There are several standard barcodes and most of them will take a base ten digit and create a barcode from it directly.

Customer: replied 4 years ago.

I am kind of locked into this program but I will check into some others. This program is made to serialize thousands of numbers in all different formats but but I guess this base 31 is harder for it to do. The vba macro thing is probably the way I need to go. The customer has specified that it needs to be a code 128 base 31 so I can't change that. I can have someone do the vba macro for me - I was just hoping there was an easier way. Thanks for your help anyway!

well its harder to get something to print out base 31 in order because programs dont tend to use base 31 anymore than we do. 128 is a standard barcode but i dont know if there is anything out there that will convert it from a base 10 number i think it would tend to print the number as is and skip the numbers with letters in them.

I could probably write something in another programming language to generate the list and drop the list into excel for you but it would take me a couple of days to do it. If you have somebody that will do the vba code for you that would probably be the way to go. But i am willing to give it a shot if you want me to try and get you back the list at least if not the program. But again i would want a couple of days to find the time to do it.