Identify Part Numbers from String

since you have more than one # in the field,
change gustav's firt line from:
Use InStr() to look up the position of a "#" mark.
to
Use InStr() to look up the position of a "part#"

jaffer

First off ... if you can do as you said...
>> What I could do is have each supplier provide me a valid list of part numbers and then attempt to use SoundEx to perform the comparison <<

But .. you would not use SoundEx(), you would use the LIKE operator ...

... WHERE [YourFreeTextField] LIKE "*" & [VendoPartNumber] & "*"

If you are resonably sure of some delimiters you could do something like this too:

... WHERE [YourFreeTextField] LIKE "*# " & [VendoPartNumber] & ",*"

.....

In addition, if you are resonably sure of the use of the octothorpes (#) you can use VBA's Split() function to extract the part numbers and put them in an array or return a "cleaner" CSV of the partnumbers, but Split() does not work in a Query (of, so you need to create a User Defined Function in a module... maybe something like this:

Public Function CleanPartNumberCSV(strYourFreeTextValue As String) As String
   
    Dim aPartNumber() As String
    Dim x As Long
   
    aPartNumber = Split(strYourFreeTextValue, "#")
   
    For x = LBount(aPartNumber) To UBound(aPartNumber)
       
        'Remove Hyphens and spaces
        aPartNumber(x) = Replace(aPartNumber(x), "-", "")
        aPartNumber(x) = Replace(aPartNumber(x), Chr(32), "")
       
         '<do any further massaging you want>
    Next x
   
    'Return a pipe delimited string with your best attempt of extracting partnumbers
    CleanPartNumberCSV = "|" & Join(aPartNumber, "|") & "|"
 
End Function
-------------

The reason for the format of "|partnumber|partnumber|" is so you can use the LIKE in a where clause to effectively filter out a SINGLE partnumber ...

.... WHERE CleanPartNumberCSV([YourFreeTextField] & "") Like "|" & ActualPartNumberBeingSought & "|"

{note the .. &"" when passing the value to CleanPartNumberCSV is to coerce Nulls to a ZLS, thus not erroring out the UDF}

----------------

Hope this helps you in your daunting task!!

Hi,
I think datAdrenaline's idea's are spot on. I've had a crack at a UI in access for you. If you can get hold of a part list then the DB I've added may be of some use. I created a user defined function that would find part numbers in the free form text string.
The useful thing is that it returns multiple records i.e. if your string is 'hhhss645 jjjhh P/N22 hhj' and you had part numbers 645 and P/N22 then it returns 2 records and connects them to that string.

I've added a basic form that could be used as a UI to review these records and then choose the correct part number. If you could then combine this with datAdrenaline's CleanPartNumberCSV I think you would be well on your way to a solution.

The code to create a list of IDs to partIDs is located in the module.