VBA for removing extra characters in Barcode scanning

[2KGrafix]

So I created an Excel inventory sheet I would like to scan existing bar codes (part numbers) into. Some codes are QR (data matrix), and some standard industrial bar codes you see on a box of cereal. The issue I'm having is data matrix codes give too much data and standard bar codes either contain spaces or are missing a prefix for some components. For example :

The correct part number is: PCD6MD17E303SBW (All correct part numbers are listed in sheet2 in this format)
Data Matrix Barcode Input: PD6MD 17E30 3SBW S17867999 R7/27/1999 22:30:45 (Missing the C in the prefix which is necessary and contains spaces and extra unwanted data)
Standard Barcode Input: D6MD 17E30 3SBW (Part missing PC prefix altogether and contains spaces)

I have an HTML coding background but not great at writing VBA macros from scratch. I welcome any assistance in finding the right solution.

 

[PeteWright]

@2KGrafix
The problem you will face, no matter what coding language, is that if the scan results in a lack of data (missing characters etc.) you can not bring them back.
For the extra characters to be removed, you'll have a chance, but only if the data is consistent (a rule can be applied) and if they differ no program can fix what the human brain may find.

Back to topic, if you could provide me an example pattern that fits all scans and tell me how the result should look like, I can find a formula or VBA function.

 

[2KGrafix]

@2KGrafix
The problem you will face, no matter what coding language, is that if the scan results in a lack of data (missing characters etc.) you can not bring them back.
For the extra characters to be removed, you'll have a chance, but only if the data is consistent (a rule can be applied) and if they differ no program can fix what the human brain may find.

Back to topic, if you could provide me an example pattern that fits all scans and tell me how the result should look like, I can find a formula or VBA function.

Thank you for replying and trying to take a stab at it.

Here are some true examples of patterns I'm seeing:

L1M36K775BFKW03100530250880 (Should Read: PCL1M36K775BF). Missing "pc" all together w/extra data.

PPCLB5B17C831AF5KBX (Should Read: PCLB5B17C831AF5KBX). There's an extra "p" at the beginning that shouldn't be.

PL1M3 9C675 ACT9940173051168_1E_04 (Should Read: PCL1M39C675AC). Missing the "c" in PC w/extra data.

PLB5B 13B415 AB S10166954 D7/18/1998 0:52:15 (Should Read: PCLB5B13B415AB). Missing the "c" in PC w/extra data.

P1MT-14A303-BAUB (Should Read: PCP1MT14A303BAUB). Missing "PC" w/dashes that should not be included.

I know that's a lot!

PCXXXX000000$$$$$$ = A prefix - BASE - Suffix

ALL parts will have the prefix PC then a BASE that could vary and a suffix that can vary. There should be zero spaces in the part number
As you can see I have different kinds of barcodes that will read all kinds of inconsistent data in the fields. I also have all the correct part numbers in a separate data sheet. I don't know if there's a way to search let's say the first 18-20 characters, identify the closest match, and return ONLY the closest value from the datasheet. AND this formula, if possible would only apply to B15:B35.

Again, I'm a little lost on this one. It's throwing me for a loop. If you could figure it out, I owe you a steak dinner via cash app, lol.

 

[PeteWright]

@2KGrafix
One more question:
Is the number of characters (prefix, base, suffix) fixed?

If the lenght varies, it may be very hard to find a matching pattern.

 

[2KGrafix]

Unfortunately no. I tried to look at some kind of pattern. The only consistent thing I can see is the prefix beginning with PC and 4 characters following. The base can vary between 4-7 characters, but it will always follow a PCXXXX. I think there are too many variables. What I think I will do is create Code128 barcodes for every part, then create a column that categorizes each part, and then create a barcode that when scanned can auto-filter those parts and just let the user scan the correct part from the barcodes I made. So I will probably try writing a code that can auto-filter categories by scan. If you have something like that, it may save me some time. Hey, thanks again for trying to brainstorm this.

 

[PeteWright]

@2KGrafix
That will be tough... I'm thinking to apply a Regular Expression (short RegExp), maybe you've heard of it.
So there are two options I can think of:

1. The text substrings (barcodes) have a fixed number of characters and an identifying start character like "PC"
2. The text substrings (barcodes) vary in length, but have some kind of delimiter (which may be any character or one of many, like " " or "-")

