I have this VBA function
Function SearchString(src As String, rng As Range) As String
Dim cell As Range
Dim output As String
' Remove spaces from source string
src = Replace(src, " ", "")
' Loop through cells in range
For Each cell In rng
' Remove spaces from cell value
cell.Value = Replace(cell.Value, " ", "")
' Check if whole text is matched
If src = cell.Value Then
output = cell.Row
Exit For
End If
' Check if first 13 characters match
If Len(src) >= 13 And Left(src, 13) = Left(cell.Value, 13) Then
output = cell.Row
Exit For
End If
' Check if first 11 characters match
If Len(src) >= 11 And Left(src, 11) = Left(cell.Value, 11) Then
output = cell.Row
Exit For
End If
' Check if first 9 characters match
If Len(src) >= 9 And Left(src, 9) = Left(cell.Value, 9) Then
output = cell.Row
Exit For
End If
' Check if first 7 characters match
If Len(src) >= 7 And Left(src, 7) = Left(cell.Value, 7) Then
output = cell.Row
Exit For
End If
Next cell
' Return result
If output = "" Then
SearchString = "No match"
Else
SearchString = output
End If
End Function
and when i called like
=SearchString(N4,H2:H21)
where N4 has text value "ahmed ali hassan"
and range h2:h21 has list of names
This yields #value.
I want to search list of names and search for whole text then without spaces then with first 13 letters and so on.
