VBA for removing extra characters in Barcode scanning

2

2KGrafix

New Member
Joined
Jan 26, 2024
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Sort by date Sort by votes
@2KGrafix I'll code something later back home.

Just one thing, those codes you mentioned before in post #7 -> what are all those "X"-es for? Are that placeholders meaning any character can fit in?
Please confirm that and I'll take a look at it later.

Could you share another table with the actual codes? (not X-es, but the real characters). That would be easier for me for coding the VBA.
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
I sent you a message with more info. take a look. We can return to the thread to share the code with the group if that works for you. Thank you again.
 
Upvote 0
Please do not share anything privately, everything must remain on the board for all to see as per rule#4
 
Upvote 0
Fluff said:
Please do not share anything privately, everything must remain on the board for all to see as per rule#4
Click to expand...
No problem. I can do that. I was trying to avoid the tread from being cluttered with simple back-and-forth questions but I understand.
 
Upvote 0
This I sent in your messages as well:

See the list below. To answer your question in the thread. Yes, the "X" are placeholders for a variety of parts. For example, take a look at the first part number. One of the actual part numbers is on the left. However, the last three characters could represent a 406 or 407 version of that part. All 349 part numbers in reply 7 are either unique prefixes or bases. The suffix could change but will always have the number of characters represented by the "X", no more, no less, and each one will have zero spaces or dashes. The complete list of parts is about four times as long. I don't know if you would need all of them. Let me know. Thank you again, you're helping me out a lot here.

