• 100% Satisfaction Guarantee

flopcat98, Consultant
Category: Microsoft Office
Satisfied Customers: 7883
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

Resolved Question:

How do I write a formula to make a serialized number - base 31 - in excel.
Submitted: 2 years ago.
Category: Microsoft Office
Expert:  flopcat98 replied 2 years ago.

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.

Expert:  flopcat98 replied 2 years ago.

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 2 years ago.

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

Customer: replied 2 years ago.

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

Customer: replied 2 years ago.
Relist: Other.
Didn't really answer my question. Gave me information on something other than what I was looking for.
Customer: replied 2 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.

Expert:  flopcat98 replied 2 years ago.

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

Expert:  flopcat98 replied 2 years ago.

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 2 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!

Expert:  flopcat98 replied 2 years ago.

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 2 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!

Expert:  flopcat98 replied 2 years ago.

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.

flopcat98, Consultant
Category: Microsoft Office
Satisfied Customers: 7883
Experience: degree in comp sci. 20+ years install work with networks for small and large companies
Customer: replied 2 years ago.

Thanks for offering but I think I can get someone to do it for me (I hope he will be able to get it to work). Thanks for all your help anyway!

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

What Customers are Saying:

• My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
• My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
• Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
• This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
• Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
• I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
• Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
• Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland

• jstinehelfer

Satisfied Customers:

36
A+ Comptia Certified computer repair
< Last | Next >

jstinehelfer

Satisfied Customers:

36
A+ Comptia Certified computer repair

JasonJames122

Satisfied Customers:

0
I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business

The-PC-Guy

Satisfied Customers:

366
20 years experience providing remote computer support

Jess M.

Satisfied Customers:

359
Computer Software Specialist for more than 10 years

Chris L.

Satisfied Customers:

134
Certified Expert with over 10 years experience.

132

IT Miro

Satisfied Customers:

114
Bachelor's Degree in Information Technology, Microsoft Certified Professional

Related Microsoft Office Questions

Chat Now With A Microsoft Office Technician
flopcat98
117 Satisfied Customers
degree in comp sci. 20+ years install work with networks for small and large companies