Building A Complex Formula In #Excel

Here is the process I used to build a complicated formula using 7 different functions in Excel:

You can download the file here to follow along. If you get a preview, look for the download arrow in the upper right hand corner.

The purpose of this post is not to teach you how to use each of the functions mentioned, but to show the process that I went through to build a complex formula. The process used in the example is quite long and requires significant explanation. I will provide the basics below, but I encourage you to watch the video for the full details.

Here’s the scenario: I have a long list of hyperlinks that reference my blog posts, such as these examples:

build3

I want to use formulas to extract the blog name, remove the “-“, convert it to PROPER case and change “excel” to “#Excel”, as indicated in the “Title” column:

build4

The resulting formula will be:

=IFERROR(PROPER(REPLACE(SUBSTITUTE(MID(B1,28,(LEN(B1)-28)),”-“,” “),FIND(“excel”,MID(B1,28,(LEN(B1)-28))),5,”#Excel”)),””)

The list of functions used and their definitions is as follows:

build2

For this exercise, the hyperlink is located in cell B1.

The first step is to trim off the “http://www.excel-bytes.com/” at the beginning and the “/” at the end. We will use the MID and LEN functions. The first part of the hyperlink is always the same and is 27 characters. So we will always be able to extract the MID text starting at character 28. Formula for this is:

=MID(B1,28,(LEN(B1)-28))

And it results in the following:

determining-items-not-in-a-list-in-excel

Good start. Now I want to substitute the “-” with spaces. We will use the SUBSTITUTE function and wrap it around the current formula that we’ve built. The formula now is:

=SUBSTITUTE(MID(B1,28,(LEN(B1)-28)),”-“,” “)

And it results in the following:

determining items not in a list in excel

Getting there. Now I want to replace “excel” with “#Excel”. Here we will use the REPLACE function and wrap it around what we’ve currently built. That will be:

=REPLACE(SUBSTITUTE(MID(B1,28,(LEN(B1)-28)),”-“,” “),FIND(“excel”,MID(B1,28,(LEN(B1)-28))),5,”#Excel”)

And now we have:

determining items not in a list in #Excel

Note that we needed to use the FIND function to locate where “excel” started

OK, almost home. We now will wrap all this in the PROPER function so that each word begins with a capital letter:

=PROPER(REPLACE(SUBSTITUTE(MID(B1,28,(LEN(B1)-28)),”-“,” “),FIND(“excel”,MID(B1,28,(LEN(B1)-28))),5,”#Excel”))

And we get:

Determining Items Not In A List In #Excel

Perfect! The only thing left to do is wrap all that in an IFERROR function so that if there isn’t a hyperlink in the appropriate cell, it will return a blank cell:

=IFERROR(PROPER(REPLACE(SUBSTITUTE(MID(B1,28,(LEN(B1)-28)),”-“,” “),FIND(“excel”,MID(B1,28,(LEN(B1)-28))),5,”#Excel”)),””)

And now it’s perfect! I can now copy this down through my whole list of links and have it return the topic of the post with a hashtag in front of Excel for ease of use with Twitter and Google Plus!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , , , , , , ,

No comments yet.

Leave a Reply