PC66270661004056627 066100 XXX
PC8V4116C618BE8V41 16C618 XX
PC95028950516R9502 895051 XX
PCAU5T12A647ACAU5T 12A647 XX
PCBB5317K835ABB53 17K835 X
PCCM5142528BACM51 42528 XX
PCDA8T19E591BADA8T 19E591 XX
PCDS7T15K603AADS7T 15K603 XX
PCFA1T19H541AAFA1T 19H541 XX
PCFK7T14F680CAFK7T 14F680 XX
PCFT4B8B262ACFT4B 8B262 XX
PCGB5T14F680DAGB5T 14F680 XX
PCGD9T19H222AGGD9T 19H222 XX
PCGJ548B262AAGJ54 8B262 XX
PCJB3B15201ACJB3B 15201 XX
PCJG9A9F479ABJG9A 9F479 XX
PCJL1T14F680DAJL1T 14F680 XX
PCJL7B16K808AAJL7B 16K808 XX
PCJL7T19J220DDJL7T 19J220 XX
PCJU5T15K859AC5HNVJU5T 15C859 XXXXXX
PCJU5T15C868AC5HNVJU5T 15C868 XXXXXX
PCJX7T14F679XAJX7T 14F679 XX
PCJX7T14F680DAJX7T 14F680 XX
PCKK2A9F479ABKK2A 9F479 XX
PCKT4BR16E116APIA3KT4B R16E11 XXXXXX
PCL1M318D473FCCL1M3 18D473 XXX
PCL1M36K775BFL1M3 6K775 XX
PCL1M37A095ADL1M3 7A095 XX
PCL1M38005AFL1M3 8005 XX
PCL1M38125AAL1M3 8125 XX
PCL1M38B204ABL1M3 8B204 XX
PCL1M38B273AAEL1M3 8B273 XXX
PCL1M38B274BAHL1M3 8B274 XXX
PCL1M38B555FCFL1M3 8B555 XXX
PCL1M38C351FCFL1M3 8C351 XXX
PCL1M38C455FAAL1M3 8C455 XXX
PCL1M38D048PBCL1M3 8D048 XXX
PCL1M38D094ABL1M3 8D094 XX
PCL1M38D098EAL1M3 8D098 XX
PCL1M38D099EAL1M3 8D099 XX
PCL1M38D101AAL1M3 8D101 XX
PCL1M38W015FFL1M3 8W015 XX
PCL1M39C675ACL1M3 9C675 XX
PCL1M39D188BAL1M3 9D188 XX
PCL1M39F823ADL1M3 9F823 XX
PCL1MA19A286ACL1MA 19A286 XX
PCL1MB16E116APIA12L1MB 16E116 XXXXXX
PCL1MB16E166APIA02L1MB 16E166 XXXXXX
PCL1MB8121AAL1MB 8121 XX
PCL1MB8122AAL1MB 8122 XX
PCL1MB8476AAL1MB 8476 XX
PCL1MBS109A26ABL1MB S109A26 XX
PCL1MH19C600AFL1MH 19C600 XX
PCL1MH19C700BCL1MH 19C700 XX
PCL1MH19F667ABL1MH 19F667 XX
PCL1MH19N651BBL1MH 19N651 XX
PCL1MT13A803ABL1MT 13A803 XX
PCL1TT14B006AAL1TT 14B006 XX
PCLB5A16700ACLB5A 16700 XX
PCLB5B13B414ABLB5B 13B414 XX
PCLB5B13B415ABLB5B 13B415 XX
PCLB5B15A254AFLB5B 15A254 XX
PCLB5B15A255AFLB5B 15A255 XX
PCLB5B15K272ACLB5B 15K272 XX
PCLB5B15K273ACLB5B 15K273 XX
PCLB5B16C657ADLB5B 16C657 XX
PCLB5B17C794AB5UAWLB5B 17C794 XXXXXX
PCLB5B17C795AB5UAWLB5B 17C795 XXXXXX
PCLB5B17C831AFPRAALB5B 17C831 XXXXXX
PCLB5B17E899CBLB5B 17E899 XX
PCLB5B17E911CASMASLB5B 17E911 XXXXXX
PCLB5B17F001AEPRAALB5B 17F001 XX
PCLB5B17F001AG5KBXLB5B 17F001 XXXXXX
PCLB5B17F765AD5JP4LB5B 17F765 XXXXXX
PCLB5B17F771AB5JP4LB5B 17F771 XXXXXX
PCLB5B8200AB5YZ9LB5B 8200 XXXXXX
PCLB5B8C324AALB5B 8C324 XX
PCLB5C5F078AFLB5C 5F078 XX
PCLB5S17C831AF2323LB5S 17C831 XXXXXX
PCLB5S17F001BE2323LB5S 17F001 XXXXXX
PCLB5T14F680AALB5T 14F680 XX
PCLB5T15A861A1A5YZ9LB5T 15A861 XXXXXXX
PCLB5T15A862AAPRAALB5T 15A862 XXXXXX
PCLB5T15A863AAPRAALB5T 15A863 XXXXXX
PCLB5T15A864A1A5YZ9LB5T 15A864 XXXXXXX
PCLB5T15C872B1A5YZ9LB5T 15C872 XXXXXXX
PCLB5T15C873B1A5YZ9LB5T 15C873 XXXXXXX
PCLB5T15K870A1A5YZ9LB5T 15K870 XXXXXXX
PCLB5T15K871A1A5YZ9LB5T 15K871 XXXXXXX
PCLB5T15K872A1A5YZ9LB5T 15K872 XXXXXXX
PCLB5T15K873A1A5YZ9LB5T 15K873 XXXXXXX
PCLB5T19J215BCLB5T 19J215 XX
PCLB5T9G768ABLB5T 9G768 XX
PCLC5B13E014CNLC5B 13E014 XX
PCLC5B13E015CNLC5B 13E015 XX
PCLC5B15A226ACLC5B 15A226 XX
PCLC5B15A227ACLC5B 15A227 XX
PCLC5B15A245ABLC5B 15A245 XX
PCLC5B15A246ABLC5B 15A246 XX
PCLC5B15A254ADLC5B 15A254 XX
PCLC5B15A255ADLC5B 15A255 XX
PCLC5B15A424BDLC5B 15A424 XX
PCLC5B15A425BDLC5B 15A425 XX
PCLC5B15K272ABLC5B 15K272 XX
PCLC5B15K273ABLC5B 15K273 XX
PCLC5B17A385AB5YZ9LC5B 17A385 XXXXXX
PCLC5B17A754AASMASLC5B 17A754 XXXXXX
PCLC5B17A848ABLC5B 17A848 XX
PCLC5B17B635BCPRAALC5B 17B635 XXXXXX
PCLC5B17B938ACLC5B 17B938 XX
PCLC5B17B939ACLC5B 17B939 XX
PCLC5B17C794AASMASLC5B 17C794 XXXXXX
PCLC5B17C795AASMASLC5B 17C795 XXXXXX
PCLC5B17C831AEPRAALC5B 17C831 XXXXXX
PCLC5B17E847ABLC5B 17E847 XX
PCLC5B17E898ACLC5B 17E898 XX
PCLC5B17E899ACLC5B 17E899 XX
PCLC5B17E911AASMASLC5B 17E911 XXXXXX
PCLC5B17F000AB5YBTLC5B 17F000 XXXXXX
PCLC5B17F001AHPRAALC5B 17F001 XXXXXX
PCLC5B17F011ACPRAALC5B 17F011 XXXXXX
PCLC5B17F954CEPRAALC5B 17F954 XXXXXX
PCLC5B17K646ABLC5B 17K646 XX
PCLC5B17K945AD59B8LC5B 17K945 XXXXXX
PCLC5B17K946ABSMASLC5B 17K946 XXXXXX
PCLC5B17K947ABSMASLC5B 17K947 XXXXXX
PCLC5B19H548AALC5B 19H548 XX
PCLC5B403B98AALC5B 403B98 XX
PCLC5B403B99AALC5B 403B99 XX
PCLC5B8200AESMA4LC5B 8200 XXXXXX
PCLC5B8C324CBLC5B 8C324 XX
PCLC5B8K141AASMASLC5B 8K141 XXXXXX
PCLC5T14F680AALC5T 14F680 XX
PCLC5T15A861AALC5T 15A861 XX
PCLC5T15A862AALC5T 15A862 XX
PCLC5T15A863AALC5T 15A863 XX
PCLC5T15A864AALC5T 15A864 XX
PCLC5T15C872AAPRAALC5T 15C872 XXXXXX
PCLC5T15C873AAPRAALC5T 15C873 XXXXXX
PCLC5T15K870AAPRAALC5T 15K870 XXXXXX
PCLC5T15K871AAPRAALC5T 15K871 XXXXXX
PCLC5T15K872AAPRAALC5T 15K872 XXXXXX
PCLC5T15K873AAPRAALC5T 15K873 XXXXXX
PCLU5T14G618ADLU5T 14G618 XX
PCM1M38W005AAAM1M3 8W005 XXX
PCM1M39647CAM1M3 9647 XX
PCM1M39E635BAM1M3 9E635 XX
PCM1PB515B0AAM1PB 515 XXXX
PCMB5B13W029PFMB5B 13W029 XX
PCMB5B13W030PFMB5B 13W030 XX
PCMB5B15A298BA5YZ9MB5B 15A298 XXXXXX
PCMB5B15A299BA5YZ9MB5B 15A299 XXXXXX
PCMB5B17A894AD5KBXMB5B 17A894 XXXXXX
PCMB5B17A895AD5KBXMB5B 17A895 XXXXXX
PCMB5B17C831BC5KBXMB5B 17C831 XXXXXX
PCMB5B17E899DDMB5B 17E899 XX
PCMB5B17F765LA5UAWMB5B 17F765 XXXXXX
PCMB5B403F55JAMB5B 403F55 XX
PCMB5B8200HG5YZ9MB5B 8200HG5YZ9 XX
PCMB5B8A034AAS2ADMB5B 8A034 XXXXXX
PCMB5B8C324EAMB5B 8C324 XX
PCMB5B8C324GA5UAWMB5B 8C324 XXXXXX
PCMB5J17E911AA5UAWMB5J 17E911 XXXXXX
PCMB5J17F765AA5UAWMB5J 17F765 XXXXXX
PCMB5J17F771AA51MDMB5J 17F771 XXXXXX
PCMB5J8200AA51MDMB5J 8200 XXXXXX
PCMC5B8K141BASMV7MC5B 8K141 XXXXXX
PCMEL18175UVB20MEL1 8175UV XXX
MLDL1MB105B00BBMLDL1MB 105B00 BB
MLDL1MB14C022ABMLDL1MB 14C022 AB
MLDL1MB19892AAMLDL1MB 19892 AA
MLDL1MB8472CAMLDL1MB 8472 CA
MLDL1MB8472DAMLDL1MB 8472 DA
MLDL1MB8472EAMLDL1MB 8472 EA
MLDL1MB8472FAMLDL1MB 8472 FA
MLDL1MB8472GAMLDL1MB 8472 GA
MLDL1MB8472HAMLDL1MB 8472 HA
MLDL1MB8472JAMLDL1MB 8472 JA
MLDL1MB8472KAMLDL1MB 8472 KA
MLDL1MB8472LAMLDL1MB 8472 LA
MLDL1MB8472MAMLDL1MB 8472 MA
MLDLB5B15A245ACMLDLB5B 15A245 AC
MLDLB5B15A246ACMLDLB5B 15A246 AC
MLDLB5B15A298AC5YZ9MLDLB5B 15A298 AC5YZ9
MLDLB5B15A298AD5YZ9MLDLB5B 15A298 AD5YZ9
MLDLB5B15A299AC5YZ9MLDLB5B 15A299 AC5YZ9
MLDLB5B15A299AD5YZ9MLDLB5B 15A299 AD5YZ9
MLDLB5B17A385AB5YZ9MLDLB5B 17A385 AB5YZ9
MLDLB5B17A385BA5YZ9MLDLB5B 17A385 BA5YZ9
MLDLB5B17A385BB5YZ9MLDLB5B 17A385 BB5YZ9
MLDLB5B17A385DC5YZ9MLDLB5B 17A385 DC5YZ9
MLDLB5B17A385EB5YZ9MLDLB5B 17A385 EB5YZ9
MLDLB5B17B810AB5YZ9MLDLB5B 17B810 AB5YZ9
MLDLB5B17B810CB5YZ9MLDLB5B 17B810 CB5YZ9
MLDLB5B17D958ADMLDLB5B 17D958 AD
MLDLB5B17D959ADMLDLB5B 17D959 AD
MLDLB5B17E899ACMLDLB5B 17E899 AC
MLDLB5B17E899BBMLDLB5B 17E899 BB
MLDLB5B17F011AE5YZ9MLDLB5B 17F011 AE5YZ9
MLDLB5B17F011BC5YZ9MLDLB5B 17F011 BC5YZ9
MLDLB5B17F775AE5YZ9MLDLB5B 17F775 AE5YZ9
MLDLB5B17F775BE5YZ9MLDLB5B 17F775 BE5YZ9
MLDLB5B17F775CC5YZ9MLDLB5B 17F775 CC5YZ9
MLDLB5B17F775DA5YZ9MLDLB5B 17F775 DA5YZ9
MLDLB5B17F954AE5YZ9MLDLB5B 17F954 AE5YZ9
MLDLB5B17F954BE5YZ9MLDLB5B 17F954 BE5YZ9
MLDLB5B17F954CF5YZ9MLDLB5B 17F954 CF5YZ9
MLDLB5B17F954DE5YZ9MLDLB5B 17F954 DE5YZ9
MLDLB5B17F954DF5YZ9MLDLB5B 17F954 DF5YZ9
MLDLB5B17F954EF5YZ9MLDLB5B 17F954 EF5YZ9
MLDLB5B17F954FD5YZ9MLDLB5B 17F954 FD5YZ9
MLDLB5B17F954GD5YZ9MLDLB5B 17F954 GD5YZ9
MLDLB5B17F954HB5YZ9MLDLB5B 17F954 HB5YZ9
MLDLB5B17K922AC5YZ9MLDLB5B 17K922 AC5YZ9
MLDLB5B278G08ACMLDLB5B 278G08 AC
MLDLB5B278G09ACMLDLB5B 278G09 AC
MLDLB5B8312ABMLDLB5B 8312 AB
MLDLB5B8312CAMLDLB5B 8312 CA
MLDLB5B8314AAMLDLB5B 8314 AA
MLDLB5B8314ABMLDLB5B 8314 AB
MLDLB5B8314ACMLDLB5B 8314 AC
MLDLB5B8314BAMLDLB5B 8314 BA
MLDLB5B8A164ADMLDLB5B 8A164 AD
MLDLC5B13A078ABMLDLC5B 13A078 AB
MLDLC5B13A079ABMLDLC5B 13A079 AB
MLDLC5B17B635AC5YBTMLDLC5B 17B635 AC5YBT
MLDLC5B17D958ACMLDLC5B 17D958 AC
MLDLC5B17D959ACMLDLC5B 17D959 AC
MLDLC5B17F779ABMLDLC5B 17F779 AB
MLDLC5B17F954AB5YBTMLDLC5B 17F954 AB5YBT
MLDLC5B17F954AD5YBTMLDLC5B 17F954 AD5YBT
MLDLC5B17F954BE5YBTMLDLC5B 17F954 BE5YBT
MLDLC5B8312AAMLDLC5B 8312 AA
MLDLC5B8314AAMLDLC5B 8314 AA
MLDLC5B8A164ADMLDLC5B 8A164 AD
MLDLC5B8A200ADMLDLC5B 8A200 AD
MLDM1MB105B00EBMLDM1MB 105B00 EB
MLDMB5B01896AAR2A9MLDMB5B 01896AA R2A9
MLDMB5B01897AAR2A9MLDMB5B 01897AA R2A9
MLDMB5B17A385GA5YZ9MLDMB5B 17A385 GA5YZ9
MLDMB5B17F775DB5YZ9MLDMB5B 17F775 DB5YZ9
MLDMB5B17F775DC5YZ9MLDMB5B 17F775 DC5YZ9
MLDMB5B17F954HB5YZ9MLDMB5B 17F954 HB5YZ9
MLDMB5B8312AAMLDMB5B 8312 AA
MLDMB5B8314ACMLDMB5B 8314 AC
MLDRB5B17A385AA5YZ9MLDRB5B 17A385 AA5YZ9
MLDRB5B17A385BA5YZ9MLDRB5B 17A385 BA5YZ9
MLDRB5B17F011AA5YZ9MLDRB5B 17F011 AA5YZ9
MLDRB5B17F775AB5YZ9MLDRB5B 17F775 AB5YZ9
MLDRB5B17F775BA5YZ9MLDRB5B 17F775 BA5YZ9
MLDRB5B8312AAMLDRB5B 8312 AA
MLDRB5B8314AAMLDRB5B 8314 AA
MLDRB5B8314CAMLDRB5B 8314 CA
MLDRB5BS278G08AAMLDRB5BS 278G08 AA
MLDRB5BS278G09AAMLDRB5BS 278G09 AA
MLDRC5B17B635AA5YBTMLDRC5B 17B635 AA5YBT
MLDRC5B17F011AAMLDRC5B 17F011 AA
MLDRC5B8312AAMLDRC5B 8312 AA
MLDRC5B8314AAMLDRC5B 8314 AA
MLDRC5B8A200BAMLDRC5B 8A200 BA
MLDRC5B8A200BBMLDRC5B 8A200 BB
PCN806830S100N806 830S XXX
PCNB5B8200BA59B8NB5B 8200 XXXXXX
PCNC5B8200AA59B8NC5B 8200 XXXXXX
PCNU5T14G618ACNU5T 14G618 XX
PCNU5T15K859AA51MDNU5T 15K859 XXXXXX
PCP1M36A960FCAP1M3 6A960 XXX
PCP1M38C607AAP1M3 8C607 XX
PCP1M38D048FCAP1M3 8D048 XXX
PCP1M38D050FBAP1M3 8D050 XXX
PCP1M38D094LAP1M3 8D094 XX
PCP1MP7R081ABP1MP 7R081 XX
PCP1MT14A303BAAAP1MT 14A303 XXXX
PCP1MT14N139BADBP1MT 14N139 XXXX
PCP1MT15K867BBAP1MT 15K867 XXX
PCPV4A9F479BAPV4A 9F479 XX
PCR1M36K775ACR1M3 6K775 XX
PCR1M38B273AADR1M3 8B273 XXX
PCR1M38W005AABR1M3 8W005 XXX
PCR1M39D188CCR1M3 9D188 XX
PCR1M39E635AAR1M3 9E635 XX
PCR1MBS109A26AAR1MB S109A26 XX
PCR1MT14A303BABAR1MT 14A303 XXXX
PCR1MT14N139AAMAR1MT 14N139 XXXX
PCR1MT15K867FGAR1MT 15K867 XXX
PCRB5B13W029PBRB5B 13W029 XX
PCRB5B13W030PBRB5B 13W030 XX
PCRB5B15266AARB5B 15266 XX
PCRB5B15267AARB5B 15267 XX
PCRB5B15A245AA5YZ9RB5B 15A245 XXXXXX
PCRB5B15A246AA5YZ9RB5B 15A246 XXXXXX
PCRB5B15A254ABRB5B 15A254 XX
PCRB5B15A298AA5YZ9RB5B 15A298 XXXXXX
PCRB5B15A299AA5YZ9RB5B 15A299 XXXXXX
PCRB5B17C831AB5LPMRB5B 17C831 XXXXXX
PCRB5B17F765BA5JP4RB5B 17F765 XXXXXX
PCRB5B17F771BA5UAWRB5B 17F771 XXXXXX
PCRB5B8200AA5YZ9RB5B 8200 XXXXXX
PCRB5B8C324AARB5B 8C324 XX
PCRB5T15A861A1A5YZ9RB5T 15A861 XXXXXXX
PCRB5T15A862A1A5YZ9RB5T 15A862 XXXXXXX
PCRB5T15A863A1A5YZ9RB5T 15A863 XXXXXXX
PCRB5T15A864A1A5YZ9RB5T 15A864 XXXXXXX
PCRB5T9G853ADRB5T 9G853 XX
PCRC5B13E014ABRC5B 13E014 XX
PCRC5B13E015ABRC5B 13E015 XX
PCRC5B15A226AARC5B 15A226 XX
PCRC5B15A227AARC5B 15A227 XX
PCRC5B15A245BARC5B 15A245 XX
PCRC5B15A245CA59B8RC5B 15A245 XXXXXX
PCRC5B15A246BARC5B 15A246 XX
PCRC5B15A246CA59B8RC5B 15A246 XXXXXX
PCRC5B17B635BA5KBXRC5B 17B635 XXXXXX
PCRC5B17C831BA5KBXRC5B 17C831 XXXXXX
PCRC5B17F011AA5KBXRC5B 17F011 XXXXXX
PCRC5B17F788AASMASRC5B 17F788 XXXXXX
PCRC5B17K945AA59B8RC5B 17K945 XXXXXX
PCRC5B17K946AA59B8RC5B 17K946 XXXXXX
PCRC5B17K947AA59B8RC5B 17K947 XXXXXX
PCRC5B8200CASMASRC5B 8200 XXXXXX
PCRC5B8C324DB59B8RC5B 8C324 XXXXXX
PCRC5T15A861AARC5T 15A861 XX
PCRC5T15A862AARC5T 15A862 XX
PCRC5T15A863AARC5T 15A863 XX
PCRC5T15A864AARC5T 15A864 XX
PCW500214S442W500 XXXXXXX
PCW502660S450BW502 XXXXXXX
PCW505425S307W505 XXXXXXX
PCW506856S450BW506 XXXXXXX
PCW506975S450BW506 XXXXXXXX
PCW520201S440W520 XXXXXXX
PCW520802S439B1W520 XXXXXXXXX
PCW700618S300W700 XXXXXXX
PCW702852S300W702 XXXXXXX
PCW704277S439W704 XXXXXXX
PCW705297S300W705 XXXXXXX
PCW706184S300W706 XXXXXXX
PCW707501S439W707 XXXXXXX
PCW709816S442W709 XXXXXXX
PCW709984S300W709 XXXXXXX
PCW711158S439W711 XXXXXXX
PCW712524S450LW712 XXXXXXXX
PCW714113S451W714 XXXXXXX
PCW716195S450BW716 19XXXXXX
PCW716460S300W716 XXXXXXX
PCW717893S450W717 XXXXXXX
PCW718150S439W718 XXXXXXX
PCW719660S442W719 XXXXXXX
PCW720930S439W720 XXXXXXX
 
