Partial match Substring

Question

I have a hierarchy codification system within a sheet column. I would like looking for values within that column that match in partially way with values on that column also. The search should start from longer values. Here the sample:

AME_ASO_010_010_010

AME_ASO_010_010_010_010 (longer values, search starting)

In summary i look for some ideas for finding matches, without taking into account last four places (_010). Thanks to all! Any support will be appreciated!

With the contribution of dwirony, im trying this. Would somebody please know why is giving me object required error (424). Many thanks! it Fails in line > Left(cell, Len(cell) - 4).Offset(, 1).Select

Sub main()
Dim cell As Range
Dim arr As Variant, arrElem1 As Variant
Dim rng As Range
Dim sh1 As Worksheet

Set sh1 = Sheets("Valeurs")

  With Worksheets("Valeurs")
    For Each cell In .Range("E1", .Cells(.Rows.Count, "E").End(xlUp))
    Set rng = Range(cell, cell.Offset(0, 12))

            arr = Split(Replace(cell.Value, "  ", " "), " ")
            For Each arrElem1 In arr
            If Len(arrElem1) = 15 Then

                Left(cell, Len(cell) - 4).Offset(, 1).Select
                With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent4
                .TintAndShade = -0.249977111117893
                .PatternTintAndShade = 0
                End With

            End If
            Next arrElem1
    Next cell
End With

End Sub

is this helppful? https://stackoverflow.com/questions/15585058/check-if-a-string-contains-another-string — juju, Oct 12 '17 at 16:56
Hi, thanks! the problem is that I don't know how removing four last places (longer code) from the search. — Daniel Velez, Oct 12 '17 at 17:05
@DanielVelez You can use `Left` or `InStr`, if using a macro to find partial matches — dwirony, Oct 12 '17 at 17:05
@DanielVelez So to omit the last 4 characters of a string, you would do `Left(mystring,Len(mystring)-4)` — dwirony, Oct 12 '17 at 17:07

score 0 · Accepted Answer · answered Oct 13 '17 at 16:57

Trying and trying the success has arrived! Here the code, maybe it will be useful for others. Subject closed!

Sub main()
Dim i As Long
Dim cell As Range
Dim lResult As String
Dim arr As Variant, arrElem1 As Variant
Dim rng As Range, rng1 As Range
Dim sh1 As Worksheet

    With Worksheets("Valeurs")
    For Each cell In .Range("E1", .Cells(.Rows.Count, "E").End(xlUp))

        arr = Split(Replace(cell.Value, "  ", " "), " ")
            For Each arrElem1 In arr
            If Len(arrElem1) = 15 Then
            lResult = Left(arrElem1, Len(arrElem1) - 4)
                Set rng1 = sh1.Range("E15:E10000")
                Set Findv = Range("E15:E10000").Cells.Find(What:=lResult, LookAt:=xlWhole, _
                After:=Range("E15"), SearchDirection:=xlPrevious)
                Findv.Offset(0, 1).Select
                    With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent4
                    .TintAndShade = -0.249977111117893
                    .PatternTintAndShade = 0
                    End With

            End If
            Next arrElem1
    Next cell
    End With

End Sub

Partial match Substring

1 Answers1