Computers and Programs are stupid, they only follow strict rules, but there may be a huge amount of rules they can handle.
So is there any chance you could find some rules that match the criteria? Having many rules is not a problem, but they should be unique.

Think of the VBA code to do something like this:
Take the text string and start searching for pattern #1, if not found, search for pattern #2 and so on until the last pattern was evaluated.
If no match was found there could be some kind of message to the user like a text "N/A".

 

[2KGrafix]

Okay, I took the time to look at every part number to find any patterns. There are thousands of parts but there are 349 unique patterns that those part numbers fit into. All parts except the ones that begin with MLD should also include PC in front of them. Keep in mind when you scan SOME existing barcode, sometimes it reads PPC (an extra P), or P (no C) in its prefix, or it's missing the prefix altogether. Some have no spaces, some with dashes, and some with no spaces or dashes. For this project, there should be no spaces or dashes. Does this help a little? Seems pretty painstaking.

6627 066100 XXX

8V41 16C618 XX

9502 895051 XX

AU5T 12A647 XX

BB53 17K835 X

CM51 42528 XX

DA8T 19E591 XX

DS7T 15K603 XX

FA1T 19H541 XX

FK7T 14F680 XX

FT4B 8B262 XX

GB5T 14F680 XX

GD9T 19H222 XX

GJ54 8B262 XX

JB3B 15201 XX

JG9A 9F479 XX

JL1T 14F680 XX

JL7B 16K808 XX

JL7T 19J220 XX

JU5T 15C859 XXXXXX

JU5T 15C868 XXXXXX

JX7T 14F679 XX

JX7T 14F680 XX

KK2A 9F479 XX

KT4B R16E11 XXXXXX

L1M3 18D473 XXX

L1M3 6K775 XX

L1M3 7A095 XX

L1M3 8005 XX

L1M3 8125 XX

L1M3 8B204 XX

L1M3 8B273 XXX

L1M3 8B274 XXX

L1M3 8B555 XXX

L1M3 8C351 XXX

L1M3 8C455 XXX

L1M3 8D048 XXX

L1M3 8D094 XX

L1M3 8D098 XX

L1M3 8D099 XX

L1M3 8D101 XX

L1M3 8W015 XX

L1M3 9C675 XX

L1M3 9D188 XX

L1M3 9F823 XX

L1MA 19A286 XX

L1MB 16E116 XXXXXX

L1MB 16E166 XXXXXX

L1MB 8121 XX

L1MB 8122 XX

L1MB 8476 XX

L1MB S109A26 XX

L1MH 19C600 XX

L1MH 19C700 XX

L1MH 19F667 XX

L1MH 19N651 XX

L1MT 13A803 XX

L1TT 14B006 XX

LB5A 16700 XX

LB5B 13B414 XX

LB5B 13B415 XX

LB5B 15A254 XX

LB5B 15A255 XX

LB5B 15K272 XX

LB5B 15K273 XX

LB5B 16C657 XX

LB5B 17C794 XXXXXX

LB5B 17C795 XXXXXX

LB5B 17C831 XXXXXX

LB5B 17E899 XX

LB5B 17E911 XXXXXX

LB5B 17F001 XX

LB5B 17F001 XXXXXX

LB5B 17F765 XXXXXX

LB5B 17F771 XXXXXX

LB5B 8200 XXXXXX

LB5B 8C324 XX

LB5C 5F078 XX

LB5S 17C831 XXXXXX

LB5S 17F001 XXXXXX

LB5T 14F680 XX

LB5T 15A861 XXXXXXX

LB5T 15A862 XXXXXX

LB5T 15A863 XXXXXX

LB5T 15A864 XXXXXXX

LB5T 15C872 XXXXXXX

LB5T 15C873 XXXXXXX

LB5T 15K870 XXXXXXX

LB5T 15K871 XXXXXXX

LB5T 15K872 XXXXXXX

LB5T 15K873 XXXXXXX

LB5T 19J215 XX

LB5T 9G768 XX

LC5B 13E014 XX

LC5B 13E015 XX

LC5B 15A226 XX

LC5B 15A227 XX

LC5B 15A245 XX

LC5B 15A246 XX

LC5B 15A254 XX

LC5B 15A255 XX

LC5B 15A424 XX

