Improve company productivity with a Business Account.Sign Up

x
?
Solved

How to create a vba code to search a string for values on a list and return with the value found?

Posted on 2014-12-09
3
Medium Priority
?
210 Views
Last Modified: 2014-12-10
I uploaded an example file with 4 different text strings in column “A” and the desired result in column “B” on sheet1.  The list of values to search for is found in cells on sheet2.
Exampe.xlsx
0
Comment
Question by:kbay808
  • 2
3 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40490094
Here is a user-defined function that will work exactly as you requested:
Public Function Find_Matches(Test_Cell As Range, Lookup_array As Range) As String
    'This returns all substring matches within a cell
    Dim cl As Object
    For Each cl In Lookup_array
        If InStr(1, Test_Cell, cl.Value, vbTextCompare) > 0 Then
            Find_Matches = Find_Matches & cl.Value & ", "
        End If
    Next cl
    If Find_Matches <> "" Then
        Find_Matches = Left(Find_Matches, Len(Find_Matches) - 2)
    Else
        Find_Matches = "NOT FOUND"
    End If
End Function

Open in new window


In cell B2 you would enter:
=find_matches(A2,Sheet2!$A$2:$A$5)

and you could then copy that to each cell below.  

If more than one match is found, they are all shown - separated by commas.

Regards,
-Glenn
0
 

Author Closing Comment

by:kbay808
ID: 40490528
Perfect!!!  Thank you very much.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40491700
You're welcome.  You'd be surprised how often I use this UDF myself.

-Glenn
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question