Solved: Non-VBA Solution: Identify Text String from Table and Return Text String

[goobers]

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.

Many Thanks as always.

-Goobers

[mdmackillop]

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]

[goobers]

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.

[mdmackillop]

Here's your sample file.

[goobers]

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?

[mdmackillop]

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.

[goobers]

The search string will always be separated by spaces on the Summary tab. And the strings on the Data tab will always be separated by "-"

[mdmackillop]

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]

[goobers]

mdmackillop -

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.

Thanks so much for your help!