excel add-in Archives - Page 2 of 3

21Sep 2015 by Charlie Nichols No Comments

Regular Expressions in Excel – Test Tool

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Regular expressions in Excel Regular expressions in Excel are made easy with the pwrREGEXMATCH function. As a demonstration of the function, the example file pictured below is set up to evaluate a regular expression entered in cell D2 against text entered in cell D4. The result is contained in cell D8. The formula used in Excel (cell D8) is the following: =pwrREGEXMATCH(D4, D2) That's it. Download the example worksheet (link below) or just create one on your own. Using this, you can easily try out different regular expressions against some target text to massage into the perfect regex for your need. [caption id="attachment_3430" align="aligncenter" width="908"] Sample Excel template demonstrating use of regular expressions in Excel thru the pwrREGEXMATCH add-in function[/caption] Download the sample workbook here.

03Feb 2015 by Charlie Nichols No Comments

How to compare two strings in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] How to Compare Two Strings in Excel for Similarity Excel comes loaded with several useful text or string comparison functions. Out of the box, Excel provides a function named EXACT that will return whether two strings are exact matches. That's great, unless you actually need to find strings that are really close or very similar. The good news is when you want to compare two strings in Excel you aren't limited to exact matches only. Using a function such as pwrSIMILARITY you can compare two strings and get a rating back (a percentage) that indicates the degree of similarity between the two strings. You actually have a great deal more flexibility with pwrSIMILARITY. For example, the EXACT function is case sensitive so comparing "Abc" and "abc" would return…

22Jan 2015 by Charlie Nichols No Comments

How to Generate Random Text in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] A Little About Lorem Ipsum "Lorem Ipsum" text is commonly used in the printing or design industry as dummy text used as a placeholder for "real" text. The Lorem Ipsum text is originally from Latin literature that is over 2,000 years old. It is not Latin-looking gibberish as often believed. Generate Random Text in Excel You can use the pwrRANDOMTEXT function in Excel to generate random text. The random text generated will be based on the Lorem Ipsum text commonly used as filler. This function is available as part of the Random Data Assistant contained in the Excel PowerUps Premium Suite add-in for Excel. In order to use this function you simply use it in your worksheet as you would any other function. Examples If you want to…

21Oct 2014 by Charlie Nichols No Comments

Return Random Value from List in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Two Functions to Make Returning Random Data Easy Using the pwrRANDFROMSET and pwrRANDFROMRANGE functions it's easy to get a random value back to your formula. Random Values from a Set The pwrRANDFROMSET function returns a value from a set defined within the function while the pwrRANDFROMRANGE function returns a value from a defined range in Excel. If you wanted to return a random value from a set of colors you can use the equation below as an example. =pwrRANDFROMSET("red", "pink", "green", "blue", "black", "orange", "yellow") This will return a random value from the set of colors in the formula. Random Values from a Range The pwrRANDFROMRANGE function returns a value from a range of cells. This range can be multiple columns and rows as long as they are…

24Sep 2014 by Charlie Nichols No Comments

How to Collect Excel User Input and Return to Formulas

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Collecting Excel User Input Using the Excel PowerUps Premium Suite you can easily collect basic user input and return that input to your formula. You can use the function called pwrINPUTBOX to prompt the user to provide some information. Since this is a function made available to you in Excel, you don't need to write any code. Example Here's an example. Imagine you need to have the user provide some information if a value is beyond a given threshold. Let's say if a key performance metric falls below a specific level you want to capture a brief note regarding why from the user. So let's say we want to capture an explanation if the KPI falls below 85%, and that the KPI is in cell A1. The formula…

24Sep 2014 by Charlie Nichols 2 Comments

How to Show an Excel Message Box or Alert without Code

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Show an Excel Message Box Occasionally, you need to alert the user of a specific condition or value in your Excel workbook. For example, if a value gets higher or lower than a desired level you may wish to make that more apparent to the user. You can use the Conditional Formatting features in Excel to, say, make the field bright red. However, you would be relying on the user to view that tab or potentially scroll to that part of the worksheet to actually see the value in the first place. Alternatively, you can pop up a message box to alert the user of the condition or value. One method is to use the embedded scripting system within Excel and write some custom code to pop up…

24May 2014 by Charlie Nichols No Comments

Work Beyond Excel’s 32k Per-Cell Character Limit

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] The Character Limit in Excel can be Limiting - Don't let it be There is a character limit in Excel. Excel has a character limit (per cell) of 32,767 characters. And in most cases this is ample space for your needs. Sometimes You Need to Work with XML Blocks Larger Than 32k. You may have a need that resembles one of the following scenarios. You need to consume a web service that returns more than the 32k limit. You need to open a local XML file, and that file may also be larger than the 32k character limit. Meet New and Improved WEBSERVICE and FILTERXML Functions Each of the functions, including their “power” equivalent functions of pwrWEBSERVICE and pwrFILTERXML, has been enhanced in the latest version of the Excel…

24Mar 2014 by Charlie Nichols No Comments

Excel add-in update: version 1.6.3 of Excel PowerUps is Available – Get the update today

100 level, Excel

A new version of the Excel add-in Excel PowerUps Premium Suite has been released. Like other releases, this Excel add-in version has a few new and enhanced functions along with some bug fixes and tool additions. Go to the download page. New Functions SKEW.P SKEW.P was added to match the Excel 2013 function of the same name. SHEET SHEET was added to match the Excel 2013 function of the same name. SHEETS SHEETS was added to match the Excel 2013 function of the same name. pwrISREGEXMATCH pwrISREGEXMATCH will identify a regular expression match within a string. pwrREGEXREPLACE pwrREGEXREPLACE will replace text identified by a regular expression with text provided in the function call. Enhanced Functions pwrISBROKENURL pwrISBROKENURL added identification of bad URLs by Title or URL pattern. Now you can…

23Mar 2014 by Charlie Nichols No Comments

Add Excel 2013 Functions to Excel 2010

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Get the Latest Excel Functions Working in Older Versions of Excel Do you have a number of Excel 2010, Excel 2007, or Excel 2003 licenses that you want or need to get another year or more of use from? Do you need to hold off on upgrading all of your licenses to Excel 2013, at least for now? But are you in need of some of the new functions that were added in Excel 2013? Perhaps you have a worksheet that was created in Excel 2013 that makes use of one or more of the new Excel functions and you need to be able to open and work with the same workbook on PCs with Excel 2010, 2007, or 2003. You can. Excel Forward Compatibility Adapter You can…

17Dec 2013 by Charlie Nichols 1 Comment

Excel WEBSERVICE Function, plus ENCODEURL and FILTERXML Make a Useful Trio of Web Functions

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Now, Add This New Excel 2013 Trio of Functions to Your Version of Excel Excel 2013 introduced a trio of new functions that make it easy to extend your worksheets to use XML web services. The Excel WEBSERVICE function, ENCODEURL function, and FILTERXML function work nicely together to make the task of working with web services straightforward within Excel 2013. This functionality is made available in Excel 2010, Excel 2007, and Excel 2003 in the Excel PowerUps Premium Suite add-in for Excel. This can be downloaded for free. There are many posts and videos posted on YouTube illustrating how you may use the features and seeing how many there are give you a sense for how popular of an addition these functions are to Excel. The sections below…