I am trying to get my head around this huge formula and wondering if anyone can help?

I have a cell B7 which contains any of the values (outdoor, indoor, 3g astroturf, etc.). I want it to search that cell, see if the text in that cell contains the value and if it does, substitute, but if it doesn't, move on and check for the next value.

Simple, right?

Here's what I have so far:

=IF(SEARCH("outdoor",E7),SUBSTITUTE(B7,"<format>","outdoor"),"")=IF(SEARCH("indoor",E7),SUBSTITUTE(B7,"<format>","indoor"),"")=IF(SEARCH("9-hole",E7),SUBSTITUTE(B7,"<format>","9-hole"),"")=IF(SEARCH("18-hole",E7),SUBSTITUTE(B7,"<format>","18-hole"),"")=IF(SEARCH("club",E7),SUBSTITUTE(B7,"<format>","club"),"")=IF(SEARCH("venue",E7),SUBSTITUTE(B7,"<format>","venue"),"")=IF(SEARCH("astroturf",E7),SUBSTITUTE(B7,"<format>","astroturf"),"")=IF(SEARCH("3g-astroturf",E7),SUBSTITUTE(B7,"<surface>","3G astroturf"),"")=IF(SEARCH("grass",E7),SUBSTITUTE(B7,"<surface>","grass"),"")=IF(SEARCH("hard-macadam",E7),SUBSTITUTE(B7,"<surface>","hard macadam"),"")=IF(SEARCH("concrete",E7),SUBSTITUTE(B7,"<surface>","concrete"),"")=IF(SEARCH("clay",E7),SUBSTITUTE(B7,"<surface>","clay"),"")=IF(SEARCH("indoor",E7),SUBSTITUTE(B7,"<surface>","indoor"),"")=IF(SEARCH("artificial",E7),SUBSTITUTE(B7,"<surface>","artificial"),"")=IF(SEARCH("synthetic-rubber",E7),SUBSTITUTE(B7,"<surface>","synthetic rubber"),"")=SUBSTITUTE(B7,"<venue>",A7)

Any help massively appreciated. Thanks

  • Will the words from the list only happen once per cell or could there be multiples? – Scott Craner Aug 4 '16 at 15:49
  • 1
    What is the purpose of the formula? A personal change on words? Say you misspelled the word "balls" and called it "calls". You could Control+H, Finall all words "calls", replace all with "balls". Probing the reason behind this formula. – ejbytes Aug 5 '16 at 1:51
  • Why not put up a print screen to show the relationship explicitly. Your words leaved lots of guesswork to the onlooker. If someone sees what you are trying to do, he/she might come up with a simpler solution. – ejbytes Aug 5 '16 at 1:53
  • Also it would help to list out what you want explicitly, instead of putting up an if statement of 20 else'ifs. Instead write it out: If B7 has the word beef, then replace beef with chicken. A list of pseudo-code. – ejbytes Aug 5 '16 at 1:57

Put your list of words in other cells:

enter image description here

Then reference those cells in an array formula:


Being an array formula it needs to be confirmed with Ctrl-Shift-Enter when exiting edit mode instead of Enter. If done properly Excel will put {} around the formula.

This will only replace the first word in the list that is found in the target text string. If two or more words on the list are present the others will remain.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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