Upvote 0
2KGrafix said:
This I sent in your messages as well:

See the list below. To answer your question in the thread. Yes, the "X" are placeholders for a variety of parts. For example, take a look at the first part number. One of the actual part numbers is on the left. However, the last three characters could represent a 406 or 407 version of that part. All 349 part numbers in reply 7 are either unique prefixes or bases. The suffix could change but will always have the number of characters represented by the "X", no more, no less, and each one will have zero spaces or dashes. The complete list of parts is about four times as long. I don't know if you would need all of them. Let me know. Thank you again, you're helping me out a lot here
Click to expand...
I think you got me wrong.

What I need is an actual list of items as they are (unchanged, no placeholders).
The table you posted is not what I wanted (see first row of your table below)
PC66270661004056627 066100 XXX

What I need is a list (or table) with the original scanned barcodes (like mentioned in your first post)
I need a list like this:

L1M36K775BFKW03100530250880
PPCLB5B17C831AF5KBX
PL1M3 9C675 ACT9940173051168_1E_04
PLB5B 13B415 AB S10166954 D7/18/1998 0:52:15
P1MT-14A303-BAUB

or table like this:
SCANNED BARCODEACTUAL CODE
L1M36K775BFKW03100530250880PCL1M36K775BF
PPCLB5B17C831AF5KBXPCLB5B17C831AF5KBX
PL1M3 9C675 ACT9940173051168_1E_04PCL1M39C675AC
PLB5B 13B415 AB S10166954 D7/18/1998 0:52:15PCLB5B13B415AB
P1MT-14A303-BAUBPCP1MT14A303BAUB

