Clean & Standard: Using IF With CONCATENATE In Excel


If you've ever come across a column of data that is woefully unstandardized and you need to clean it, this post is for you!

Let's say we have a list of hyperlinks, but some of them are missing the initial http://

In the new column, we want to start out with an IF function.

The first term of every IF function is the condition we're testing.  To keep it simple, let's check each of our URLs to see if the first four letters are "http".

If this is true, that means our hyperlink is good as-is and we can just let the answer be the cell's current contents.  If this is false, we want to concatenate "http://" with the cell's contents.

That's it!  We drag the cell formula down through the other cells and we've got our squeaky clean column of hyperlinks.

 Need Excel help with the IF function, CONCATENATE operator, or other Excel elements?  Please reach out to us on live chat where Sherpas are standing by to help you!