Author: Oscar Cronquist Commodity last updated on July 13, 2021

Split search value using delimiter and search for each substring

This commodity demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns the corresponding values also concatenated.

The instance prototype above shows "anil singh raj" in prison cell B3, the UDF and formulas split the string in substrings and search for each sub-string in cell range E3:E5. If a match is found the corresponding value in cell range F3:F5 is returned.

The result values are concatenated if multiple values are matching values in jail cell range E3:E5. "anil" is constitute in cell E3, the corresponding value is 10. "singh" is found in prison cell E5, the coprresponding value is 30. Value "raj" is institute in cell E4 and the corresponding value in cell F4 is 20. The value returned in cell C3 is 10 30 20.

1. Question

Anil asks:
I have
A1(anil singh raj)
Information technology can be anything Like
A1(singh raj anil)
I want render value in
B1 (ten 30 20)
Or
B1(30 xx 10)
Or
Lookup array is
D1(anil) E1(10)
D2(raj) E2(20)
D3(singh) E3(thirty)

I take fabricated a small-scale custom function to split the search string and get the values yous are looking for.

Back to top

ii. User Defined Part Syntax

SearchValues(str, search_col, return_col)

str - Search string

search_col - lookup column

return_col - values to render

Back to top

3. UDF Formula

Split search value using delimiter and search for each substring

Formula in cell C3:

=SearchValues(B3, $E$3:$E$5, $F$3:$F$five)

Back to height

4. User Defined Function - VBA

'Name Function Function SearchValues(str Every bit Range, search_col As Range, return_col As Range)  'Dimension variables and declare information types Dim j As Long, i Every bit Long  'Split text in variable str based on a space grapheme arr = Split(str, " ")  'Save the number of rows in range variable search_col to variable j j = search_col.Rows.CountLarge  'Iterate through all array values in variable arr  For Each Vl In arr      'Go from 1 to number stored in variable j     For i = i To j          'If ... Then argument         'Check if value in range variable search_col is equal to variable Vl          'Concatenate corresponding value in return_col with result variable if true         If search_col.Cells(i, 1) = Vl So result = result & return_col.Cells(i, 1) & " "     Next i Adjacent Vl  'Return string stored in effect to worksheet SearchValues = event End Function

Back to top

5. Where to put the VBA code?

Yous need to copy the code to a higher place and paste it to a code module, detailed instructions below.

  1. Open vb editor (shortcut keys: Alt+F11)
  2. Insert a new module
  3. Paste code to lawmaking module
  4. Exit vb Editor

Note, save your workbook as a macro-enabled workbook *.xlsm to go on the code fastened to your workbook.

Back to top

6. Excel 2019 Formula

Split search value using delimiter and search for each substring array formula

Array formula in cell C3:

=TEXTJOIN(" ", TRUE, TRANSPOSE(IF(IFERROR(SEARCH(TRANSPOSE(FILTERXML("<A><B>"& SUBSTITUTE(B3, " ", "</B><B>") & "</B></A>", "//B")), $E$3:$East$5), 0), $F$iii:$F$five, "")))

vi.1 Explaining formula in cell C3

Step 1 - Insert XML tags

The SUBSTITUTE part replaces a specific text string in a value. We need to replace the delimiting character to xml tags in order to split the strings into an array.

SUBSTITUTE(text,old_text,new_text, [instance_num])

"<A><B>"& SUBSTITUTE(B3, " ", "</B><B>") & "</B></A>"

becomes

"<A><B>"&"anil</B><B>singh</B><B>raj"& "</B></A>"

and returns

"<A><B>anil</B><B>singh</B><B>raj</B></A>"

Step 2 - Separate string into substrings

The FILTERXML role extracts specific values from XML content past using the given xpath. You can utilize this function to split a string into substrings.

FILTERXML("<A><B>"& SUBSTITUTE(B3, " ", "</B><B>") & "</B></A>", "//B")

becomes