Meanwhile I'm working on some VBA function which you could just add to your workbook...
 
Upvote 0
@2KGrafix
I have just finished coding, so here is what I got until now:

VBA Code: 
Option Explicit
Public Function barcode(text As String) As String
   Dim rex As Object: Set rex = CreateObject("VBScript.RegExp")
   Dim tmp As Variant
   Dim match As Object
   Dim p1 As String: p1 = "PC[A-Z0-9]+"
   Dim p2 As String: p2 = "[A-Z0-9]{4} [A-Z0-9]+ [A-Z0-9]{2}"
   Dim p3 As String: p3 = "[A-Z0-9]{4}-[A-Z0-9]+-[A-Z0-9]+"
  
   Select Case True
      Case RegExpTester(text, p1)
         rex.Pattern = p1
         Set match = rex.Execute(text)
         tmp = match(0)
         barcode = tmp
      Case RegExpTester(text, p2)
         rex.Pattern = p2
         Set match = rex.Execute(text)
         tmp = match(0)
         tmp = Replace(tmp, " ", "")
         barcode = "PC" & tmp
      Case RegExpTester(text, p3)
         rex.Pattern = p3
         Set match = rex.Execute(text)
         tmp = match(0)
         tmp = Replace(tmp, "-", "")
         barcode = "PC" & tmp
      Case Else
         barcode = "N/A"
   End Select
