Search for text

undemane

Board Regular
Joined
Nov 19, 2007
Messages
75
Group Name
Result
xxx-global-hds-xxx
hds
xxx-network-xxx-xxx
network
xxx-xxx-orcl-xxx-xxx
orcl

<tbody>
</tbody>
I have a table with the column on the left. I want to scan the column A (Group Name) for key words like HDS or Network or ORCL, and if I find them, then paste them to the column B. How do I do this?
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
You're just searching for those three words ONLY?

in B1
=IF(ISNUMBER(SEARCH("hds",A1)),"hds","")&IF(ISNUMBER(SEARCH("network",A1)),"network","")&IF(ISNUMBER(SEARCH("orcl",A1)),"orcl","")
 

undemane

Board Regular
Joined
Nov 19, 2007
Messages
75
For now, yes. Thank you for your help

You're just searching for those three words ONLY?

in B1
=IF(ISNUMBER(SEARCH("hds",A1)),"hds","")&IF(ISNUMBER(SEARCH("network",A1)),"network","")&IF(ISNUMBER(SEARCH("orcl",A1)),"orcl","")
 

maheshmaxi

Active Member
Joined
Dec 16, 2008
Messages
252
You can also use this following formula by creating a table in C & D columns like given below. Formula =LOOKUP(2^15,SEARCH(C1:C10,A2),D1:D10) where A2 is the cell contains Group Name "xxx-global-hds-xxx", just drag up to the end.

C

hds
D

hds
networknetwork
orclorcl

<tbody>
</tbody>
 

maheshmaxi

Active Member
Joined
Dec 16, 2008
Messages
252
If you want to search more words like those three words you have provided, you can simply add those in the C:D table and expand the range (rows) in lookup formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,521
Messages
5,529,326
Members
409,863
Latest member
stacy09
Top