Calc2Code Tips of the Day

Day 1 Part 2

Our next tip demonstrates that Calc2Code can operate on text just as well as on numbers. How often have you imported text into Excel and had to spend time parsing it to get a pieceof text (perhaps a number) out of the strings? This can be a time consuming task. We’re going to create two new functions to add to our Excel arsenal. The first will find the n-th occurrence of a given delimiter (or separator), like comma or colon. This will be used by another function, which will return the n-th item in the text list.

For example, given the text:

This,is,a,text,string

If we want to extract the second element, it will return the string “is”.

First, we’ll create the spreadsheet find_nth_a.xls.

Here’s the spreadsheet:

The way this spreadsheet works is by finding the first delimiter in the string with FIND() in G1. It then chops off the left side of that string in H1, so we have the remaining string after the first delimiter. We then repeat this one row down to FIND the first delimiter on that remaining portion, and then chop again. In this way, we end up with a table of where the first, second, third and so on instances of the delimiter were found. Once we go past the last one, FIND just returns errors. A shortcoming of this method is that it only allows us to find up to the fifteenth occurrence of the delimiter, but we can always copy the formulas down further if we think that’s a problem. Finally, given the table of locations of each delimiter (columns J and k), we use VLOOKUP to find the n-th one in B5.

Calc2Code’s configuration screen looks like this:

The inputs are the delimiter, the text, and which occurrence to find. Notice the return type is XLOPER. This allows us to pass back the error code from FIND if the occurrence input is too large. Notice also that we have checked the box “Make available for other Calc2Code projects” which causes the new find_nth function to be saved in Calc2Code’s ini file for use in other functions. You’ll see why in a moment.

We “Close” this and then click on the builder button and create a new function called find_nth that can be used in any spreadsheet.

One spreadsheet we want to use it in is one in which we’ll create get_nth. We are going to do this in find_nth_b.xls which is a bigger version of the previous spreadsheet:

In this spreadsheet, we are going to use the find_nth function in cells B13 and B14 to get the nth piece of the text string. Quite simply, if we want the fourth item, we find the fourth and fifth comma, and use MID to return the text in between those two commas. We do something special if the first text piece is requested, since that won’t be preceded by a comma.

Notice that this new get_nth function uses the old find_nth function. This is one of Calc2Code’s most powerful features; generated functions can be used as building blocks for more complex functions, which can in turn be used as building blocks for every more complex functions. And all the while the spreadsheets can be small and focused on a single task, making them much more maintainable. To enable Calc2Code to know how to call find_nth from within get_nth the turboexcel.ini file needs to have a line in it which tells Calc2Code where to find find_nth (it didn’t have to be in the same DLL), as wlel as the types of all the arguments. That’s why we checked “Make available for other Calc2Code projects” earlier; it placed the proper line in the ini file for us earlier.

A second thing to note is that the single DLL has two functions in it. You can group routines together in a single DLL library this way.

This handy utility can now be used anywhere in Excel to make text parsing much easier.

Click here to see the next tip..