End Function
Private Function RegExpTester(txt As String, pat As String) As Boolean
   Dim rex As Object: Set rex = CreateObject("VBScript.RegExp")
   rex.Pattern = pat
   If rex.test(txt) = True Then
      RegExpTester = True
   Else
      RegExpTester = False
   End If
End Function

Here's how it works and looks like:
1706564438312.png


Right now my function has covered only 3 scenarios:
  1. The text contains the letters "PC" followed by any number of letters or digits -> pattern: PC[A-Z0-9]+
  2. The text contains 3 substrings delimited by spaces (prefix base suffix), where the prefix has exactly 4 characters and the suffix exactly 2 characters -> pattern: [A-Z0-9]{4} [A-Z0-9]+ [A-Z0-9]{2}
  3. The text contains 3 substrings delimited by dashes (prefix-base-suffix), where the prefix has exactly 4 characters -> pattern: [A-Z0-9]{4}-[A-Z0-9]+-[A-Z0-9]+
If none of the patterns was matched the function returns "N/A"

In order to develop the function I need more input from your side.

You can try out the VBA code and if you don't know how to start, just let me know. I'll guide you step by step from creating the right workbook (*.xlsm) to entering the user defined function (UDF).
 
Upvote 0
PeteWright said:
I think you got me wrong.

