Split search value using delimiter and search for each substring
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.
Formula in cell B1:
=SearchValues(A1,$D$1:$D$3,$E$1:$E$3)
User defined function
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 For i = 1 To j If search_col.Cells(i, 1) = Vl Then result = result & return_col.Cells(i, 1) & " " Next i Next Vl SearchValues = result End Function
How do I use the code above in my workbook?
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
Download excel *.xlsm file
Split search value using delimiter and search for each string.xlsm
How to count word frequency in a cell range [UDF]
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]
List files in a folder and subfolders [UDF]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
Search for a file in folder and subfolders [UDF]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
Split values equally into groups
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
Split words in a cell range into a cell each [UDF]
This post describes how to split words in a cell range into a cell each using a custom function. I […]
Filter unique distinct words from a cell range [UDF]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
Count unique distinct values by cell color
This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]
Substitute multiple text strings [UDF]
The SUBSTITUTE and REPLACE functions can only handle one string, the following User-Defined Function (UDF) allows you to substitute multiple […]
Count comma separated values [UDF]
I received an email from one of my seven blog readers. In Excel, I have a column, say A, with some […]
4 Responses to “Split search value using 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...
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.
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