Bar code numbers to run in a sequential order

dixiboy

New Member
Joined
Sep 23, 2016
Messages
7
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


<tbody>
</tbody>
below is the sequence i am trying to number. Any help would be appreciated.

89000000000002224578
89000000000002224579
89000000000002224580
89000000000002224581

thank you.


<colgroup><col></colgroup><tbody>
</tbody>
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Hi,

You can do this:


Book1
B
189000000000002224578
289000000000002224579
389000000000002224580
489000000000002224581
Sheet6
Cell Formulas
RangeFormula
B2=LEFT(B1,LEN(B1)-LEN(RIGHT(B1,15)+1))&RIGHT(B1,15)+1


B1 is manually entered as TEXT, B2 formula copied down.
 
Upvote 0
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.
 
Upvote 0
FYI, my formula in post #2 will let you increment your bar code up to 15 digits from the right.
 
Upvote 0
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?
 
Upvote 0
Forget my last comment, It was the zeros that was throwing what it showed, I can see it is 15 places now
 
Upvote 0
Thank You, Dryver, it will go up to 999999999999999, that's 999 trillion+
 
Upvote 0
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!!
 
Upvote 0
Thank so much for the reply and answer. You have saved me about two weeks worth of keying in the bar-codes. You Rock!!
 
Upvote 0
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.:)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top