Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

3 Answers 3

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. –  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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.