Text Detective: Fixing Text in Excel with IF and FIND


A good IF statement is like a good hound dog--just give him a sniff o' what he needs to find and turn him loose to find the trail!

Today's topic is something I use literally every day:  IF statements whose behavior is driven by looking for certain text strings.  There are COUNTLESS uses of this thing, but a very common one is cleaning long lists of hyperlinks, some of which have the "http://" and some don't.

In English first, we want our IF statement to do the following:

"If I can't find 'http://' in the string, concatenate 'http://' with the string; otherwise, just leave the text string unchanged"

Excel-speak for "can't find" is a combination of two formulas:  ISERROR and FIND.

ISERROR(FIND("http://",N7))

When the FIND function doesn't find it's target, it generates an error--so putting that inside an ISERROR gives us the TRUE/FALSE we need to power our IF statement.

Need additional help with IF, FIND, ISERROR, CONCATENATE, or other Excel elements?  Reach out today via live chat, email, or phone and we'll get your issue fixed in minutes?