4 Responses to “Split search value using delimiter and search for each substring”

1. Anil says:

   May 1, 2017 at 5:17 am

   Thanks

   for reply my question

   it makes me very easy to do my job more simple

   but i am facing a problem
   if the value is not in lookup array the result is only partial

   I have
   A1(anil baljeet raj)

   and result should be
   B1 (10 (error) 20)
   Or

   Lookup array is
   D1(anil) E1(10)
   D2(raj) E2(20)
   D3(singh) E3(30)

   means if the value is not in the lookup array
   the result should not skip the value's(not in the lookup array) space

   Reply
   • Rick Rothstein (MVP - Excel) says:

     May 1, 2017 at 7:02 pm

     Does the following UDF do what you want? NOTE that I change the argumemtn list from how Oscar setup his UDF... instead of a second argument for the search column and a third argument for the replacement column, I have only a second argument for the search/replace table (assumed to be two contiguous, aligned ranges of cells); so, you would call my UDF like this...

     =SearchValues(A1,$D$1:$E$3)

     where cell A1 has the text being parsed and range D1:E3 contains the search and replace values. Here is my UDF code...

     Function SearchValues(ByVal S As String, _
              SearchReplace As Range) As String
       Dim Word As Variant, Rng As Range, HasParen As Boolean
       HasParen = Left(S, 1) = "("
       S = Trim(Replace(Replace(S, "(", ""), ")", ""))
       For Each Word In Split(S)
         Set Rng = SearchReplace.Columns(1).Find( _
                   Word, , , xlWhole, , , False)
         If Rng Is Nothing Then
           S = Replace(" " & S & " ", " " & Word & " ", _
                       " (Error) ", , , vbTextCompare)
         Else
           S = Replace(" " & S & " ", " " & Word & " ", _
                       " " & Rng.Offset(, 1).Value & _
                       " ", , , vbTextCompare)
         End If
       Next
       If HasParen Then S = "(" & S & ")"
       SearchValues = Application.Trim(S)
     End Function
     

   • Oscar says:

     May 1, 2017 at 8:40 pm

     Rick Rothstein (MVP - Excel),

     Thank you for your comment and yes your UDF works fine. Yours is probably faster too using the Range Find method, but is it faster than converting the range to an array and then search with a simple For - Next? Anyway, interesting UDF.

     Anil,

     If your search and replace table is not contiguous this UDF seems to work also.

     Function SearchValues(str As Range, search_col As Range, return_col As Range)
     Dim j As Long, i As Long
     arr = Split(str, " ")
     j = search_col.Rows.CountLarge
     For Each Vl In arr
         chk = 0
         For i = 1 To j
             If search_col.Cells(i, 1) = Vl Then
                 result = result & return_col.Cells(i, 1) & " "
                 chk = 1
             End If
         Next i
         If chk = 0 Then result = result & "(Error)" & " "
     Next Vl
     SearchValues = result
     End Function
     

2. Anil says:

   May 7, 2017 at 1:51 pm

   Thank you very much oscar

   it makes me very easy to do my work

   i hope you will be in touch as you can
   for our futures problems regarding excel

   thanks Again
   with regards
   Anil

   Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes &lt; and > becomes &gt;

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form