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

erollman

New Member
Joined
Jan 26, 2011
Messages
10
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:


OEM Number
Search Keywords
Data to be returned
11604
160033, 16-1000, 396-703
Jerry
160033
11605, 11662, 113-275, 11604
Mack

<tbody>
</tbody>

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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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:
Code:
    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>
 
Last edited:
Upvote 0
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 ;) )
 
Upvote 0
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.
Code:
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
 
Upvote 0
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:


OEM Number
Search Keywords
Data to be returned
11604
160033, 16-1000, 396-703
Jerry
160033
11605, 11662, 113-275, 11604
Mack

<TBODY>
</TBODY>

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.

OEM NumberSearch KeywordsData to be returned
11604160033, 16-1000, 396-703Jerry 160033Jerry
16003311605, 11662, 113-275, 11604Mack 11604Mack

<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>
</TBODY>

F2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=LOOKUP(9.99999999999999E+307,SEARCH(","&$E2&",",","&SUBSTITUTE($B$2:$B$3," ","")&","),$C$2:$C$3)
 
Upvote 0
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.
 
Upvote 0
A:C houses the sample you posted.

OEM Number
Search Keywords
Data to be returned
11604
160033, 16-1000, 396-703
Jerry
160033
Jerry
160033
11605, 11662, 113-275, 11604
Mack
11604
Mack

<tbody>
</tbody>

F2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=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.
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top