What I need is an actual list of items as they are (unchanged, no placeholders).
The table you posted is not what I wanted (see first row of your table below)
PC66270661004056627 066100 XXX

What I need is a list (or table) with the original scanned barcodes (like mentioned in your first post)
I need a list like this:

L1M36K775BFKW03100530250880
PPCLB5B17C831AF5KBX
PL1M3 9C675 ACT9940173051168_1E_04
PLB5B 13B415 AB S10166954 D7/18/1998 0:52:15
P1MT-14A303-BAUB

or table like this:
SCANNED BARCODEACTUAL CODE
L1M36K775BFKW03100530250880PCL1M36K775BF
PPCLB5B17C831AF5KBXPCLB5B17C831AF5KBX
PL1M3 9C675 ACT9940173051168_1E_04PCL1M39C675AC
PLB5B 13B415 AB S10166954 D7/18/1998 0:52:15PCLB5B13B415AB
P1MT-14A303-BAUBPCP1MT14A303BAUB

Meanwhile I'm working on some VBA function which you could just add to your workbook...
Click to expand...

PeteWright said:
@2KGrafix
I have just finished coding, so here is what I got until now:

VBA Code: 
Option Explicit
Public Function barcode(text As String) As String
   Dim rex As Object: Set rex = CreateObject("VBScript.RegExp")
   Dim tmp As Variant
   Dim match As Object
   Dim p1 As String: p1 = "PC[A-Z0-9]+"
   Dim p2 As String: p2 = "[A-Z0-9]{4} [A-Z0-9]+ [A-Z0-9]{2}"
   Dim p3 As String: p3 = "[A-Z0-9]{4}-[A-Z0-9]+-[A-Z0-9]+"
 
   Select Case True
      Case RegExpTester(text, p1)
         rex.Pattern = p1
         Set match = rex.Execute(text)
         tmp = match(0)
         barcode = tmp
      Case RegExpTester(text, p2)
         rex.Pattern = p2
         Set match = rex.Execute(text)
         tmp = match(0)
         tmp = Replace(tmp, " ", "")
         barcode = "PC" & tmp
      Case RegExpTester(text, p3)
         rex.Pattern = p3
         Set match = rex.Execute(text)
         tmp = match(0)
         tmp = Replace(tmp, "-", "")
         barcode = "PC" & tmp
      Case Else
         barcode = "N/A"
   End Select
