Can i unscramble/ straighten out user data into my prefered input?

I have an excel sheet which i use to order materials needed for projects at work.

I just copy the data from the client into this sheet and it figures all sorts of things out. The problem is that every client hands off the data in a different way. For example:

Item	Color
Bike	Rims Ral 7035, Frame RGB 255/255/255
car	Rims Sikkens R6.23.34, Frame RGB 230/010/255

How my clients give information:

Item	Color
Bike	Frame RGB 255/255/255-Rims R6.23.34
car	Rims 9016/ RGB 230 010 255 frame color

Is there a way to look up any part of the string in a cell and compare it to my color database? So if 7035 is recognized it know to put "Frame Ral" & "7035"

Does this make sense? And is there a way?

PS. obviously i can keep returning the files to clients if it doesnt match but i would like it if i could do it this way.

I tried something with isnumber(search("",A2)) but since the code is somewhere in that string, i dont know what to search for. Also the color codes have different lengths depending on the manufacturer.

Solution

Here's a quick example of a VBA function (that could be used as a udf) that will search the incoming cell's value for something that matches RGB <number>/<number>/<number> where the / could also be a space.

Note, this is just an example and it won't 100% solve your problem, but if you are serious about heading towards VBA to solve this, this should get you a jump start.

Function getColorCode(inCell As Range) As String
    'search incoming cell value for regex pattern and return first hit
    '
    'Add Microsoft VBScript Regular Expressions 5.5 via Tool>>References
    
    Dim regEx As New RegExp
    Dim pattern As String
    
    'write the regex pattern for matching color
    pattern = "RGB [0-9]{1,3}[/ ][0-9]{1,3}[/ ][0-9]{1,3}"
    
    'Set up the regex object properties
    With regEx
        .Global = True
        .MultiLine = False
        .IgnoreCase = True
        .pattern = pattern
    End With
    
    'If we find a match, extract it and return
    If regEx.Test(inCell.Value) Then
        Set matches = regEx.Execute(inCell.Value)
        getColorCode = matches(0)
    End If
    
End Function

In the VBA screen, you'll need to go to Tools>>References and find that Microsoft VBScript Regular Expressions 5.5 and put a checkbox next to it for this work. Then create a new module and paste this in. You can then save your workbook and use this in the sheet like =getColorCode(A1) where A1 has a value like your example.

For some more information about regex in VBA check out the answers here: How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops You can also search online since most dialects of regex are similar. That pattern string will be the part that needs the most tweaking to deal with as many edge cases as possible.