Over the last four weeks, you saw how to create your own macros in Excel, how to store them, and how to use them. However, sometimes what you need isn’t really a macro. By that I mean that you cannot simply move the cursor around and use the built-in Excel functions to complete the task you want done. An example of that uses the list of files shown below in which you want to extract just the filename from column A to column B, removing all the folder (path) references.
As you can see in this list, the number of folders and the length of the path varies. We cannot simply look for the text after the first, second, or third forward slash to get the filename. Nor is there a way to ask Excel to get all of the text after the last slash in the filename even though that is exactly what we want. Excel has the FIND() function which allows you to get the first location of a character in a string. Because the FIND() function allows you to specify the character that you start at, I suppose there would be a way to iterate through the file path to find the last slash and then assume that all text after the last slash defines the filename. For example, suppose you had the path:
The result of FIND(“\”,A2,1) would be 3 because the third character in the path is a slash. Now you could take the result of the first find, increment the value by 1, and use it as the starting point for the new search as in FIND(“\”,A2, 4). This time you get a value of 14 which is the position of the next slash. You would continue to repeat the FIND() function incrementing the previous result by 1 until FIND() returns no value.
While this technique would work and you would eventually find the last slash and then using the MID() function you could retrieve just the filename, this process is a bit clumsy. Wouldn’t it be better if you could search the string from the right? Unfortunately, Excel does not provide such a function. However, you could write that function yourself by created a UDF (User Defined Function). Here’s how.
First, open the Developer ribbon. If you do not see the Developer tab, refer back to my first macro post to learn how.
Next click the Visual Basic icon in the Code group of the ribbon. This opens the Visual Basic for Applications development environment. The panels on the left shows the object model for the current spreadsheet
Right click on Microsoft Excel Objects and select Insert –> Module as shown in the following figure. This adds a Modules folder and a default code module named Module1. (While you can rename the code module, there will be no need to do that here for what we are going to do.)
Next begin defining a function by entering the word: Function followed by the name of the function you want to create. In this case, the first function I will show you how to create is one that reverses the order of the characters in the string so that the first shall be last and the last shall be first.
(This post is not meant to teach you programming, so I will assume that you know some Visual Basic from here on out.)
I am going to pass a single parameter into this function to a variable scoped to the function simply named: text to receive the original string, and the function itself will return a string with the characters displayed in reverse order. Therefore, I will enter the line:
Function RevStr(text As String) As String
Next I will create a simple For-Next loop to build the new string beginning with the characters at the end of the original string appending one character at a time to a new string using the following code:
For i = Len(text) To 1 Step -1 RevStr = RevStr + Mid(text, i,1) Next i
That is all you need for this function. You can actually test this function by returning to the Excel spreadsheet (click the small green Excel icon beneath the word File in the menu) and entering the command: =RevStr(A1) in cell B1. But wait, we are not done yet.
Now that we can reverse the string, we need to find the first slash, and then retrieve only the characters up to that first slash but not including the slash.
To generalize this function a little, I will pass two parameters into it. The first parameter: text is the original string again, and the second parameter: searchStr is the character that I’m looking for, a slash in this case. Again this function returns a string which should be only the filename. The code for this function appears in the next figure along with the reverse code function.
Note that after calling the RevStr function, I can use the InStr command to find the first occurrence of the slash character. (Ok, I know I could have used the InStrRev command and skipped creating the RevStr function entirely, but you never know when you might need a reverse string function.)
The InStr function returns either the column position where it finds the search character or it returns a zero if it does not find the character. Therefore, I use a little IF-THEN-ELSE block to get the filename that follows the last slash or returns the entire original string.
Apply this function through the rest of column B using an expression such as the following beginning in cell B2:
After adding this function to all the cells from B2 down you should see the results of your user defined functions displaying only the filename for each address in column A as shown in the following results:
You might ask whether we could get rid of the drive reference in the last row of the previous figure. Of course you can. All it takes is a bit more code to check for a colon when there is no slash. But I’ve left that enhancement up to you to try.
So congratulations. You’ve just created your first UDF (User Defined Function) in Excel that you can now use like any other Excel function. You might note as you begin using your new function that it even appears in the Insert Function dialog and when entering the function, you get the same tooltip style help to fill in the parameters that you get with every other built-in Excel function.
Now that you can create macros and functions within Excel, you can tell your friends that you are an Excel programmer. I’ll come back later to talk about creating and using forms in Excel to collect data. First however, we will return to DAX to look at some of the things it can help us with when working in PowerPivot.