End Function
Private Function RegExpTester(txt As String, pat As String) As Boolean
   Dim rex As Object: Set rex = CreateObject("VBScript.RegExp")
   rex.Pattern = pat
   If rex.test(txt) = True Then
      RegExpTester = True
   Else
      RegExpTester = False
   End If
End Function

Here's how it works and looks like:
View attachment 105933

Right now my function has covered only 3 scenarios:
  1. The text contains the letters "PC" followed by any number of letters or digits -> pattern: PC[A-Z0-9]+
  2. The text contains 3 substrings delimited by spaces (prefix base suffix), where the prefix has exactly 4 characters and the suffix exactly 2 characters -> pattern: [A-Z0-9]{4} [A-Z0-9]+ [A-Z0-9]{2}
  3. The text contains 3 substrings delimited by dashes (prefix-base-suffix), where the prefix has exactly 4 characters -> pattern: [A-Z0-9]{4}-[A-Z0-9]+-[A-Z0-9]+
If none of the patterns was matched the function returns "N/A"

In order to develop the function I need more input from your side.

You can try out the VBA code and if you don't know how to start, just let me know. I'll guide you step by step from creating the right workbook (*.xlsm) to entering the user defined function (UDF).
Click to expand...
It took me a second but here are the variations of barcode scans. On the left is what it should be, on the right it the scanner's data.

