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

Question

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?

Thanks in advance

Answer 1

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.

Addendum:
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")

Answer 2

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

Answer 3

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);"")

Obviously you can adapt it to your needs.