Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
up vote 1 down vote accepted

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

share|improve this answer
    
+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. – Kaze 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

share|improve this answer
    
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);"")

Obviously you can adapt it to your needs.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .