How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask flopcat98 Your Own Question

flopcat98
flopcat98, Consultant
Category: Microsoft Office
Satisfied Customers: 7277
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
A new question is answered every 9 seconds

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: 1 year ago.
Category: Microsoft Office
Expert:  flopcat98 replied 1 year 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 1 year 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

 

http://www.youtube.com/watch?v=Yikd9n5shtQ

Customer: replied 1 year ago.

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


 

Customer: replied 1 year ago.


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

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

 

http://www.barcoderesource.com/excelbarcodefont.shtml

Customer: replied 1 year 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 1 year 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 1 year 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 1 year 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 1 year 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 1 year 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: 7277
Experience: degree in comp sci. 20+ years install work with networks for small and large companies
flopcat98 and 2 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year 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!

JustAnswer in the News:

 
 
 
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.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...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
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

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

Related Microsoft Office Questions