Searching with a single cell

[CoolSnaz]

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

 

[fairwinds]

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]

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]

Thank you, Thank you, Thank you!

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

Many Thanks
CoolSnaz

 

[Andrew Poulsom]

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.