# Searching with a single cell

#### CoolSnaz

##### New Member
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

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### fairwinds

##### MrExcel MVP
Hi,

Try:

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

or maybe better:

##### Well-known Member
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
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
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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,130
Messages
5,857,551
Members
431,885
Latest member
Rsdg

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back