LC5B 15A425 XX

LC5B 15K272 XX

LC5B 15K273 XX

LC5B 17A385 XXXXXX

LC5B 17A754 XXXXXX

LC5B 17A848 XX

LC5B 17B635 XXXXXX

LC5B 17B938 XX

LC5B 17B939 XX

LC5B 17C794 XXXXXX

LC5B 17C795 XXXXXX

LC5B 17C831 XXXXXX

LC5B 17E847 XX

LC5B 17E898 XX

LC5B 17E899 XX

LC5B 17E911 XXXXXX

LC5B 17F000 XXXXXX

LC5B 17F001 XXXXXX

LC5B 17F011 XXXXXX

LC5B 17F954 XXXXXX

LC5B 17K646 XX

LC5B 17K945 XXXXXX

LC5B 17K946 XXXXXX

LC5B 17K947 XXXXXX

LC5B 19H548 XX

LC5B 403B98 XX

LC5B 403B99 XX

LC5B 8200 XXXXXX

LC5B 8C324 XX

LC5B 8K141 XXXXXX

LC5T 14F680 XX

LC5T 15A861 XX

LC5T 15A862 XX

LC5T 15A863 XX

LC5T 15A864 XX

LC5T 15C872 XXXXXX

LC5T 15C873 XXXXXX

LC5T 15K870 XXXXXX

LC5T 15K871 XXXXXX

LC5T 15K872 XXXXXX

LC5T 15K873 XXXXXX

LU5T 14G618 XX

M1M3 8W005 XXX

M1M3 9647 XX

M1M3 9E635 XX

M1PB 515 XXXX

MB5B 13W029 XX

MB5B 13W030 XX

MB5B 15A298 XXXXXX

MB5B 15A299 XXXXXX

MB5B 17A894 XXXXXX

MB5B 17A895 XXXXXX

MB5B 17C831 XXXXXX

MB5B 17E899 XX

MB5B 17F765 XXXXXX

MB5B 403F55 XX

MB5B 8200HG5YZ9 XX

MB5B 8A034 XXXXXX

MB5B 8C324 XX

MB5B 8C324 XXXXXX

MB5J 17E911 XXXXXX

MB5J 17F765 XXXXXX

MB5J 17F771 XXXXXX

MB5J 8200 XXXXXX

MC5B 8K141 XXXXXX

MEL1 8175UV XXX

MLDL1MB 105B00 BB

MLDL1MB 14C022 AB

MLDL1MB 19892 AA

MLDL1MB 8472 CA

MLDL1MB 8472 DA

MLDL1MB 8472 EA

MLDL1MB 8472 FA

MLDL1MB 8472 GA

MLDL1MB 8472 HA

MLDL1MB 8472 JA

MLDL1MB 8472 KA

MLDL1MB 8472 LA

MLDL1MB 8472 MA

MLDLB5B 15A245 AC

MLDLB5B 15A246 AC

MLDLB5B 15A298 AC5YZ9

MLDLB5B 15A298 AD5YZ9

MLDLB5B 15A299 AC5YZ9

MLDLB5B 15A299 AD5YZ9

MLDLB5B 17A385 AB5YZ9

MLDLB5B 17A385 BA5YZ9

MLDLB5B 17A385 BB5YZ9

MLDLB5B 17A385 DC5YZ9

MLDLB5B 17A385 EB5YZ9

MLDLB5B 17B810 AB5YZ9

MLDLB5B 17B810 CB5YZ9

MLDLB5B 17D958 AD

MLDLB5B 17D959 AD

MLDLB5B 17E899 AC

MLDLB5B 17E899 BB

MLDLB5B 17F011 AE5YZ9

MLDLB5B 17F011 BC5YZ9

MLDLB5B 17F775 AE5YZ9

MLDLB5B 17F775 BE5YZ9

MLDLB5B 17F775 CC5YZ9

MLDLB5B 17F775 DA5YZ9

MLDLB5B 17F954 AE5YZ9

MLDLB5B 17F954 BE5YZ9

MLDLB5B 17F954 CF5YZ9

MLDLB5B 17F954 DE5YZ9

MLDLB5B 17F954 DF5YZ9

MLDLB5B 17F954 EF5YZ9

MLDLB5B 17F954 FD5YZ9

MLDLB5B 17F954 GD5YZ9

