Excel Formulas are Like Latin

I'm doing a spot of data manipulation and importing for MotorShout at the moment and am currently importing every Mercedes-Benz ever. Sounds like fun? Not really. Anyway, the source data is in an Excel spreadsheet which I'm modifying to match up with our existing SQL Server input table. The input data for Mercedes-Benz is missing the car name’s prefixes. e.g. "CLK500" is missing the “CLK” but I do have the "500" and the “CLK-Class” family name in other columns. I want to merge the “500” with the “CLK” from the family name to get the model name. Easy peasy? Hardly!

Here is the alien Excel formula that rubs my 15 years of coding experience the wrong way:

=CONCATENATE(IF(ISERROR(SEARCH("-Class",B2)),"",SUBSTITUTE(B2, "-Class","")), D2)

Why is this alien? Well for a start a simple Replace() method would have done this job fine except Excel already has a REPLACE() function... which replaces text at the given indexes, WTF? OK, so they have SUBSTITUTE() that does what I want, fair enough, let's get on with it.

OK, now what if the family name doesn't contain "-Class"? I don't want to prefix the model name in that instance so my first thought is I'd use an InStr(), or IndexOf() or Contains() method to check if the string is in the family name, sorry wrong again! I have to use Excel's SEARCH() function. But do you know what makes SEARCH() oh so special? If the string isn't found in the source it THROWS AN ERROR! The endlessly helpful #VALUE! error, fun times indeed. In their wisdom Microsoft therefore ensured Excel has an ISERROR() function which, you guessed it, checks for an error. Sorted!

Finally, a simple IF() statement wrapping that ISERROR() condition and we're almost there. Just need to concatenate (yes, the function uses that name) the newly trimmed out family name with the model name and voila! Simple!

*sigh*

This is what I would have liked to write (C# mashed into Excel please!):

=B2.Contains("-Class") ? B2.Replace("-Class", " ") : "" + D2;