Latest excel headache

[Excel Ranger]

I'm utilizing an older COMPAQ H3900 iPAQ with a SYMBOL SPS3000 Scanner expansion pack for tracking issues or inventory in a warehouse. I'm running on Windows XP with Office 2003. I have the VLOOKUPs all set up so when I scan a barcode, it drops the value in the A column. The other column values (i.e. stock number, manufacturer, serial number, price, shelf location, etc.) autofill across the other columns based on the VLOOKUP of the scanned value, based on the inventory sheet on a seperate tab. The problem that I am having is that I'm dealing with 2 barcode formats. The older system had a letter (A-L) plus 4 numbers (example L1234). We recently switched over to a new logistics system which uses a 6-digit barcode instead of the older 5-digit format. In the interim, a zero was placed at the beginning of the older numbers, so L1234 became 0L1234. The new system is gradually replacing all of the stickers, so eventually it will be 6 numbers, instead of a letter plus four numbers. Now what I'm trying to do is set the formatting to read a number like L1234 as 0L1234. The second part of the problem is that the new stickers, when read, incorporate an accounting code with the new number. So when I scan a new sticker, it might read 000789WK4FN. I just need the first 6 characters.
Anybody care to tackle this one? No VB, please. I had enough trouble with C++ and Java!

 

[SteveO59L]

For the first, could try

="0"&a1

for the second,

=left(a1,6)

 

[lozzablake]

You are looking up against a table with L1234 so you need to lose the 0?

so you do an if statement based on the length of the code:

if(len(A1)=6,vlookup(right(A1,5),range,cols,false), vlookup(left(a1,6),etc))

 

[Excel Ranger]

Thanks for the info. Is there anyway to incorporate all of it into a function string? In order for the VLOOKUP to work, all of the numbers have to be in the 6-character format, which is the way the 2000+ line inventory sheet is formatted.

 

[lozzablake]

just change the if statement. I assume you need to add the zero rather than remove it so:



if(len(A1)=5,vlookup("0"&A1,range,cols,false), vlookup(left(a1,6),etc))

 

[Excel Ranger]

THANK YOU!!!
Tweaked it a bit, not it works great. I was hoping that the inputted/scanned value would show up in the desired 6-character format in col A, but I can live with it. The rest works out great.

 

[Excel Ranger]

=IF(LEN($A2)=5,VLOOKUP("0"&$A2,'20051117'!$A:$J,2,FALSE),VLOOKUP(LEFT($A2,6),'20051117'!$A:$I,2,FALSE)) is how it ended up.