

ASAP Utilities, description of our Excel toolsEnglish (us) / Nederlands / Deutsch / Español / Français / Português do Brasil / Italiano / Русский / 中文(简体) / 日本語Formulas » Insert function from the ASAP Utilities library...With this utility you can insert a formula from the ASAP Utilities functions library into the active cell.The ASAP Utilities functions library includes the following functions: =ASAPCELLCOLORINDEX(cell)Returns the color index number of the cell.If you afterwards change the color in the cell, you have to press Ctrl+Alt+F9 to have the formula recalculated. This function does not recognize formatting if it is applied via conditional formatting. Parameters:
=ASAPCOUNTBYCELLCOLOR(reference, color_index_nr)Counts the number of cells in the given range that have a certain fill color.If you afterwards change the fill color in any of the referenced cells, you have to press Ctrl+Alt+F9 to have the formulas recalculated. This function does not recognize formatting if it is applied via conditional formatting. Parameters:
=ASAPCOUNTBYFONTCOLOR(reference, color_index_nr)Counts the number of cells in the given range that have a certain font color.If you afterwards change the font color in any of the referenced cells, you have to press Ctrl+Alt+F9 to have the formulas recalculated. This function does not recognize formatting if it is applied via conditional formatting. Parameters:
=ASAPCOUNTCHAR(within_text, find_text)Counts the number of times a character occurs in a textThis way you can for example count the number of commas in a cell. This function is case sensitive. Parameters:
=ASAPCOUNTSHADES(reference)Counts the number of colored cells in your range.If you afterwards change the fill color in any of the referenced cells, you have to press Ctrl+Alt+F9 to have the formulas recalculated. This function does not recognize formatting if it is applied via conditional formatting. Parameters:
=ASAPEXTRACTFILENAME(text, optional path_separator)Returns the file name from a full path and filename. By default the formula uses a backslash (\) as separator, but optionally you can specify another separator.For example =ASAPEXTRACTFILENAME("D:\Projects\Archive\Client 1\Balance.xls") returns "Balance.xls". Parameters:
=ASAPEXTRACTFOLDERNAME(text, optional path_separator)Returns the folder name from a combined filepath and filename. By default the formula uses a backslash (\) as separator, but optionally you can specify another separator.For example =ASAPEXTRACTFOLDERNAME("D:\Projects\Archive\Client 1\Balance.xls") returns "D:\Projects\Archive\Client 1". Parameters:
=ASAPEXTRACTNUMBERS(text)Returns the numbers from a text string.For example the formula =ASAPEXTRACTNUMBERS("8011 LB") returns 8011. Parameters:
=ASAPFILENAME()Returns the name of your workbook. This is the name of the workbook without the filepath (folder).For example "Balance.xls". =ASAPFILEPATH()Returns the filepath (the folder) where your workbook is stored.For example: "D:\Projects\Archive\Client 1". =ASAPFILEPROPERTIES(property_name_or_id)Returns the value of one of the builtin document properties for the current workbook.You can refer to document properties either by index value or by their English name. The following list shows the available builtin index values and document property names: 1 Title 2 Subject 3 Author 4 Keywords 5 Comments 6 Template 7 Last Author 8 Revision Number 9 Application Name 10 Last Print Date 11 Creation Date 12 Last Save Time 13 Total Editing Time * 14 Number of Pages * 15 Number of Words * 16 Number of Characters * 17 Security 18 Category 19 Format 20 Manager 21 Company 22 Number of Bytes * 23 Number of Lines * 24 Number of Paragraphs * 25 Number of Slides * 26 Number of Notes * 27 Number of Hidden Slides * 28 Number of Multimedia Clips * 29 Hyperlink Base 30 Number of Characters (with spaces) * * Excel isn't required to define values for every builtin document property. If Microsoft Excel doesn't define a value for one of the builtin document properties, reading the Value property for that document property results in an error. You have to press Ctrl+Alt+F9 to have the formula recalculated. Example: =ASAPFILEPROPERTIES("Last Print Date") Parameters:
=ASAPFONTCOLORINDEX(cell)Returns the color index number of the font of a cell.If you afterwards change the font color in the cell, you have to press Ctrl+Alt+F9 to have the formula recalculated. This function does not recognize formatting if it is applied via conditional formatting. Parameters:
=ASAPFULLFILENAME()Returns the full filename of your workbook. This is the name of the workbook including the folder (filepath) where it is saved.For example "D:\Projects\Archive\Client 1\Balance.xls". =ASAPGETCOMMENT(cell)Returns the text from the comment a cell.If you afterwards change the comment in the cell, you have to press Ctrl+Alt+F9 to have the formula recalculated. Parameters:
=ASAPGETDOMAIN(text, optional show_protocol = False)Returns the (sub)domain from a given hyperlink (website address/url).For example if cell A1 contains the value "http://www.asaputilities.com/downloadasaputilities.php" then these are the formula results: =ASAPGETDOMAIN(A1) returns "www.asaputilities.com" =ASAPGETDOMAIN(A1;TRUE) returns "http://www.asaputilities.com" Parameters:
=ASAPGETFONTNAME(cell)Returns the name of the font in a cell.If you afterwards change the font in the cell, you have to press Ctrl+Alt+F9 to have the formula recalculated. This function does not recognize formatting if it is applied via conditional formatting. Parameters:
=ASAPGETFONTSIZE(cell)Returns the font size of a cell.If you afterwards change the font size in the cell, you have to press Ctrl+Alt+F9 to have the formula recalculated. This function does not recognize formatting if it is applied via conditional formatting. Parameters:
=ASAPGETFORMULA(cell)Returns the formula of a cell.Parameters:
=ASAPGETFORMULAINT(cell)Returns the formula of a cell in the "international" notation.The English names for the formulas will be used, the list separator is a comma and the decimal separator is a point. The largest resources on the Internet on Excel are in English. On these websites the "international" formulas and style are used. If you use a local version of Excel you can now easily create an "international" example of the formula you used. Parameters:
=ASAPGETHYPERLINK(cell, optional text_no_link)Returns the hyperlink from a cell. The hyperlink can be one of the following types: existing file or web page  place in your document  email address If you afterwards change the hyperlink in the cell, you have to press Ctrl+Alt+F9 to have the formula recalculated. Parameters:
=ASAPGETNUMBERFORMAT(cell)Returns the number format of a cell.If you afterwards change the number format in the cell, you have to press Ctrl+Alt+F9 to have the formula recalculated. Parameters:
=ASAPISBOLD(reference)Returns TRUE if the cell is bold or FALSE if it isn't.If you afterwards change the bold setting in the cell, you have to press Ctrl+Alt+F9 to have the formula recalculated. This function does not recognize formatting if it is applied via conditional formatting. =ASAPISFORMULA(cell)Returns TRUE if the cell has a formula or FALSE if it doesn't.=ASAPLOADIMAGE(image_fullname, optional width_in_pixels, optional height_in_pixels)Inserts the specified image as an object and puts it at the lefttop of your cell.To update the image, you can replace the formula with a new image name. To remove the image you have to remove both the formula and the image. (The image isn't removed if only the formula is removed.) You have to press Ctrl+Alt+F9 to have the formula recalculated. Example: =ASAPLOADIMAGE("D:\products\images\art782.gif") Parameters:
=ASAPMERGECELLS(reference, optional delimiter = "", optional skip_empty_cells = True)Joins several text strings into one text string.An easy alternative for the Excel =CONCATENATE() function. The benefit of this ASAP Utilities function:  You can specify a range to join, for example "A1:G1".  The numberformat of the values will be used. For example if a cell has the value "12.23072" and the number format is to display only one decimal then this function uses the value "12.2".  You only have to specify a delimiter once.  By default empty cells will be ignored. Parameters:
=ASAPRANDOMPASSWORD(optional length = 8, optional use_symbols = True)Returns a random string that can be used as a password.This function will return a strong password which contains of:  both uppercase and lowercase letters  numbers  special characters, such as ~!@#$%^*()[]\/<>:=+_ Parameters:
=ASAPSHEETNAME(optional reference)Returns the name of the worksheet this formula is used on.Parameters:
=ASAPSPELLNUMBER(ByVal number, optional strLanguage = "EN", optional blnCurrency = False, optional strSingular, optional strPlural, optional strComma, optional strCentSingular, optional strCentPlural)Returns a spelledout number or amount.A few examples if cell A1 contains the value 142.23 =ASAPSPELLNUMBER(A1,"EN", TRUE, "Dollar", "Dollars", , "Cent", "Cents") returns One Hundred Forty Two Dollars and Twenty Three Cents =ASAPSPELLNUMBER(A1,"EN", FALSE,,,"Comma") returns One Hundred Forty Two Comma Twenty Three =ASAPSPELLNUMBER(A1,"NL") returns éénhonderdtweeenveertig komma drieentwintig If a number contains more than two decimals this function will spell out the number as if it was rounded to two decimals. A practical example where this function can be useful is to write out amounts on cheques. Parameters:
=ASAPSTRIPNUMBERS(text)Removes all numbers from a text string and removes all spaces at the beginning and end of the result.For example the formula =ASAPSTRIPNUMBERS("8011 LB") returns "LB". Parameters:
=ASAPSUMBYCELLCOLOR(reference, color_index_nr)Adds the cells that have a certain fill color.If you afterwards change the color in any of the referenced cells, you have to press Ctrl+Alt+F9 to have the formulas recalculated. This function does not recognize formatting if it is applied via conditional formatting. Parameters:
=ASAPSUMBYFONTCOLOR(reference, color_index_nr)Adds the cells that have a certain font color.If you afterwards change the font color in any of the referenced cells, you have to press Ctrl+Alt+F9 to have the formulas recalculated. This function does not recognize formatting if it is applied via conditional formatting. Parameters:
Practical tricks on how this can help youPractical 'real world' examples on our blog that show you how this tool can help you in Excel. Enjoy!Starting this utilityIn the Excel menu choose: ASAP Utilities » Formulas » Insert function from the ASAP Utilities library...Download example workbook that demonstrates these extra functionsWe have created a example workbook that shows how to get things done, that are normally not possible in Excel, by using the functions that ASAP Utilities adds.ExampleworkbookASAPUtilitiesformulas.xls (0.8 MB) Remarks on calculationBecause Excel only recalculates a formula if the value in a (referenced) cell changes (most of the times), some of the ASAP Utilities worksheet formulas don't get triggered to update. In that case you can use Excel's shortcuts F9 or Control+Alt+F9 to have the ASAP Utilities formula(s) updated/recalculated. This is for example when you use the =ASAPSUMBYCELLCOLOR() function and then change the colors, the formulas doesn't get automatically triggered to update.Remarks on distributingIf you use these worksheet functions in your worksheet there a few considerations to make if other people are going to work on the same workbook:
« Return to the list of “Formulas” tools.
Download ASAP Utilities
Additional keywords for this tool: return cell's color, colour, connect strings together, separators, combines Follow ASAP Utilities on Facebook  
Home
Sitemap
Search
List of all utilities
Printer friendly page
^ Back to Top
© 19992017  Bastien Mensink  ASAP Utilities  A Must in Every Office BV  The Netherlands
Connect with us and receive our tips that show you how to benefit from the tools in ASAP Utilities at:
