Searching with a single cell

CoolSnaz

New Member
Joined
Jul 14, 2004
Messages
22
Hi folks, I was wondering if one of all you smart people could help me find a formula...
This is what I am trying to do:

I have a cell with the following information in it:
2/1 Andromeda_slot4 connected 580 normal full 1000 1000BaseSX

I would like to get the word "connected" out of the cell and in to a cell of its own?

But it is not that simple, because I know you can search the cell for the word "connected" and it will return the start position and from there I know you can get the entire word using "=mid(A1,'start_num','num,char')" and job done...

However the word isn’t always "connected" and isn't always in the same start position, and here lies the problem.

Is there a command that tells me the start position of the word "connected" or the word "disabled" or the word "notconnected"?

I would really appreciate any assistance with this
Many Thanks
CoolSnaz
 

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.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Try:

=LOOKUP(2,1/ISNUMBER(SEARCH({"connected","disabled","notconnected"},A1)),{"connected","disabled","notconnected"})


or maybe better:

=LOOKUP(2,1/ISNUMBER(SEARCH({"","connected","disabled","notconnected"},A1)),{"not found","connected","disabled","notconnected"})
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

If your data is in A1 then

in B1 enter connected

then in C1 have this formula

=MID(A1,FIND(B1,A1),LEN(B1))

not sure how you decide which word to look for!
 

CoolSnaz

New Member
Joined
Jul 14, 2004
Messages
22
Thank you, Thank you, Thank you!

That work beautifully, ten times better than anything I had come up with!

Many Thanks
CoolSnaz
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Make a list of the words/phrases you want to search for, eg:

not connected
connected
disabled

and name it List using Insert|Name|Define.

If the text to be searched is in A1, in a spare cell type:

=INDEX(List,MATCH(TRUE,ISNUMBER(SEARCH(List,A1)),FALSE))

and press Ctrl+Shift+Enter, not just Enter. If correctly entered Excel will surround this array formula with curly braces {}.

Note that "not connected" must appear before "connected" in the list to ensure that the "connected" within "not connected" isn't found first.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,407
Messages
5,547,774
Members
410,811
Latest member
adustin42
Top