# Excel notation that gives FIND the ability to look for multiple strings

So I've been working with a spreadsheet that has a list of schools names. I pull data from this spreadsheet to create a roster that limits the size of text in each cell. In order to get the school names to fit, I have to remove the type of school from the name.

Example: Original name => Processed Name

• Generic Elementary => Generic
• Unnamed Middle School => Unnamed
• Across Town High School => Across Town

I can't look for the first space since some of the school names are two words long. I've been able to make this work with a highly nested IF statement.

``````=IFERROR(LEFT(J2,FIND("Elementary",J2)-2),IFERROR(LEFT(J2,FIND("Middle",J2)-2),IFERROR(LEFT(J2,FIND("High",J2)-2),J2)))
``````

The problem I have with this solution is that it is really long. What I would like is a way to have the FIND function search for more than one text string. I feel that would reduce this formula down to something much easier to read.

Does anyone have any ideas? Is there a more elegant way to do this?

-

Datatoo beat me to it (+1).
If you're up for an alternative that doesn't involve formulas, you could always use Excel's Find and Replace --

1. Ctrl + H
2. Type " middle school" (with a space before the word) into the Find what box
3. Leave Replace with empty.
4. Replace All
5. Repeat for each word/phrase that you want to remove from your cells.

If you're up for using a custom function, try this out:

``````Option Compare Text
Public Function REPLACETEXT(src As Range, crt As Range) As String
s = Trim(src.Value)
For Each c In crt
If InStr(1, s, Trim(c.Value)) Then newstr = Replace(s, Trim(c.Value), "")
Next c
REPLACETEXT = newstr
End Function
``````

Press ALT + F11. Paste the code into a new module.

Usage:

``````=REPLACETEXT(A1,D1:D3)
``````

where A1 contains the full text ("Really Cool Middle School") and D1:D3 contains the strings you want to remove from your cells (e.g. "middle school", "high school", "elementary")

-
+1this is really the easiest way, unless you need the original retained for some reason – datatoo Jun 3 '11 at 21:45
And also if you have to search for a bajillion substrings, a macro or formula would be a better solution. – Ellesa Jun 4 '11 at 12:25
like this simpler function, it is adaptable. – datatoo Jun 4 '11 at 13:10
While my personal prejudice is to avoid functions in Excel, I have to admit that this is the best solution I could hope for. Thanks! – Doltknuckle Jun 7 '11 at 15:27

Maybe not more elegant, but shorter, and with the same problem as yours regarding case sensitivity. Problems if the school ending string is not consistent, but perhaps they are.

``````=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J2,"Elementary",""),"Middle School",""),"High School","")
``````

Someone may create a more elegant user defined function, but you could add this to a module in the workbook.

`````` Public Function schType(Cell As String) As String
If InStr(1, LCase(Cell), "elementary school") > 0 Then schType = Replace(Cell, "elementary school", "")
If InStr(1, LCase(Cell), "high school") > 0 Then schType = Replace(Cell, "high school", "")
If InStr(1, LCase(Cell), "middle school") > 0 Then schType = Replace(Cell, "middle school", "")
End Function
``````

Then in the cell you want =schType(J2)
This was quick and looks for these specific strings, rather than partial strings in a case where you might have a school like "Middleton Elementary School", just looking for "middle" would get it wrong

-
This does work better than the Nested mess I have. I'm wondering if you can reduce this further by getting a search operator in there. I basically want to remove something if it matches any of the three strings. This does it one at a time. – Doltknuckle Jun 3 '11 at 19:53

Put "Elementary", "Middle School", "High School", "" in B1:B4. Then, assuming that your list is in column J and you want the processed list in column K, input the following formula in K2 confirming with Ctrl+Shift+Enter and copy down.

``````=SUBSTITUTE(J2;INDEX(B\$1:B\$4;MIN(IF(ISERROR(FIND(B\$1:B\$4;J2));999;ROW(B\$1:B\$4)))-ROW(B\$1)+1);"")
``````