Finding a text string in a string of text, then returning cell related data

[erollman]

Hi folks,
I have a problem that has me stumped sideways til next Thursday. Maybe I am just fried because it's Friday and I have been struggling with this all week, or maybe I am still not seeing the bigger picture. This is why I come to ask help from the experts.

Problem:
Searching a string of text in a column to find a specific string of text

Criteria:
Need to be able to identify which cell the particular string of text is found in(if any), and return data from a cell elsewhere in the document, but on the same row.

Example:


[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]OEM Number
[/TD]
[TD]Search Keywords
[/TD]
[TD]Data to be returned
[/TD]
[/TR]
[TR]
[TD]11604
[/TD]
[TD]160033, 16-1000, 396-703
[/TD]
[TD]Jerry
[/TD]
[/TR]
[TR]
[TD]160033
[/TD]
[TD]11605, 11662, 113-275, 11604
[/TD]
[TD]Mack
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Basically I need the formula to be able to find 11604 or 160033 in colum "Search Keywords" and return data from a cell related on the same row as 11604 or 160033, ie. Jerry or Mack. I am sure there is an easy way of doing this, but I am stumped. It's gotta be the impending weight I'll gain from Turkey day next week.

Any help would be greatly appreciated.


erollman

 

[DeBeuz]

Hi erollman,

I currently have only a Dutch version of Excel at hand, so I'm not able to translate all functions to their English counterpart.
But I'll try to give you an idea.
Assume in your example that the data is in the columns A:C.
If so you could try setting up a formula to try to match both values and when found one, returns the value from column C:

    X1=IF(OR(find_in_str<find_in_str>(B1,"11604"),find_in_str<find_in_str>(B1,"160033")),C1,"")

You need to replace 'find_in_str<find_in_str>' with the correct English Excel function.

I hope that, despite not be able to give full working solution, it will help you.

Paul</find_in_str></find_in_str></find_in_str>

 

[DutchDiggy]

I think a INDEX/MATCH should do it for you. I don't know how your data is build, so I can't come up with a formula for you.

You can take a look at this link how index/match works. Then you have to apply the match formula with an OR function for the values

How to Use INDEX MATCH MATCH


@ DeBeuz: I like your disclaimer as I also tend to forget some replacements of the Ducht separators
(Ik denk dat ik die ook gewoon even lekker van je ga kopieren, beter goed gejat dan slecht verzonnen )

 

[mumps]

Hi erollman. Assuming your data is on columns A to C, I'm not sure if you want to find each value of column A in anywhere in column B or just in the same as row the searched value. Try this macro and then we can go from there if it's not what you want. The returned value is copied to Sheet2.

Sub test()
    Application.ScreenUpdating = False
    Dim bottomA As Integer
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim bottomB As Integer
    bottomB = Range("B" & Rows.Count).End(xlUp).Row
    Dim rng1 As Range
    Dim rng2 As Range
    For Each rng1 In Range("A2:A" & bottomA)
        For Each rng2 In Range("B2:b" & bottomB)
            If rng2 Like "*" & rng2 & "*" Then
                rng2.Offset(0, 1).Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            End If
        Next rng2
    Next rng1
    Application.ScreenUpdating = True
End Sub

 

[Aladin Akyurek]

Hi folks,
I have a problem that has me stumped sideways til next Thursday. Maybe I am just fried because it's Friday and I have been struggling with this all week, or maybe I am still not seeing the bigger picture. This is why I come to ask help from the experts.

Problem:
Searching a string of text in a column to find a specific string of text

Criteria:
Need to be able to identify which cell the particular string of text is found in(if any), and return data from a cell elsewhere in the document, but on the same row.

Example:


[TABLE="width: 500, align: center"]
<TBODY>[TR]
[TD]OEM Number
[/TD]
[TD]Search Keywords
[/TD]
[TD]Data to be returned
[/TD]
[/TR]
[TR]
[TD]11604
[/TD]
[TD]160033, 16-1000, 396-703
[/TD]
[TD]Jerry
[/TD]
[/TR]
[TR]
[TD]160033
[/TD]
[TD]11605, 11662, 113-275, 11604
[/TD]
[TD]Mack
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Basically I need the formula to be able to find 11604 or 160033 in colum "Search Keywords" and return data from a cell related on the same row as 11604 or 160033, ie. Jerry or Mack. I am sure there is an easy way of doing this, but I am stumped. It's gotta be the impending weight I'll gain from Turkey day next week.

