Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Bar code numbers to run in a sequential order

  1. #1
    New Member
    Join Date
    Sep 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Bar code numbers to run in a sequential order

    Good Day, I am trying to create a sequence of bar code number in Excel that I can drag down to run in sequential order. I would need the last digit to increase by one number as I drag it. I cannot seem to get it to work as it does not run in sequence. I converted the number to text because it reflected as
    8.9E+19
    below is the sequence i am trying to number. Any help would be appreciated.

    89000000000002224578
    89000000000002224579
    89000000000002224580
    89000000000002224581

    thank you.

  2. #2
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    3,534
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bar code numbers to run in a sequential order

    Hi,

    You can do this:

    B
    189000000000002224578
    289000000000002224579
    389000000000002224580
    489000000000002224581

    Sheet6



    Worksheet Formulas
    CellFormula
    B2=LEFT(B1,LEN(B1)-LEN(RIGHT(B1,15)+1))&RIGHT(B1,15)+1



    B1 is manually entered as TEXT, B2 formula copied down.

  3. #3
    Board Regular
    Join Date
    Jun 2015
    Location
    Tillamook, OR
    Posts
    1,958
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Bar code numbers to run in a sequential order

    Welcome to the board.

    Here is one potential solution:

    With your starting barcode number placed in a cell formatted as Text, use the following formula and copy down. The formula cells need to be formatted as General.

    ="8900000000000"&INT(RIGHT(A1,7))+1

    I placed the first number in A1, and the formula in A2 and down. This will account for 20 digit barcode numbers until the number passes 89000000000009999999. The formula can be modified to be more dynamic if you desire.
    Win 7:MS Excel 2010

    Excel Worksheet Functions (TechOnTheNet)
    Excel Worksheet Functions (ExcelJet) Excel Formulas (ExcelJet)

    Post a copy of your data with one of the following methods:
    Excel Jeanie, MrExcel HTML Maker, Tableizer!

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    3,534
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bar code numbers to run in a sequential order

    FYI, my formula in post #2 will let you increment your bar code up to 15 digits from the right.

  5. #5
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    2,391
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Bar code numbers to run in a sequential order

    Jtakw,

    Love this, don't understand it. As I run it through the formula evaluator it seems to add one to the last 8 "digits" of the string, is this correct?

    if so would this fail on the 100 millionth bar code?
    I visit this site mainly to remember how little I know

  6. #6
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    2,391
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Bar code numbers to run in a sequential order

    Forget my last comment, It was the zeros that was throwing what it showed, I can see it is 15 places now
    I visit this site mainly to remember how little I know

  7. #7
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    3,534
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bar code numbers to run in a sequential order

    Thank You, Dryver, it will go up to 999999999999999, that's 999 trillion+

  8. #8
    New Member
    Join Date
    Sep 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bar code numbers to run in a sequential order

    Thank so much for the reply and answer. You have really saved me about two weeks worth of keying in the bar-codes. You rock!!

  9. #9
    New Member
    Join Date
    Sep 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bar code numbers to run in a sequential order

    Thank so much for the reply and answer. You have saved me about two weeks worth of keying in the bar-codes. You Rock!!

  10. #10
    New Member
    Join Date
    Sep 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bar code numbers to run in a sequential order

    Thank you jtakw and dreid1011 for your replies to my post. Absolutely thrilled with the results and this will save me a couple of weeks worth of keying entries. Really indicates to me how little i know of Excel. Thank goodness for this site.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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