FILTERXML("<A><B>anil</B><B>singh</B><B>raj</B></A>", "//B")

and returns {"anil";"singh";"raj"}.

Step iii - Convert a vertical range to a horizontal range, or vice versa

The TRANSPOSE function allows you to convert a vertical range to a horizontal range, or vice versa.

TRANSPOSE(FILTERXML("<A><B>"& SUBSTITUTE(B3, " ", "</B><B>") & "</B></A>", "//B"))

becomes

TRANSPOSE({"anil"; "singh"; "raj"})

and returns {"anil", "singh", "raj"}.

Step 4 - Search for each value in the array in jail cell range $Eastward$iii:$E$five

The SEARCH function returns a number representing the position of character at which a specific text string is establish reading left to correct.

SEARCH(find_text,within_text, [start_num])

SEARCH(TRANSPOSE(FILTERXML("<A><B>"& SUBSTITUTE(B3, " ", "</B><B>") & "</B></A>", "//B")), $E$3:$East$five)

becomes

SEARCH( {"anil", "singh", "raj"}, $Due east$three:$Eastward$5)

becomes

SEARCH( {"anil", "singh", "raj"}, {"anil";"raj";"singh"})

and returns {1, #VALUE!, #VALUE!; #VALUE!, #VALUE!, 1; #VALUE!, 1, #VALUE!}.

Step five - Supplant #VALUE errors with 0 (zero)

The IFERROR function lets you catch most errors in Excel formulas.

IFERROR(value,value_if_error)

IFERROR(SEARCH(TRANSPOSE(FILTERXML("<A><B>"& SUBSTITUTE(B3, " ", "</B><B>") & "</B></A>", "//B")), $Due east$iii:$E$5), 0)

becomes

IFERROR({1, #VALUE!, #VALUE!; #VALUE!, #VALUE!, ane; #VALUE!, ane, #VALUE!}, 0)

and returns {one, 0, 0; 0, 0, 1; 0, 1, 0}.

Stride 6 - Return values if Truthful (i)

The IF office returns ane value if the logical test is Truthful and another value if the logical examination is Faux.

IF(logical_test, [value_if_true], [value_if_false])

IF(IFERROR(SEARCH(TRANSPOSE(FILTERXML("<A><B>"& SUBSTITUTE(B3, " ", "</B><B>") & "</B></A>", "//B")), $E$3:$Due east$5), 0), $F$iii:$F$five, "")

becomes

IF({i, 0, 0; 0, 0, one; 0, i, 0}, $F$3:$F$5, "")

becomes

IF({ane, 0, 0; 0, 0, ane; 0, 1, 0}, {x; 20; 30}, "")

and returns {10,"","";"","",twenty;"",30,""}.

Footstep seven - Convert a vertical range to a horizontal range, or vice versa

The TRANSPOSE function allows you to catechumen a vertical range to a horizontal range, or vice versa.

TRANSPOSE(IF(IFERROR(SEARCH(TRANSPOSE(FILTERXML("<A><B>"& SUBSTITUTE(B3, " ", "</B><B>") & "</B></A>", "//B")), $Due east$3:$E$five), 0), $F$3:$F$5, ""))

becomes

TRANSPOSE({10,"","";"","",20;"",30,""})

and returns {10,"","";"","",thirty;"",20,""}.

Footstep 8 - Concate values in array

The TEXTJOIN office allows yous to combine text strings from multiple jail cell ranges and also use delimiting characters if you lot want.

TEXTJOIN(" ", TRUE, TRANSPOSE(IF(IFERROR(SEARCH(TRANSPOSE(FILTERXML("<A><B>"& SUBSTITUTE(B3, " ", "</B><B>") & "</B></A>", "//B")), $E$three:$E$5), 0), $F$3:$F$5, "")))

becomes

TEXTJOIN(" ", TRUE, {10,"","";"","",30;"",20,""})

and returns "ten 30 20".

Back to height

Back to pinnacle