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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

DeBeuz

Active Member
Joined
Jun 6, 2012
Messages
448
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:

DutchDiggy

Board Regular
Joined
Sep 10, 2013
Messages
210
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

Well-known Member
Joined
Apr 11, 2012
Messages
9,745
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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)
 

erollman

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

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

Watch MrExcel Video

Forum statistics

Threads
1,114,560
Messages
5,548,737
Members
410,869
Latest member
eSoftToolsNSFtoPSTConvert
Top