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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Hi @2KGrafix

This is the closest I can get:

1706927481942.png


As you can see row 11, 12 and 17 were not matched.
That's because I couldn't find any possible pattern for them to work since they don't follow any strict rule.

11: L1M38005AF2734
12: L1M36K775BFKW03100540180141
17: MB5B13W029BF240125163613

Do you see anything those numbers have all in common? Or anything that would distinguish any of these numbers from others?
The only thing I can think of is following rule for 11 and 17:
<anything><letters only><digits only> and then keep everything but the digits at the end.
Would that help?

Here is my updated code:
VBA Code: 
Public Function barcode(text As String) As String
   Dim regex As Object: Set regex = CreateObject("VBScript.RegExp")
   Dim temp As String
   Dim pattern10 As String: pattern10 = "(MLD[A-Z0-9]+),.*"
   Dim pattern20 As String: pattern20 = "(PC[A-Z0-9]+)\|.*"
   Dim pattern30 As String: pattern30 = "^([A-Z0-9]{12}) .*"
   Dim pattern40 As String: pattern40 = "^([A-Z0-9]{10,12})$"
   Dim pattern44 As String: pattern44 = "^([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9]+)$"
   Dim pattern45 As String: pattern45 = "([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9_]{2}?)"
   Dim pattern50 As String: pattern50 = "([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9]+)"
   Dim pattern60 As String: pattern60 = "([A-Z0-9]{4})-([A-Z0-9]+)-([A-Z0-9]+)"
   Dim pattern70 As String: pattern70 = "^(PC[A-Z0-9]+)$"
   Dim pattern80 As String: pattern80 = "^(PC[A-Z0-9]+) [A-Z0-9]+"
   Dim pattern90 As String: pattern90 = "^P(PC[A-Z0-9]+)$"
   
   Dim pattern100 As String: pattern100 = "([A-Z0-9]{4})-([A-Z0-9]+)- ([A-Z0-9]+)"
   Dim pattern110 As String: pattern110 = "^P(PC[A-Z0-9]+) [A-Z0-9]+$"
   Dim pattern120 As String: pattern120 = "^C([A-Z0-9]+) ~[0-9]+~.*$"
   
   Select Case True
      Case RegExpTest(text, pattern10)
         text = RegExpReplace(text, pattern10, "$1")
         barcode = text
      Case RegExpTest(text, pattern20)
         text = RegExpReplace(text, pattern20, "$1")
         barcode = text
      Case RegExpTest(text, pattern30)
         text = RegExpGet(text, pattern30)
         text = RegExpReplace(text, "([A-Z0-9]+) .*", "$1")
         barcode = "PC" & text
      Case RegExpTest(text, pattern40)
         text = RegExpGet(text, pattern40)
         barcode = "PC" & text
      Case RegExpTest(text, pattern44)
         text = RegExpGet(text, pattern44)
         text = RegExpReplace(text, "^([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9]+)$", "$1$2$3")
         barcode = "PC" & text
      Case RegExpTest(text, pattern45)
         text = RegExpGet(text, pattern45)
         text = RegExpReplace(text, "([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9_]{2}?)", "$1$2$3")
         barcode = "PC" & text
      Case RegExpTest(text, pattern50)
         text = RegExpGet(text, pattern50)
         text = RegExpReplace(text, "([A-Z0-9]+) ([A-Z0-9]+) ([A-Z0-9]+)", "$1$2$3")
         barcode = "50_PC" & text
      Case RegExpTest(text, pattern60)
         text = RegExpGet(text, pattern60)
         text = RegExpReplace(text, "([A-Z0-9]+)-([A-Z0-9]+)-([A-Z0-9]+)", "$1$2$3")
         barcode = "PC" & text
      Case RegExpTest(text, pattern70)
         barcode = RegExpReplace(text, pattern70, "$1")
      Case RegExpTest(text, pattern80)
         text = RegExpGet(text, pattern80)
         text = RegExpReplace(text, "([A-Z0-9]+) .*", "$1")
         barcode = text
      Case RegExpTest(text, pattern90)
         text = RegExpGet(text, pattern90)
         text = RegExpReplace(text, "^P(PC[A-Z0-9]+)$", "$1")
         barcode = text
      Case RegExpTest(text, pattern100)
         text = RegExpGet(text, pattern100)
         text = RegExpReplace(text, "([A-Z0-9]+)-([A-Z0-9]+)- ([A-Z0-9]+)", "$1$2$3")
         barcode = "PC" & text
      Case RegExpTest(text, pattern110)
         text = RegExpGet(text, pattern110)
         text = RegExpReplace(text, "^P(PC[A-Z0-9]+) [A-Z0-9]+$", "$1")
         barcode = text
      Case RegExpTest(text, pattern120)
         text = RegExpGet(text, pattern120)
         text = RegExpReplace(text, "^C([A-Z0-9]+[^0-9])[0-9]* ~[0-9]+~.*$", "$1")
         barcode = "PC" & text
      Case Else
         barcode = "N/A"
   End Select