MLDLB5B17F775BE5YZ9MLDLB5B17F775BE5YZ9, 22:57:38 1/26/2024, SEQ 224
MLDLC5B17F954BE5YBTMLDLC5B17F954BE5YBT, 11:52: 2 1/22/2024, SEQ 257
PCLB5B13B414ABPLB5B 13B414 AB S10155600 D7/6/1998 0:22:1
PCLB5B17F771BB5UAWPPCLB5B17F771BB5UAW
PCLB5B8200EB5UAWLB5B-8200-EB5UAW
PCLC5B15A227ACPCLC5B-15A227-AC
PCLC5B17A848ABPLC5B 17A848 AB S10167471 D1/15/2024 8:48:9 12345
PCLC5B17C831AE5KBXPPCLB5B17C831AE5KBX
PCLC5B17C831AE5LPMPPCLC5B17C831AE5LPM
PCLC5B17E911AASMASPCLC5B17E911AASMAS
PCLC5B17E911CA59B8PCLC5B17E911CA59B8 10324
PCLC5B17F001AH51MDPPCLC5B17F001AH51MD
PCLC5B17K945AD59B8PPCLC5B17K945AD59B8
PCLC5B8200BESMA4PCLC5B8200BESMA4|240238352706||20240123181548
PCLC5B8200DE5KW9LC5B-8200-DE5KW9
PCL1MT13A803ABL1MT-13A803-AB
PCL1M38B273BAFL1M3-8B273-BAF,EE0WA,24010,18:10:12
PCL1M38005BFL1M38005BF7482
PCMB5J17F771AA51MDPPCMB5J17F771AA51MD
PCM1M38W005AAAM1M3-8W005-AAA C757L
PCP1MT15K867BBAP1MT-15K867-BBA
PCP1M38C607FAP1M38C607FA
PCP1MT14N139BADBP1MT 14N139 BADB
PCMB5B13W030JGMB5B13W030JG240125045754
PCP1MT14A303BBCAP1MT-14A303-BBCA
PCRB5B8200DBSMA4RB5B-8200-DBSMA4|0001|P3340|YYYYMMDDHHMMSS|
PCRC5B8200CBSMASCRC5B8200CBSMAS111623 ~233205437218~FD834.1A00.CA11
PCRB5B13W029CGRB5B13W020CG RH SAE HIGH U625(2) 12/05/23 16:36:55
PCRC5B17C831BB53CCPPCRC5B17C831BC53CC
PCRC5B13E014AERC5B13E014AE
PCRB5B17F765BA5JP4PPCRB5B17F765BA5JP4
 
Upvote 0
2KGrafix said:
It took me a second but here are the variations of barcode scans. On the left is what it should be, on the right it the scanner's data.

MLDLB5B17F775BE5YZ9MLDLB5B17F775BE5YZ9, 22:57:38 1/26/2024, SEQ 224
MLDLC5B17F954BE5YBTMLDLC5B17F954BE5YBT, 11:52: 2 1/22/2024, SEQ 257
PCLB5B13B414ABPLB5B 13B414 AB S10155600 D7/6/1998 0:22:1
PCLB5B17F771BB5UAWPPCLB5B17F771BB5UAW
PCLB5B8200EB5UAWLB5B-8200-EB5UAW
PCLC5B15A227ACPCLC5B-15A227-AC
PCLC5B17A848ABPLC5B 17A848 AB S10167471 D1/15/2024 8:48:9 12345
PCLC5B17C831AE5KBXPPCLB5B17C831AE5KBX
PCLC5B17C831AE5LPMPPCLC5B17C831AE5LPM
PCLC5B17E911AASMASPCLC5B17E911AASMAS
PCLC5B17E911CA59B8PCLC5B17E911CA59B8 10324
PCLC5B17F001AH51MDPPCLC5B17F001AH51MD
PCLC5B17K945AD59B8PPCLC5B17K945AD59B8
PCLC5B8200BESMA4PCLC5B8200BESMA4|240238352706||20240123181548
PCLC5B8200DE5KW9LC5B-8200-DE5KW9
PCL1MT13A803ABL1MT-13A803-AB
PCL1M38B273BAFL1M3-8B273-BAF,EE0WA,24010,18:10:12
PCL1M38005BFL1M38005BF7482
PCMB5J17F771AA51MDPPCMB5J17F771AA51MD
PCM1M38W005AAAM1M3-8W005-AAA C757L
PCP1MT15K867BBAP1MT-15K867-BBA
PCP1M38C607FAP1M38C607FA
PCP1MT14N139BADBP1MT 14N139 BADB
PCMB5B13W030JGMB5B13W030JG240125045754
PCP1MT14A303BBCAP1MT-14A303-BBCA
PCRB5B8200DBSMA4RB5B-8200-DBSMA4|0001|P3340|YYYYMMDDHHMMSS|
PCRC5B8200CBSMASCRC5B8200CBSMAS111623 ~233205437218~FD834.1A00.CA11
PCRB5B13W029CGRB5B13W020CG RH SAE HIGH U625(2) 12/05/23 16:36:55
PCRC5B17C831BB53CCPPCRC5B17C831BC53CC
PCRC5B13E014AERC5B13E014AE
PCRB5B17F765BA5JP4PPCRB5B17F765BA5JP4
Click to expand...
Alright, thanks. That's 31 variations. I'll try to figure out some patterns...

Right now we have 19 out of 31 correct matches. That makes 61 %

1706568800765.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

2
  • Question Question
VBA for using barcode scan to filter a column
Replies
0
Views
359
2KGrafix
2
M
  • Solved
VBA Help - Duplicate & Unwanted Data Removal
Replies
6
Views
915
MoonDancer
M
B
  • Solved
Barcode Scan result is errant in MS Excel, good data in Notepad/G Sheets
Replies
2
Views
3K
bmoreskimore
B
G
  • Solved
Scanning Barcode to check against existing database and notify if Needed.
Replies
3
Views
6K
GinT
G
B
  • Question Question
Barcode scanning help
Replies
0
Views
539
benmills6
B

Forum statistics

Threads
1,223,708
Messages
6,174,006
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top