Latest excel headache

Excel Ranger

Board Regular
Joined
Oct 31, 2005
Messages
51
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! :confused:
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
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

Board Regular
Joined
Oct 31, 2005
Messages
51
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

Well-known Member
Joined
Dec 15, 2005
Messages
818

ADVERTISEMENT

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

Board Regular
Joined
Oct 31, 2005
Messages
51
(y) 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.
:biggrin:
 

Excel Ranger

Board Regular
Joined
Oct 31, 2005
Messages
51
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,264
Messages
5,577,076
Members
412,763
Latest member
sienweiw
Top