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]

	A	B (this would be Tab called 'Sprint MMBS Inbox')	C	D
1	From	Subject	Received	Size
2	Stair Candy	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	10:29 PM	117 KB
3	Adegeye Adebimpe	RE: WCN2 - R12 Pre Shipment Report 05-19-2015 S2D SF03UB727	9:58 PM	214 KB
4	Kaneshia Andrews	RE: Logistics COGS MLA East: R2W CL34XC359 & CL80XC311 Site Swap - goal is to have DO 8255581070 SMM-2CD00410EX (1) Reversed/Deleted	8:37 PM	156 KB
5	Stair Candy	Logistics COGS MLA East: R2W CL34XC359 & CL80XC311 Site Swap - goal is to have DO 8255581070 SMM-2CD00410EX (1) Reversed/Deleted	8:24 PM	833 KB
6	Minkyu Lee	RE: Pre-ship request for 05/20/2015	7:48 PM	74 KB
7	Greg Lipham	Re: Pre-ship request for 05/20/2015	7:30 PM	35 KB
8	Shin Park	RE: May Network Closing Schedule_NV Equip_Cell Site	5:45 PM	56 KB
9	SprintMMBS	RE: COGS report shipping confirmation (NY33HO062)	5:17 PM	290 KB
10	Kevin Chung	20150520 Revenue Update 5:05pm download	5:13 PM	51 KB

<TBODY>
</TBODY>

 

[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...

Row\Col	A​	B​
1​	Site-Id(SH.Name2)
2​	IN03XC115	#N/A​
3​	NY33HO062	SprintMMBS

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...

Row\Col	A​	B​
1​	Site-Id(SH.Name2)
2​	IN03XC115	#N/A​
3​	NY33HO062	SprintMMBS

<TBODY>
</TBODY>

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.