Sequence Formula
 

Hey guys I'm having a bit of trouble with a formula for barcodes.

I'm trying to get a sequence eg.

eg I will scan this barcode: HD65B1164281P
and it will go up like this HD65B1164282P
283P

for ten barcodes then i will scan another barcode and it needs to go
into another sequence for 9 barcodes....etc

Any help would be appreciated.


--
asokol
------------------------------------------------------------------------
asokol's Profile: http://www.excelforum.com/member.php...o&userid=29485
View this thread: http://www.excelforum.com/showthread...hreadid=491876

Sequence Formula
 

asokol,
I assume your codes are in the following format:
LLDDLDDDDDDDL,
i.e. fixed length and the letters (L) are always in the same position,
then, if the first code is in, say A1, the following formula will
generate the next element in the sequence:
=left(A1, 9) & mid(A1, 10, 3)+1 & right(A1,1)

HTH
Kostis Vezerides

Sequence Formula
 

Assuming your first scan was in A1, second in A2, etc.......... put this in
B1 and copy over to the right and down as reqired

=LEFT(A1,5)&MID(A1,6,7)*1+1&RIGHT(A1,1)

Vaya con Dios,
Chuck, CABGx3



"asokol" wrote:


Hey guys I'm having a bit of trouble with a formula for barcodes.

I'm trying to get a sequence eg.

eg I will scan this barcode: HD65B1164281P
and it will go up like this HD65B1164282P
283P

for ten barcodes then i will scan another barcode and it needs to go
into another sequence for 9 barcodes....etc

Any help would be appreciated.


--
asokol
------------------------------------------------------------------------
asokol's Profile: http://www.excelforum.com/member.php...o&userid=29485
View this thread: http://www.excelforum.com/showthread...hreadid=491876

Sequence Formula
 

One method since you have the P on the end of the code.

Enter HD65B1164281 in A1 then right-click and drag the fill handle down to
A10.

Release and select "fill series"

Your numbers will increment.

Do this for each set of 10 codes.

Now in B1 enter =A1 & "P"

Left-click and drag down.

Select the Column B range and Paste Special(in place)ValuesOKEsc.

Delete column A


Gord Dibben Excel MVP


On Thu, 8 Dec 2005 10:33:14 -0600, asokol
wrote:


eg I will scan this barcode: HD65B1164281P

Sequence Formula
 

Thanks for the help guys...

Another question relating to this, what would i need to add to the
formula so that for example i scan the first barcode in a1 the cursor
then jumps down to a11 scan that then jumps to a21 etc.. etc..

cheers for the help


--
asokol
------------------------------------------------------------------------
asokol's Profile: http://www.excelforum.com/member.php...o&userid=29485
View this thread: http://www.excelforum.com/showthread...hreadid=491876