MLDLB5B 17F954 HB5YZ9

MLDLB5B 17K922 AC5YZ9

MLDLB5B 278G08 AC

MLDLB5B 278G09 AC

MLDLB5B 8312 AB

MLDLB5B 8312 CA

MLDLB5B 8314 AA

MLDLB5B 8314 AB

MLDLB5B 8314 AC

MLDLB5B 8314 BA

MLDLB5B 8A164 AD

MLDLC5B 13A078 AB

MLDLC5B 13A079 AB

MLDLC5B 17B635 AC5YBT

MLDLC5B 17D958 AC

MLDLC5B 17D959 AC

MLDLC5B 17F779 AB

MLDLC5B 17F954 AB5YBT

MLDLC5B 17F954 AD5YBT

MLDLC5B 17F954 BE5YBT

MLDLC5B 8312 AA

MLDLC5B 8314 AA

MLDLC5B 8A164 AD

MLDLC5B 8A200 AD

MLDM1MB 105B00 EB

MLDMB5B 01896AA R2A9

MLDMB5B 01897AA R2A9

MLDMB5B 17A385 GA5YZ9

MLDMB5B 17F775 DB5YZ9

MLDMB5B 17F775 DC5YZ9

MLDMB5B 17F954 HB5YZ9

MLDMB5B 8312 AA

MLDMB5B 8314 AC

MLDRB5B 17A385 AA5YZ9

MLDRB5B 17A385 BA5YZ9

MLDRB5B 17F011 AA5YZ9

MLDRB5B 17F775 AB5YZ9

MLDRB5B 17F775 BA5YZ9

MLDRB5B 8312 AA

MLDRB5B 8314 AA

MLDRB5B 8314 CA

MLDRB5BS 278G08 AA

MLDRB5BS 278G09 AA

MLDRC5B 17B635 AA5YBT

MLDRC5B 17F011 AA

MLDRC5B 8312 AA

MLDRC5B 8314 AA

MLDRC5B 8A200 BA

MLDRC5B 8A200 BB

N806 830S XXX

NB5B 8200 XXXXXX

NC5B 8200 XXXXXX

NU5T 14G618 XX

NU5T 15K859 XXXXXX

P1M3 6A960 XXX

P1M3 8C607 XX

P1M3 8D048 XXX

P1M3 8D050 XXX

P1M3 8D094 XX

P1MP 7R081 XX

P1MT 14A303 XXXX

P1MT 14N139 XXXX

P1MT 15K867 XXX

PV4A 9F479 XX

R1M3 6K775 XX

R1M3 8B273 XXX

R1M3 8W005 XXX

R1M3 9D188 XX

R1M3 9E635 XX

R1MB S109A26 XX

R1MT 14A303 XXXX

R1MT 14N139 XXXX

R1MT 15K867 XXX

RB5B 13W029 XX

RB5B 13W030 XX

RB5B 15266 XX

RB5B 15267 XX

RB5B 15A245 XXXXXX

RB5B 15A246 XXXXXX

RB5B 15A254 XX

RB5B 15A298 XXXXXX

RB5B 15A299 XXXXXX

RB5B 17C831 XXXXXX

RB5B 17F765 XXXXXX

RB5B 17F771 XXXXXX

RB5B 8200 XXXXXX

RB5B 8C324 XX

RB5T 15A861 XXXXXXX

RB5T 15A862 XXXXXXX

RB5T 15A863 XXXXXXX

RB5T 15A864 XXXXXXX

RB5T 9G853 XX

RC5B 13E014 XX

RC5B 13E015 XX

RC5B 15A226 XX

RC5B 15A227 XX

RC5B 15A245 XX

RC5B 15A245 XXXXXX

RC5B 15A246 XX

RC5B 15A246 XXXXXX

RC5B 17B635 XXXXXX

RC5B 17C831 XXXXXX

RC5B 17F011 XXXXXX

RC5B 17F788 XXXXXX

RC5B 17K945 XXXXXX

RC5B 17K946 XXXXXX

RC5B 17K947 XXXXXX

RC5B 8200 XXXXXX

RC5B 8C324 XXXXXX

RC5T 15A861 XX

RC5T 15A862 XX

RC5T 15A863 XX

RC5T 15A864 XX

W500 XXXXXXX

W502 XXXXXXX