End Function
Private Function RegExpTest(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
      RegExpTest = True
   Else
      RegExpTest = False
   End If
End Function
Private Function RegExpReplace(txt As String, pat As String, rep As String) As String
   Dim rex As Object: Set rex = CreateObject("VBScript.RegExp")
   rex.Pattern = pat
   rex.Global = True
   RegExpReplace = rex.Replace(txt, rep)
End Function
Private Function RegExpGet(txt As String, pat As String)
   Dim rex As Object: Set rex = CreateObject("VBScript.RegExp")
   rex.Pattern = pat
   rex.Global = False
   RegExpGet = rex.Execute(txt)(0)
End Function


Please keep me updated.
 
Upvote 1
Update:

1706929253467.png


The code:

VBA Code: 
Public Function barcode(text As String) As String
   Dim regex As Object: Set regex = CreateObject("VBScript.RegExp")
   Dim temp As String
   Dim pattern10 As String: pattern10 = "(MLD[A-Z0-9]+),.*"
   Dim pattern20 As String: pattern20 = "(PC[A-Z0-9]+)\|.*"
   Dim pattern30 As String: pattern30 = "^([A-Z0-9]{12}) .*"
   Dim pattern40 As String: pattern40 = "^([A-Z0-9]{10,12})$"
   Dim pattern44 As String: pattern44 = "^([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9]+)$"
   Dim pattern45 As String: pattern45 = "([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9_]{2}?)"
   Dim pattern50 As String: pattern50 = "([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9]+)"
   Dim pattern60 As String: pattern60 = "([A-Z0-9]{4})-([A-Z0-9]+)-([A-Z0-9]+)"
   Dim pattern70 As String: pattern70 = "^(PC[A-Z0-9]+)$"
   Dim pattern80 As String: pattern80 = "^(PC[A-Z0-9]+) [A-Z0-9]+"
   Dim pattern90 As String: pattern90 = "^P(PC[A-Z0-9]+)$"
   
   Dim pattern100 As String: pattern100 = "([A-Z0-9]{4})-([A-Z0-9]+)- ([A-Z0-9]+)"
   Dim pattern110 As String: pattern110 = "^P(PC[A-Z0-9]+) [A-Z0-9]+$"
   Dim pattern120 As String: pattern120 = "^C([A-Z0-9]+) ~[0-9]+~.*$"
   Dim pattern130 As String: pattern130 = "^([A-Z0-9]+?[A-Z]+)[0-9]+$"
   Dim pattern140 As String: pattern140 = "^([A-Z0-9]+?[A-Z]{2})[A-Z]+[0-9]+$"
   
   Select Case True
      Case RegExpTest(text, pattern10)
         text = RegExpReplace(text, pattern10, "$1")
         barcode = text
      Case RegExpTest(text, pattern20)
         text = RegExpReplace(text, pattern20, "$1")
         barcode = text
      Case RegExpTest(text, pattern30)
         text = RegExpGet(text, pattern30)
         text = RegExpReplace(text, "([A-Z0-9]+) .*", "$1")
         barcode = "PC" & text
      Case RegExpTest(text, pattern40)
         text = RegExpGet(text, pattern40)
         barcode = "PC" & text
      Case RegExpTest(text, pattern44)
         text = RegExpGet(text, pattern44)
         text = RegExpReplace(text, "^([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9]+)$", "$1$2$3")
         barcode = "PC" & text
      Case RegExpTest(text, pattern45)
         text = RegExpGet(text, pattern45)
         text = RegExpReplace(text, "([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9_]{2}?)", "$1$2$3")
         barcode = "PC" & text
      Case RegExpTest(text, pattern50)
         text = RegExpGet(text, pattern50)
         text = RegExpReplace(text, "([A-Z0-9]+) ([A-Z0-9]+) ([A-Z0-9]+)", "$1$2$3")
         barcode = "50_PC" & text
      Case RegExpTest(text, pattern60)
         text = RegExpGet(text, pattern60)
         text = RegExpReplace(text, "([A-Z0-9]+)-([A-Z0-9]+)-([A-Z0-9]+)", "$1$2$3")
         barcode = "PC" & text
      Case RegExpTest(text, pattern70)
         barcode = RegExpReplace(text, pattern70, "$1")
      Case RegExpTest(text, pattern80)
         text = RegExpGet(text, pattern80)
         text = RegExpReplace(text, "([A-Z0-9]+) .*", "$1")
         barcode = text
      Case RegExpTest(text, pattern90)
         text = RegExpGet(text, pattern90)
         text = RegExpReplace(text, "^P(PC[A-Z0-9]+)$", "$1")
         barcode = text
      Case RegExpTest(text, pattern100)
         text = RegExpGet(text, pattern100)
         text = RegExpReplace(text, "([A-Z0-9]+)-([A-Z0-9]+)- ([A-Z0-9]+)", "$1$2$3")
         barcode = "PC" & text
      Case RegExpTest(text, pattern110)
         text = RegExpGet(text, pattern110)
         text = RegExpReplace(text, "^P(PC[A-Z0-9]+) [A-Z0-9]+$", "$1")
         barcode = text
      Case RegExpTest(text, pattern120)
         text = RegExpGet(text, pattern120)
         text = RegExpReplace(text, "^C([A-Z0-9]+[^0-9])[0-9]* ~[0-9]+~.*$", "$1")
         barcode = "PC" & text
      Case RegExpTest(text, pattern130)
         text = RegExpGet(text, pattern130)
         text = RegExpReplace(text, "^([A-Z0-9]+?[A-Z]{2})[A-Z]+[0-9]+$", "$1")
         barcode = "PC" & text
      Case Else
         barcode = "N/A"
   End Select
End Function
Private Function RegExpTest(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
      RegExpTest = True
   Else
      RegExpTest = False
   End If
End Function
Private Function RegExpReplace(txt As String, pat As String, rep As String) As String
   Dim rex As Object: Set rex = CreateObject("VBScript.RegExp")
   rex.Pattern = pat
   rex.Global = True
   RegExpReplace = rex.Replace(txt, rep)
End Function
Private Function RegExpGet(txt As String, pat As String)
   Dim rex As Object: Set rex = CreateObject("VBScript.RegExp")
   rex.Pattern = pat
   rex.Global = False
   RegExpGet = rex.Execute(txt)(0)
End Function
 
Last edited:
Upvote 1
Update V2:

VBA Code: 
Public Function barcode(text As String) As String
   Dim regex As Object: Set regex = CreateObject("VBScript.RegExp")
   Dim temp As String
   Dim pattern10 As String: pattern10 = "(MLD[A-Z0-9]+),.*"
   Dim pattern20 As String: pattern20 = "(PC[A-Z0-9]+)\|.*"
   Dim pattern30 As String: pattern30 = "^([A-Z0-9]{12}) .*"
   Dim pattern40 As String: pattern40 = "^([A-Z0-9]{10,12})$"
   Dim pattern44 As String: pattern44 = "^([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9]+)$"
   Dim pattern45 As String: pattern45 = "([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9_]{2}?)"
   Dim pattern50 As String: pattern50 = "([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9]+)"
   Dim pattern60 As String: pattern60 = "([A-Z0-9]{4})-([A-Z0-9]+)-([A-Z0-9]+)"
   Dim pattern70 As String: pattern70 = "^(PC[A-Z0-9]+)$"
   Dim pattern80 As String: pattern80 = "^(PC[A-Z0-9]+) [A-Z0-9]+"
   Dim pattern90 As String: pattern90 = "^P(PC[A-Z0-9]+)$"
   
   Dim pattern100 As String: pattern100 = "([A-Z0-9]{4})-([A-Z0-9]+)- ([A-Z0-9]+)"
   Dim pattern110 As String: pattern110 = "^P(PC[A-Z0-9]+) [A-Z0-9]+$"
   Dim pattern120 As String: pattern120 = "^C([A-Z0-9]+) ~[0-9]+~.*$"
   Dim pattern130 As String: pattern130 = "^([A-Z0-9]+?[A-Z]{2}?)[A-Z0-9]+$"
   Dim pattern140 As String: pattern140 = "^([A-Z0-9]+?[A-Z]+)[0-9]+$"
   
   Select Case True
      Case RegExpTest(text, pattern10)
         text = RegExpReplace(text, pattern10, "$1")
         barcode = text
      Case RegExpTest(text, pattern20)
         text = RegExpReplace(text, pattern20, "$1")
         barcode = text
      Case RegExpTest(text, pattern30)
         text = RegExpGet(text, pattern30)
         text = RegExpReplace(text, "([A-Z0-9]+) .*", "$1")
         barcode = "PC" & text
      Case RegExpTest(text, pattern40)
         text = RegExpGet(text, pattern40)
         barcode = "PC" & text
      Case RegExpTest(text, pattern44)
         text = RegExpGet(text, pattern44)
         text = RegExpReplace(text, "^([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9]+)$", "$1$2$3")
         barcode = "PC" & text
      Case RegExpTest(text, pattern45)
         text = RegExpGet(text, pattern45)
         text = RegExpReplace(text, "([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9_]{2}?)", "$1$2$3")
         barcode = "PC" & text
      Case RegExpTest(text, pattern50)
         text = RegExpGet(text, pattern50)
         text = RegExpReplace(text, "([A-Z0-9]+) ([A-Z0-9]+) ([A-Z0-9]+)", "$1$2$3")
         barcode = "50_PC" & text
      Case RegExpTest(text, pattern60)
         text = RegExpGet(text, pattern60)
         text = RegExpReplace(text, "([A-Z0-9]+)-([A-Z0-9]+)-([A-Z0-9]+)", "$1$2$3")
         barcode = "PC" & text
      Case RegExpTest(text, pattern70)
         barcode = RegExpReplace(text, pattern70, "$1")
      Case RegExpTest(text, pattern80)
         text = RegExpGet(text, pattern80)
         text = RegExpReplace(text, "([A-Z0-9]+) .*", "$1")
         barcode = text
      Case RegExpTest(text, pattern90)
         text = RegExpGet(text, pattern90)
         text = RegExpReplace(text, "^P(PC[A-Z0-9]+)$", "$1")
         barcode = text
      Case RegExpTest(text, pattern100)
         text = RegExpGet(text, pattern100)
         text = RegExpReplace(text, "([A-Z0-9]+)-([A-Z0-9]+)- ([A-Z0-9]+)", "$1$2$3")
         barcode = "PC" & text
      Case RegExpTest(text, pattern110)
         text = RegExpGet(text, pattern110)
         text = RegExpReplace(text, "^P(PC[A-Z0-9]+) [A-Z0-9]+$", "$1")
         barcode = text
      Case RegExpTest(text, pattern120)
         text = RegExpGet(text, pattern120)
         text = RegExpReplace(text, "^C([A-Z0-9]+[^0-9])[0-9]* ~[0-9]+~.*$", "$1")
         barcode = "PC" & text
      Case RegExpTest(text, pattern130)
         text = RegExpGet(text, pattern130)
         text = RegExpReplace(text, "^([A-Z0-9]+?[A-Z]{2}?)[A-Z0-9]+$", "$1")
         barcode = "PC" & text
      Case RegExpTest(text, pattern140)
         text = RegExpGet(text, pattern140)
         text = RegExpReplace(text, "^([A-Z0-9]+?[A-Z]+)[0-9]+$", "$1")
         barcode = "PC" & text
      Case Else
         barcode = "N/A"
   End Select
End Function
Private Function RegExpTest(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
      RegExpTest = True
   Else
      RegExpTest = False
   End If
End Function
Private Function RegExpReplace(txt As String, pat As String, rep As String) As String
   Dim rex As Object: Set rex = CreateObject("VBScript.RegExp")
   rex.Pattern = pat
   rex.Global = True
   RegExpReplace = rex.Replace(txt, rep)
End Function
Private Function RegExpGet(txt As String, pat As String)
   Dim rex As Object: Set rex = CreateObject("VBScript.RegExp")
   rex.Pattern = pat
   rex.Global = False
   RegExpGet = rex.Execute(txt)(0)
End Function
 
Upvote 1
Solution
2KGrafix said:
Hi Pete, so I've been testing the form. It works great. I ran into two issues. When I scan a barcode that reads out MLD part numbers correctly, it returns PCMLD. See the example below. The part number is indeed MLDL1MB105B00BB but the barcode pattern converts it to just PLMLD only, nothing more. Is there an additional pattern that can be written for this to add to the code you previously wrote? Let me know what you think. Thanks.

1MLDL1MB105B00BBPCMLD#N/A#N/A
Click to expand...
Hi, sorry about that. I totally forgot the MLD in my patterns :p
There is only a MLD pattern which is followed by a comma

Just insert the following pattern and code:
VBA Code: 
Dim pattern00 As String: pattern00 = "(MLD[A-Z0-9]+)"
VBA Code: 
Case RegExpTest(text, pattern00)
         text = RegExpReplace(text, pattern00, "$1")
         barcode = text

Here's the complete VBA Code Module:
VBA Code: 
Public Function barcode(text As String) As String
   Dim regex As Object: Set regex = CreateObject("VBScript.RegExp")
   Dim temp As String
   Dim pattern00 As String: pattern00 = "(MLD[A-Z0-9]+)"
   Dim pattern10 As String: pattern10 = "(MLD[A-Z0-9]+),.*"
   Dim pattern20 As String: pattern20 = "(PC[A-Z0-9]+)\|.*"
   Dim pattern30 As String: pattern30 = "^([A-Z0-9]{12}) .*"
   Dim pattern40 As String: pattern40 = "^([A-Z0-9]{10,12})$"
   Dim pattern44 As String: pattern44 = "^([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9]+)$"
   Dim pattern45 As String: pattern45 = "([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9_]{2}?)"
   Dim pattern50 As String: pattern50 = "([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9]+)"
   Dim pattern60 As String: pattern60 = "([A-Z0-9]{4})-([A-Z0-9]+)-([A-Z0-9]+)"
   Dim pattern70 As String: pattern70 = "^(PC[A-Z0-9]+)$"
   Dim pattern80 As String: pattern80 = "^(PC[A-Z0-9]+) [A-Z0-9]+"
   Dim pattern90 As String: pattern90 = "^P(PC[A-Z0-9]+)$"
   
   Dim pattern100 As String: pattern100 = "([A-Z0-9]{4})-([A-Z0-9]+)- ([A-Z0-9]+)"
   Dim pattern110 As String: pattern110 = "^P(PC[A-Z0-9]+) [A-Z0-9]+$"
   Dim pattern120 As String: pattern120 = "^C([A-Z0-9]+) ~[0-9]+~.*$"
   Dim pattern130 As String: pattern130 = "^([A-Z0-9]+?[A-Z]{2}?)[A-Z0-9]+$"
   Dim pattern140 As String: pattern140 = "^([A-Z0-9]+?[A-Z]+)[0-9]+$"
   
   Select Case True
      Case RegExpTest(text, pattern00)
         text = RegExpReplace(text, pattern00, "$1")
         barcode = text
      Case RegExpTest(text, pattern10)
         text = RegExpReplace(text, pattern10, "$1")
         barcode = text
      Case RegExpTest(text, pattern20)
         text = RegExpReplace(text, pattern20, "$1")
         barcode = text
      Case RegExpTest(text, pattern30)
         text = RegExpGet(text, pattern30)
         text = RegExpReplace(text, "([A-Z0-9]+) .*", "$1")
         barcode = "PC" & text
      Case RegExpTest(text, pattern40)
         text = RegExpGet(text, pattern40)
         barcode = "PC" & text
      Case RegExpTest(text, pattern44)
         text = RegExpGet(text, pattern44)
         text = RegExpReplace(text, "^([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9]+)$", "$1$2$3")
         barcode = "PC" & text
      Case RegExpTest(text, pattern45)
         text = RegExpGet(text, pattern45)
         text = RegExpReplace(text, "([A-Z0-9]{4}) ([A-Z0-9]+) ([A-Z0-9_]{2}?)", "$1$2$3")
         barcode = "PC" & text
      Case RegExpTest(text, pattern50)
         text = RegExpGet(text, pattern50)
         text = RegExpReplace(text, "([A-Z0-9]+) ([A-Z0-9]+) ([A-Z0-9]+)", "$1$2$3")
         barcode = "PC" & text
      Case RegExpTest(text, pattern60)
         text = RegExpGet(text, pattern60)
         text = RegExpReplace(text, "([A-Z0-9]+)-([A-Z0-9]+)-([A-Z0-9]+)", "$1$2$3")
         barcode = "PC" & text
      Case RegExpTest(text, pattern70)
         barcode = RegExpReplace(text, pattern70, "$1")
      Case RegExpTest(text, pattern80)
         text = RegExpGet(text, pattern80)
         text = RegExpReplace(text, "([A-Z0-9]+) .*", "$1")
         barcode = text
      Case RegExpTest(text, pattern90)
         text = RegExpGet(text, pattern90)
         text = RegExpReplace(text, "^P(PC[A-Z0-9]+)$", "$1")
         barcode = text
      Case RegExpTest(text, pattern100)
         text = RegExpGet(text, pattern100)
         text = RegExpReplace(text, "([A-Z0-9]+)-([A-Z0-9]+)- ([A-Z0-9]+)", "$1$2$3")
         barcode = "PC" & text
      Case RegExpTest(text, pattern110)
         text = RegExpGet(text, pattern110)
         text = RegExpReplace(text, "^P(PC[A-Z0-9]+) [A-Z0-9]+$", "$1")
         barcode = text
      Case RegExpTest(text, pattern120)
         text = RegExpGet(text, pattern120)
         text = RegExpReplace(text, "^C([A-Z0-9]+[^0-9])[0-9]* ~[0-9]+~.*$", "$1")
         barcode = "PC" & text
      Case RegExpTest(text, pattern130)
         text = RegExpGet(text, pattern130)
         text = RegExpReplace(text, "^([A-Z0-9]+?[A-Z]{2}?)[A-Z0-9]+$", "$1")
         barcode = "PC" & text
      Case RegExpTest(text, pattern140)
         text = RegExpGet(text, pattern140)
         text = RegExpReplace(text, "^([A-Z0-9]+?[A-Z]+)[0-9]+$", "$1")
         barcode = "PC" & text
      Case Else
         barcode = "N/A"
   End Select
End Function
Private Function RegExpTest(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
      RegExpTest = True
   Else
      RegExpTest = False
   End If
End Function
Private Function RegExpReplace(txt As String, pat As String, rep As String) As String
   Dim rex As Object: Set rex = CreateObject("VBScript.RegExp")
   rex.Pattern = pat
   rex.Global = True
   RegExpReplace = rex.Replace(txt, rep)
End Function
Private Function RegExpGet(txt As String, pat As String)
   Dim rex As Object: Set rex = CreateObject("VBScript.RegExp")
   rex.Pattern = pat
   rex.Global = False
   RegExpGet = rex.Execute(txt)(0)
End Function
 
Upvote 1
@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.
 
Upvote 0
PeteWright said:
@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.
Click to expand...

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.
 
Upvote 0
@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.
 
Upvote 0
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.
 
Upvote 0
@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".
 
Upvote 0
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,215,727
Messages
6,126,515
Members
449,316
Latest member
sravya

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