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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

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,589
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,192

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,109,411
Messages
5,528,621
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top