Bar code numbers to run in a sequential order

[dixiboy]

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 [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]8.9E+19

[/TD]
[/TR]
</tbody>[/TABLE]
below is the sequence i am trying to number. Any help would be appreciated.

[TABLE="width: 188"]
<colgroup><col></colgroup><tbody>[TR]
[TD]89000000000002224578[/TD]
[/TR]
[TR]
[TD]89000000000002224579[/TD]
[/TR]
[TR]
[TD]89000000000002224580[/TD]
[/TR]
[TR]
[TD]89000000000002224581

thank you.

[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

You can do this:

Book1
	B
1	89000000000002224578
2	89000000000002224579
3	89000000000002224580
4	89000000000002224581
Sheet6

Cell Formulas
Range	Formula
B2	=LEFT(B1,LEN(B1)-LEN(RIGHT(B1,15)+1))&RIGHT(B1,15)+1

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

 

[dreid1011]

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.

 

[jtakw]

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

 

[Dryver14]

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?

 

[Dryver14]

Forget my last comment, It was the zeros that was throwing what it showed, I can see it is 15 places now

 

[jtakw]

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

 

[dixiboy]

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

 

[dixiboy]

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

 

[dixiboy]

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.