Search for word in cell

[seacrest]

I want to find and certain word in a certain cell.This word will most likely be in a sentence.Can this be done without using vba

 

[Candy Samsung]

[TABLE="width: 887"]
<TBODY>[TR]
[TD="class: xl65, width: 29, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 32, bgcolor: transparent"]A
[/TD]
[TD="class: xl65, width: 985, bgcolor: transparent"]B (this would be Tab called 'Sprint MMBS Inbox')
[/TD]
[TD="class: xl65, width: 81, bgcolor: transparent"]C
[/TD]
[TD="class: xl65, width: 55, bgcolor: transparent"]D
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]From
[/TD]
[TD="class: xl65, bgcolor: transparent"]Subject
[/TD]
[TD="class: xl65, bgcolor: transparent"]Received
[/TD]
[TD="class: xl65, bgcolor: transparent"]Size
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]Stair Candy
[/TD]
[TD="class: xl65, bgcolor: transparent"]KUDO's on Using a great tool 5 20 2015 // RE: Group Inbox Emails Search Engine 5 16 2015 - Tool only searches SprintMMBS Inbox, all Folders, even Archived
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10:29 PM
[/TD]
[TD="class: xl65, bgcolor: transparent"]117 KB
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent"]Adegeye Adebimpe
[/TD]
[TD="class: xl65, bgcolor: transparent"]RE: WCN2 - R12 Pre Shipment Report 05-19-2015 S2D SF03UB727
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]9:58 PM
[/TD]
[TD="class: xl65, bgcolor: transparent"]214 KB
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]Kaneshia Andrews
[/TD]
[TD="class: xl65, bgcolor: transparent"]RE: Logistics COGS MLA East: R2W CL34XC359 & CL80XC311 Site Swap - goal is to have DO 8255581070 SMM-2CD00410EX (1) Reversed/Deleted
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]8:37 PM
[/TD]
[TD="class: xl65, bgcolor: transparent"]156 KB
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent"]Stair Candy
[/TD]
[TD="class: xl65, bgcolor: transparent"]Logistics COGS MLA East: R2W CL34XC359 & CL80XC311 Site Swap - goal is to have DO 8255581070 SMM-2CD00410EX (1) Reversed/Deleted
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]8:24 PM
[/TD]
[TD="class: xl65, bgcolor: transparent"]833 KB
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"]Minkyu Lee
[/TD]
[TD="class: xl65, bgcolor: transparent"]RE: Pre-ship request for 05/20/2015
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]7:48 PM
[/TD]
[TD="class: xl65, bgcolor: transparent"]74 KB
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl65, bgcolor: transparent"]Greg Lipham
[/TD]
[TD="class: xl65, bgcolor: transparent"]Re: Pre-ship request for 05/20/2015
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]7:30 PM
[/TD]
[TD="class: xl65, bgcolor: transparent"]35 KB
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl65, bgcolor: transparent"]Shin Park
[/TD]
[TD="class: xl65, bgcolor: transparent"]RE: May Network Closing Schedule_NV Equip_Cell Site
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]5:45 PM
[/TD]
[TD="class: xl65, bgcolor: transparent"]56 KB
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl65, bgcolor: transparent"]SprintMMBS
[/TD]
[TD="class: xl65, bgcolor: transparent"]RE: COGS report shipping confirmation (NY33HO062)
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]5:17 PM
[/TD]
[TD="class: xl65, bgcolor: transparent"]290 KB
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl65, bgcolor: transparent"]Kevin Chung
[/TD]
[TD="class: xl65, bgcolor: transparent"]20150520 Revenue Update 5:05pm download
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]5:13 PM
[/TD]
[TD="class: xl66, bgcolor: transparent"]51 KB
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Candy Samsung]

I tried to use same formula, but it won't except a group range on another tab.

 

[Aladin Akyurek]

I tried to use same formula, but it won't except a group range on another tab.

It is not clear what you want to search, where exactly, and what to return iwhen successful. Care to clarify?

 

[Candy Samsung]

It is not clear what you want to search, where exactly, and what to return iwhen successful. Care to clarify?

First worksheet, Site ID, has a cell tower site in cell A2, NY33HO062.
Second worksheet, Sprint MMBS Inbox, above has column B that I want to search and find all individual cells that contain word, NY33HO062. Notice how word is in a sentence form or subject line.
I understand I'll need to place the formula in the worksheet, Sprint MMBS Inbox, so I can find all the rows that contain word, NY33HO062.
I'll be using same formula to find over 1500 different Site ID's.
Can this be done?

 

[Aladin Akyurek]

First worksheet, Site ID, has a cell tower site in cell A2, NY33HO062.
Second worksheet, Sprint MMBS Inbox, above has column B that I want to search and find all individual cells that contain word, NY33HO062. Notice how word is in a sentence form or subject line.
I understand I'll need to place the formula in the worksheet, Sprint MMBS Inbox, so I can find all the rows that contain word, NY33HO062.
I'll be using same formula to find over 1500 different Site ID's.
Can this be done?

I don't see any specification in your reply for what to return when the search is successful. Hope what follows is a guess in the ballpark...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]Site-Id(SH.Name2)[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]IN03XC115[/td][td]

#N/A​

[/td][/tr]

[tr][td]

3​

[/td][td]NY33HO062[/td][td]SprintMMBS[/td][/tr]
[/table]

B2, copied down:

=LOOKUP(9.99999999999999E+307,
    SEARCH(A2,'Sprint MMBS Inbox'!$B$2:$B$10),
    'Sprint MMBS Inbox'!$A$2:$A$10)

 

[Candy Samsung]

I don't see any specification in your reply for what to return when the search is successful. Hope what follows is a guess in the ballpark...

[TABLE="class: grid"]
<TBODY>[TR]
[TD]Row\Col
[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Site-Id(SH.Name2)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]IN03XC115
[/TD]
[TD]

#N/A​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]NY33HO062
[/TD]
[TD]SprintMMBS
[/TD]
[/TR]
</TBODY>[/TABLE]


B2, copied down:

=LOOKUP(9.99999999999999E+307,
    SEARCH(A2,'Sprint MMBS Inbox'!$B$2:$B$10),
    'Sprint MMBS Inbox'!$A$2:$A$10)

Brilliant!!! I had to tweak it a bit to get what I needed. Sorry, I'm not great at explaining "specification", as I'm not great with Excel. But you got me far enough to where I can figure the remaining out. Thank you!!!

 

[Aladin Akyurek]

Brilliant!!! I had to tweak it a bit to get what I needed. Sorry, I'm not great at explaining "specification", as I'm not great with Excel. But you got me far enough to where I can figure the remaining out. Thank you!!!

Glad to help.