Split search value using a delimiter and search for each substring
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns the corresponding values also concatenated.
The example image above shows "anil singh raj" in 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 cell range E3:E5. "anil" is found in cell E3, the corresponding value is 10. "singh" is found in cell E5, the corresponding value is 30. Value "raj" is found in cell E4 and the corresponding value in cell F4 is 20. The value returned in cell C3 is 10 30 20.
What's on this page
1. Question
I have
A1(anil singh raj)
It can be anything Like
A1(singh raj anil)
I want return value in
B1 (10 30 20)
Or
B1(30 20 10)
Or
Lookup array is
D1(anil) E1(10)
D2(raj) E2(20)
D3(singh) E3(30)
I have made a small custom function to split the search string and get the values you are looking for.
2. User Defined Function Syntax
SearchValues(str, search_col, return_col)
str - Search string
search_col - lookup column
return_col - values to return
3. UDF Formula
Formula in cell C3:
=SearchValues(B3, $E$3:$E$5, $F$3:$F$5)
4. User Defined Function - VBA
'Name Function
Function SearchValues(str As Range, search_col As Range, return_col As Range)
'Dimension variables and declare data types
Dim j As Long, i As Long
'Split text in variable str based on a space character
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 = 1 To j
'If ... Then statement
'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 Then result = result & return_col.Cells(i, 1) & " "
Next i
Next Vl
'Return string stored in result to worksheet
SearchValues = result
End Function
5. Where to put the VBA code?
You need to copy the code above and paste it to a code module, detailed instructions below.
- Open vb editor (shortcut keys: Alt+F11)
- Insert a new module
- Paste code to code module
- Exit vb Editor
Note, save your workbook as a macro-enabled workbook *.xlsm to keep the code attached to your workbook.
6. Excel 2019 Formula
Array formula in cell C3:
6.1 Explaining formula in cell C3
Step 1 - Insert XML tags
The SUBSTITUTE function 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 - Split string into substrings
The FILTERXML function extracts specific values from XML content by using the given xpath. You can use 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 3 - 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 cell range $E$3:$E$5
The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right.
SEARCH(find_text,within_text, [start_num])
SEARCH(TRANSPOSE(FILTERXML("<A><B>"& SUBSTITUTE(B3, " ", "</B><B>") & "</B></A>", "//B")), $E$3:$E$5)
becomes
SEARCH( {"anil", "singh", "raj"}, $E$3:$E$5)
becomes
SEARCH( {"anil", "singh", "raj"}, {"anil";"raj";"singh"})
and returns {1, #VALUE!, #VALUE!; #VALUE!, #VALUE!, 1; #VALUE!, 1, #VALUE!}.
Step 5 - Replace #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")), $E$3:$E$5), 0)
becomes
IFERROR({1, #VALUE!, #VALUE!; #VALUE!, #VALUE!, 1; #VALUE!, 1, #VALUE!}, 0)
and returns {1, 0, 0; 0, 0, 1; 0, 1, 0}.
Step 6 - Return values if TRUE (1)
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
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:$E$5), 0), $F$3:$F$5, "")
becomes
IF({1, 0, 0; 0, 0, 1; 0, 1, 0}, $F$3:$F$5, "")
becomes
IF({1, 0, 0; 0, 0, 1; 0, 1, 0}, {10; 20; 30}, "")
and returns {10,"","";"","",20;"",30,""}.
Step 7 - 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(IF(IFERROR(SEARCH(TRANSPOSE(FILTERXML("<A><B>"& SUBSTITUTE(B3, " ", "</B><B>") & "</B></A>", "//B")), $E$3:$E$5), 0), $F$3:$F$5, ""))
becomes
TRANSPOSE({10,"","";"","",20;"",30,""})
and returns {10,"","";"","",30;"",20,""}.
Step 8 - Concate values in array
The TEXTJOIN function allows you to combine text strings from multiple cell ranges and also use delimiting characters if you want.
TEXTJOIN(" ", TRUE, TRANSPOSE(IF(IFERROR(SEARCH(TRANSPOSE(FILTERXML("<A><B>"& SUBSTITUTE(B3, " ", "</B><B>") & "</B></A>", "//B")), $E$3:$E$5), 0), $F$3:$F$5, "")))
becomes
TEXTJOIN(" ", TRUE, {10,"","";"","",30;"",20,""})
and returns "10 30 20".
7. Lookup each substring in a value and join the result - Excel 365
Excel 365 formula in cell C3:
Explaining formula
Step 1 - Split value into substrings
The TEXTSPLIT function splits a string into an array based on delimiting values.
Function syntax: TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])
TEXTSPLIT(B3, " ", , TRUE)
becomes
TEXTSPLIT("anil singh raj", " ", , TRUE)
and returns {"anil","singh","raj"}.
Step 2 - Match values and return the corresponding values
The LOOKUP function find a value in a cell range and return a corresponding value on the same row.
Function syntax: LOOKUP(lookup_value, lookup_vector, [result_vector])
LOOKUP(TEXTSPLIT(B3, " ", , TRUE), $E$3:$E$5, $F$3:$F$5)
becomes
LOOKUP({"anil","singh","raj"},{"anil";"raj";"singh"},{10;20;30})
and returns {10,30,20}.
Step 3 - Join the corresponding values
The TEXTJOIN function combines text strings from multiple cell ranges.
Function syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
TEXTJOIN(" ", TRUE, LOOKUP(TEXTSPLIT(B3, " ", , TRUE), $E$3:$E$5, $F$3:$F$5))
becomes
TEXTJOIN(" ", TRUE, {10,30,20})
and returns "10 30 20".
User defined function category
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
This article demonstrates how to convert a range of cells containing strings separated by a delimiter into a range of […]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]
The SUBSTITUTE and REPLACE functions can only handle one string, the following User-Defined Function (UDF) allows you to substitute multiple […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]
This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]
Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
In this vba tutorial I am going to show you how to return values from an udf, depending on where […]
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]
Functions in this article
More than 1300 Excel formulas
Excel categories
4 Responses to “Split search value using a delimiter and search for each substring”
Leave a 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 < and > becomes >
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.
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
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 FunctionRick 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 FunctionThank 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