Any help would be greatly appreciated.


erollman

A:C houses the sample you posted.

[TABLE="width: 627"]
<COLGROUP><COL style="WIDTH: 106pt; mso-width-source: userset; mso-width-alt: 5034" width=142><COL style="WIDTH: 218pt; mso-width-source: userset; mso-width-alt: 10325" width=290><COL style="WIDTH: 152pt; mso-width-source: userset; mso-width-alt: 7224" width=203><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2474" width=70><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2417" width=68><TBODY>[TR]
[TD="class: xl63, width: 142, bgcolor: transparent"]OEM Number[/TD]
[TD="class: xl63, width: 290, bgcolor: transparent"]Search Keywords[/TD]
[TD="class: xl63, width: 203, bgcolor: transparent"]Data to be returned[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 70, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 142, bgcolor: transparent, align: right"]11604[/TD]
[TD="class: xl63, width: 290, bgcolor: transparent"]160033, 16-1000, 396-703[/TD]
[TD="class: xl63, width: 203, bgcolor: transparent"]Jerry[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 70, bgcolor: transparent, align: right"]160033[/TD]
[TD="class: xl63, width: 68, bgcolor: transparent"]Jerry[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 142, bgcolor: transparent, align: right"]160033[/TD]
[TD="class: xl63, width: 290, bgcolor: transparent"]11605, 11662, 113-275, 11604[/TD]
[TD="class: xl63, width: 203, bgcolor: transparent"]Mack[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 70, bgcolor: transparent, align: right"]11604[/TD]
[TD="class: xl63, width: 68, bgcolor: transparent"]Mack[/TD]
[/TR]
</TBODY>[/TABLE]

F2, control+shift+enter, not just enter, and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(","&$E2&",",","&SUBSTITUTE($B$2:$B$3," ","")&","),$C$2:$C$3)

 

[DeBeuz]

Hi,

I think it should be FIND.

 

[Aladin Akyurek]

Hi,

I think it should be FIND.

If you are addressing my usage of SEARCH: There is no necessity to be case-sensitive, so no need for FIND.

 

[DutchDiggy]

Alading, I think he means his own post #2 for find_in_str ....

 

[erollman]

Great. Let me try a few of these to see what will work for my needs.

Aladin, I think Da Beuz was giving his interpretation of what he thinks find_in_strg is in our English copy of excel is, but just my assumption.

Thanks for the help so far. I'll get back with the results.

 

[erollman]

A:C houses the sample you posted.

[TABLE="width: 627"]
<tbody>[TR]
[TD="class: xl63, width: 142, bgcolor: transparent"]OEM Number
[/TD]
[TD="class: xl63, width: 290, bgcolor: transparent"]Search Keywords
[/TD]
[TD="class: xl63, width: 203, bgcolor: transparent"]Data to be returned
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 70, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 142, bgcolor: transparent, align: right"]11604
[/TD]
[TD="class: xl63, width: 290, bgcolor: transparent"]160033, 16-1000, 396-703
[/TD]
[TD="class: xl63, width: 203, bgcolor: transparent"]Jerry
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 70, bgcolor: transparent, align: right"]160033
[/TD]
[TD="class: xl63, width: 68, bgcolor: transparent"]Jerry
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 142, bgcolor: transparent, align: right"]160033
[/TD]
[TD="class: xl63, width: 290, bgcolor: transparent"]11605, 11662, 113-275, 11604
[/TD]
[TD="class: xl63, width: 203, bgcolor: transparent"]Mack
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 70, bgcolor: transparent, align: right"]11604
[/TD]
[TD="class: xl63, width: 68, bgcolor: transparent"]Mack
[/TD]
[/TR]
</tbody>[/TABLE]

F2, control+shift+enter, not just enter, and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(","&$E2&",",","&SUBSTITUTE($B$2:$B$3," ","")&","),$C$2:$C$3)

Hi Aladin,
Sorry, but I am unfamiliar with the F2, control+shift+enter, and copy down that you are mentioning. What does it do? It seems you have been able to make the data work the way I need it to, but I am uncertain of what you are asking. Is the lookup supposed to include 9.9999999999+307, or is that a pasting error?

Please forgive me for my inexperience, but thank you for your time and consideration with my question.