Solved: Non-VBA Solution: Identify Text String from Table and Return Text String
Hi All,
Normally I come here for VBA assistance, but today I am having difficulty with what I hope is a simple excel function problem.
In the attached workbook I have 2 tabs. The first tab, "Summary", has two columns of data. The second tab, "Data", has a list of text strings.
What I am trying to accomplish is this:
On the "Summary" tab, column B, I want to write a formula that checks the data in Column A against the text strings listed on the "Data" tab. The formula that I would want in Column B would return the text string only if it exists in column A.
For example Cell A2 on the "summary" tab has the words "Con12345 RK-NJ-MA USD$" If you refer to the data tab, you will notice that RK-NJ-MA is listed there. In Cell B2, I want to have the text string identified on the "data" tab returned, so only "RK-NJ-MA"
Keep in mind that the length of the text strings on the "Data" tab are going to vary in my much larger file.
Is there an easy way to do this without VBA? I am preparing a workbook for another team that is not excel savvy.
I can't think of a non-vba solution. Here's a function you can call as
=mysearch(A2,Data!A:A)
[VBA]Function MYSEARCH(Data, Rng)
Dim x As Long, Cel As Range
For Each Cel In Intersect(Rng, Rng.Parent.UsedRange)
x = InStr(1, Data, Cel)
If x > 0 And Cel <> "" Then
MYSEARCH = Cel
Exit Function
End If
Next
MYSEARCH = "-"
End Function
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
Thanks for the quick response, mdmackillop. I tried adding the function to a new module in my VBA window and then inserted the formula you provided, but received an error #NAME?
Was there a portion of the VBA code that I was supposed to edit? Might I add I am using Excel 2007 in case that makes a difference.
Perfect! For some reason I was able to copy everything into my main workbook, but it wasn't taking on the sample I posted earlier.
I did notice one thing when I added it to my main workbook, something that I overlooked when I originally posted. I need it to pickup the exact match in the table, and not the first possible match. So, for example there may be text strings in my "Data" tab that look similar:
RK-NJ
RK-NJ-MA
RK-NJ-CA
RK-NJ-CA-MXO
If the function is searching a cell with "RK-NJ-CA-MXO" it is picking the first potential match it finds on my data tab, in this case it is picking "RK-NJ"
What can I edit to make it search for the exact match instead of the first partial match?
This all depends on your data. Is the search string always separated by spaces? If not, how do we know where it starts/stops. A better sample helps test options.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
Try this
[VBA]Function MYSEARCH(Data, Rng)
Dim x, Cel As Range, i As Long
For Each Cel In Intersect(Rng, Rng.Parent.UsedRange)
x = Split(Data)
For i = 0 To UBound(x)
If x(i) = Cel Then
MYSEARCH = Cel
Exit Function
End If
Next
Next
MYSEARCH = "-"
End Function[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
Just wanted to thank you for saving me 200 hours of more frustration trying to find a non-vba solution. I had nested formulas within nested if statements that was just crying fail beforehand, and now i have a clean workbook that shouldn't require user intervention moving forward.