Whether you are an Excel power user or not you will find this addin a huge time saver and provide functionality to your spread sheet that you never thought possible. Seamlessly integrated into Microsoft Excel it provides a vast library of utilities to manipulate, edit and simplify repetitive tasks. Alan Cooke demonstrates it to us.
Do you need to merge data from a number of worksheets into a single worksheet? No problem. Perhaps you need to add or remove characters to the end or beginning of a sheet full of data. ASAP Utilities has a macro for just about any need.
Here you can see all the categories of tools available to you under the ASAP Utilities addin. Not sure which one you need or where it is in the library? ASAP Utilities has a ‘Find and run a utility’ to make the job easy.
Here I have searched for a macro that will help me remove empty spaces in data I imported. I found this tool indespensible when I was ‘massaging’ data which I had downloaded from a GPS co-ordinates website to be added to my Google Map websites.
I cannot do justice to the vast array of tools available in this addin so I will briefly describe how I used ASAP Utilities to solve some of my problems.
I have a number of websites which are map based and to populate the map I had obtained masses of data that needed to be cleaned up and massaged to conform to the necessary format for uploading into the maps datasource. For example an inverted comma hidden away in the description caused havoc when uploading the ‘csv’ file to the website. With thousands of lines of data it was an impossible task to manually do this and the ‘Find and replace function’ of Excel was not always possible or too basic for my requirements. Another issue was that the text data had variaties of those those pesky accented letters which needed to be replaced with regular characters. Not a problem for ASAP Utilities. Here you see a list of the regular ‘accented’ characters and the replacement. All that is required is to set the range and click ‘OK’. ASAP Utilities will go through the sheet or cell range and replace every occurrence of the accented character with the ‘English’ equivalent.
What if the ‘accented’ character is not in the list? No problem. Simply use the Advanced utility and you can replace the most obscure characters with your own equivalent. Even the invisible ones!
To give another example I had a sheet full of hyperlinks in this format ‘www.thisisthewebsite.com’. Problem was that the map I was uploading to required the link in full html format like this:
Here you can see what I have done to transform the basic url into the required format. The result can be seen in the ‘Example:’ window at the bottom. Once the example is how you need the link, click OK and the job is done.
Now I need to colour every second row. ASAP will do this in a few simple steps.
Here is a typical comment. Problem is the font is too big and I have a sheet full of them all needing to be changed. The Tahoma 16 font has been changed to Calibri 10 using the ‘Comment tools’ dialog box.
In this next example we have a column of data which we need to have formatted in five rows across four columns. In the box enter ‘5’ and click OK.
You have just spent half a day building a workbook of formulae and the data has been entered and now a number of cells need a formula changed. If it’s a column of formulae it’s fairly simple with the standard Excel options. Change the formula in the top most cell and use the drag feature to replace all the cells in the column. If there are a number of cells across the sheet in random areas the job is a little trickier. Using the following Utility you can modify a selection of cells to change the formula.
Option ‘2’ is what one uses to modify a formula. In my test I had a basic formula ‘=(A12*100)’ and modified it to ‘=(A12*100)*34’.
I cannot do justice to the range of Utilities this package offers. There are hundreds of them covering virtually every possible function or need within Excel. Whether it’s text manipulation to formulas, numbers, even objects and formatting. An easy to use and well designed application which comes with loads of hints, tips, examples, instructions and a full manual. What more could you ask for!!
Here are a number of links to find out more about ASAP Utilities.
Please note: For home/personal users ASAP Utilities is free.
For commercial users there is a cost; VERY reasonable.