W505 XXXXXXX

W506 XXXXXXX

W506 XXXXXXXX

W520 XXXXXXX

W520 XXXXXXXXX

W700 XXXXXXX

W702 XXXXXXX

W704 XXXXXXX

W705 XXXXXXX

W706 XXXXXXX

W707 XXXXXXX

W709 XXXXXXX

W709 XXXXXXX

W711 XXXXXXX

W712 XXXXXXXX

W714 XXXXXXX

W716 19XXXXXX

W716 XXXXXXX

W717 XXXXXXX

W718 XXXXXXX

W719 XXXXXXX

W720 XXXXXXX

 

[2KGrafix]

I broke apart the 349 unique patterns except we wouldn't want the spaces, and the "PC" I removed altogether.

 

[PeteWright]

Hi @2KGrafix
By "unique pattern" I was thinking of a Regular Expression Pattern rather than a text pattern, so those 349 text patterns can be put in a few RegExp Patterns if - and only if - there is some kind of delimiter (may it be a the position or value of a character).

Let me try to explain with your previous examples.

L1M36K775BFKW03100530250880 (Should Read: PCL1M36K775BF). Missing "pc" all together w/extra data.

-> no pattern​

​

PPCLB5B17C831AF5KBX (Should Read: PCLB5B17C831AF5KBX). There's an extra "p" at the beginning that shouldn't be.

-> pattern PC[A-Z0-9]+ would match PCLB5B17C831AF5KBX​

​

PL1M3 9C675 ACT9940173051168_1E_04 (Should Read: PCL1M39C675AC). Missing the "c" in PC w/extra data.

-> pattern [A-Z0-9]{4} [A-Z0-9]+ [A-Z0-9]{2} would match L1M3 9C675 AC​

PLB5B 13B415 AB S10166954 D7/18/1998 0:52:15 (Should Read: PCLB5B13B415AB). Missing the "c" in PC w/extra data.

-> pattern [A-Z0-9]{4} [A-Z0-9]+ [A-Z0-9]{2} would match LB5B 13B415 AB​

P1MT-14A303-BAUB (Should Read: PCP1MT14A303BAUB). Missing "PC" w/dashes that should not be included.

-> pattern [A-Z0-9]{4}-[A-Z0-9]+-[A-Z0-9]+ would match P1MT-14A303-BAUB​

I hope that you'll get a better understanding now.

So taking that into account, do you think your data may fit into a few patterns?

 

[2KGrafix]

Hi @2KGrafix
By "unique pattern" I was thinking of a Regular Expression Pattern rather than a text pattern, so those 349 text patterns can be put in a few RegExp Patterns if - and only if - there is some kind of delimiter (may it be a the position or value of a character).

Let me try to explain with your previous examples.

L1M36K775BFKW03100530250880 (Should Read: PCL1M36K775BF). Missing "pc" all together w/extra data.

-> no pattern​

​

PPCLB5B17C831AF5KBX (Should Read: PCLB5B17C831AF5KBX). There's an extra "p" at the beginning that shouldn't be.

-> pattern PC[A-Z0-9]+ would match PCLB5B17C831AF5KBX​

​

PL1M3 9C675 ACT9940173051168_1E_04 (Should Read: PCL1M39C675AC). Missing the "c" in PC w/extra data.

-> pattern [A-Z0-9]{4} [A-Z0-9]+ [A-Z0-9]{2} would match L1M3 9C675 AC​

PLB5B 13B415 AB S10166954 D7/18/1998 0:52:15 (Should Read: PCLB5B13B415AB). Missing the "c" in PC w/extra data.

-> pattern [A-Z0-9]{4} [A-Z0-9]+ [A-Z0-9]{2} would match LB5B 13B415 AB​

P1MT-14A303-BAUB (Should Read: PCP1MT14A303BAUB). Missing "PC" w/dashes that should not be included.

-> pattern [A-Z0-9]{4}-[A-Z0-9]+-[A-Z0-9]+ would match P1MT-14A303-BAUB​

I hope that you'll get a better understanding now.

So taking that into account, do you think your data may fit into a few patterns?

Yes, more than not they would fit within those expression patterns you mentioned. Just looking at those examples that may work. I would just need to test a few scans to see. Would you be willing to write a code for the patterns you mentioned above? Thank you.