logo ASAP Utilities
HomeFeaturesFormulasInsert function from the ASAP Utilities library...

You can do this too

Download “The one essential add-in for all Excel users.'”
Sit down and relax, while ASAP Utilities does the rest!

“Working in Excel will be fun again with all these extra supersmart tools”

ASAP Utilities, description of our Excel tools

Language: English Nederlands (Dutch) Deutsch (German) Español (Spanish) Français (French)

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 Control+Alt+F9 to have the formula recalculated.
This function does not recognize formatting if it is applied via conditional formatting.

Parameters:
  • cell = The cell to get Excel's number for the fill color from.


=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 Control+Alt+F9 to have the formulas recalculated.
This function does not recognize formatting if it is applied via conditional formatting.

Parameters:
  • reference = The range of cells to search in.
  • color_index_nr = The cell that has the fill color to count, or the color index number (1-56) from Excel.


=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 Control+Alt+F9 to have the formulas recalculated.
This function does not recognize formatting if it is applied via conditional formatting.

Parameters:
  • reference = The range of cells to search in.
  • color_index_nr = The cell that has the font color to count, or the color index number (1-56) from Excel.


=ASAPCOUNTCHAR(within_text, find_text)

Counts the number of times a character occurs in a text
This way you can for example count the number of commas in a cell. This function is case sensitive.

Parameters:
  • within_text = The text containing the character you want to count.
  • find_text = The character to count the occurences of. This has to be a single character.


=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 Control+Alt+F9 to have the formulas recalculated.
This function does not recognize formatting if it is applied via conditional formatting.

Parameters:
  • reference = The range of cells to count the number of cells that have a fill color.


=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:
  • text = The value or cell address from which you want to extract only the file name
  • path_separator = The path separator. Optional, if omitted a backslash (\) is used.


=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:
  • text = The value or cell address from which you want to extract only the folder name
  • path_separator = The path separator. Optional, if omitted a backslash (\) is used.


=ASAPEXTRACTNUMBERS(text)

Returns the numbers from a text string.
For example the formula =ASAPEXTRACTNUMBERS("8011 LB") returns 8011.

Parameters:
  • text = The value or cell address from which you want to extract the numbers from.


=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 built-in 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 built-in 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 built-in document property.
If Microsoft Excel doesn't define a value for one of the built-in document properties, reading the Value property for that document property results in an error.
You have to press Control+Alt+F9 to have the formula recalculated.

Example:
=ASAPFILEPROPERTIES("Last Print Date")  

Parameters:
  • property_name_or_id = The id or English name of the built-in document property


=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 Control+Alt+F9 to have the formula recalculated.
This function does not recognize formatting if it is applied via conditional formatting.

Parameters:
  • cell = The cell to get Excel's number for the text color from.


=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 Control+Alt+F9 to have the formula recalculated.

Parameters:
  • cell = The cell to get the text from the comment from.


=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.asap-utilities.com/download-asap-utilities.php" then these are the formula results:
=ASAPGETDOMAIN(A1) returns "www.asap-utilities.com"
=ASAPGETDOMAIN(A1;TRUE) returns "http://www.asap-utilities.com"

Parameters:
  • text = The value or cell address from which you want to extract the domain.
  • show_protocol = Optional, logical value, if omitted the default is FALSE. If TRUE this function will also return the protocol of the link, which is the part before the domain such as http://, ftp:// etc..


=ASAPGETFONTNAME(cell)

Returns the name of the font in a cell.
If you afterwards change the font in the cell, you have to press Control+Alt+F9 to have the formula recalculated.
This function does not recognize formatting if it is applied via conditional formatting.

Parameters:
  • cell = The cell to get the font from.


=ASAPGETFONTSIZE(cell)

Returns the font size of a cell.
If you afterwards change the font size in the cell, you have to press Control+Alt+F9 to have the formula recalculated.
This function does not recognize formatting if it is applied via conditional formatting.

Parameters:
  • cell = The cell to get the font size from.


=ASAPGETFORMULA(cell)

Returns the formula of a cell.

Parameters:
  • cell = The cell to get the formula from.


=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:
  • cell = The cell to get the formula from.


=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
  • e-mail address
    If you afterwards change the hyperlink in the cell, you have to press Control+Alt+F9 to have the formula recalculated.

    Parameters:
    • cell = The cell to read the hyperlink from.
    • text_no_link = Optional, this text will be displayed if the cell doesn't have a hyperlinks. If omitted, the formula will give an empty result for cells without hyperlinks.


    =ASAPGETNUMBERFORMAT(cell)

    Returns the number format of a cell.
    If you afterwards change the number format in the cell, you have to press Control+Alt+F9 to have the formula recalculated.

    Parameters:
    • cell = The cell to get the number format from.


    =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 Control+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 left-top 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 Control+Alt+F9 to have the formula recalculated.

    Example:
    =ASAPLOADIMAGE("D:\products\images\art782.gif")

    Parameters:
    • image_fullname = The full path and file name of an image of the type that Excel supports
    • width_in_pixels = Optional. You can specify the width pixels. If omitted, the width will be proportionally based on the image's height
    • height_in_pixels = Optional. You can specify the height in pixels. If omitted, the height will be the height of the cell the formula is in.


    =ASAPMERGECELLS(reference, optional delimiter = "", optional skip_empty_cells = True)

    Joins several text strings into one text string.
    An easy alternative for the Excel =CONCATENATE() fuction. 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:
    • reference = A contiguous range of cells to join the values from. When reading the cell values, their number format will be used.
    • delimiter = Optional, a character to insert between the cell values. If omitted no delimiter is used.
    • skip_empty_cells = Optional, is a logical value: to skip empty cells = TRUE or omitted; to include empty cells in the result = FALSE.


    =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:
    • length = Optional, the length of the password. If omitted the length will be 8 characters. If the length given is less then 8, still a password of 8 characters will be returned.
    • use_symbols = Optional, is a logical value: to use special characters in the password = TRUE or omitted; to create a password without special characters = FALSE.


    =ASAPSHEETNAME()

    Returns the name of the worksheet this formula is used on.


    =ASAPSPELLNUMBER(ByVal number, optional strLanguage = "EN", optional blnCurrency = False, optional strSingular, optional strPlural, optional strComma, optional strCentSingular, optional strCentPlural)

    Returns a spelled-out 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:
    • number = The number or cell with a number you want to spell.
    • language = Optional, text string representing in which language the number is spelled out: English = EN or omitted, Dutch = NL, German = DE.
    • currency = Optional, logical value: to spell the number as a currency = TRUE; to spell the number just as a number = FALSE or omitted. For example spell the number 2 as "two dollars and no cents" or just "two".
    • cur_singular = An optional text string for the currency spelled singular. For example "dollar".
    • cur_plural = An optional text string for the currency spelled plural. For example "dollars".
    • comma = An optional text string for the decimal separator used. For example the comma sign (",") or point (".") or the word "comma". If omitted then it will be automatically filled depending on the given language: English = "comma", Dutch and German = "komma".
    • cur_cent_singular = An optional text string for the word used with currency for the amount behind the comma, singular. For example "cent".
    • cur_cent_plural = An optional text string for the word used with currency for the amount behind the comma, plural. For example "cents".


    =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:
    • text = The value or cell address from which you want to strip the numbers.


    =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 Control+Alt+F9 to have the formulas recalculated.
    This function does not recognize formatting if it is applied via conditional formatting.

    Parameters:
    • reference = The range of cells to search in.
    • color_index_nr = The cell that has the fill color to sum, or the color index number (1-56) from Excel.


    =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 Control+Alt+F9 to have the formulas recalculated.
    This function does not recognize formatting if it is applied via conditional formatting.

    Parameters:
    • reference = The range of cells to search in.
    • color_index_nr = The cell that has the font color to sum, or the color index number (1-56) from Excel.

    Screenshots

    Example screenshot: 1
    1, Formulas » Insert function from the ASAP Utilities library...
    Example screenshot: 2 Calculate the sum of cells that have a specific color
    2 Calculate the sum of cells that have a specific color
    Example screenshot: 3 Retrieve the comments
    3 Retrieve the comments

    Starting this utility

    In the Excel menu choose: ASAP Utilities » Formulas » Insert function from the ASAP Utilities library...
    back to top

    Download example workbook that demonstrates these extra functions

    We 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.
    Example-workbook-ASAP-Utilities-formulas.xls (0.8 MB)

    Worksheet functions added by ASAP Utilities

    Remarks on calculation

    Since 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 have to press F9 or Control+Alt+F9 to have the ASAP Utilities formula(s) updated.
    back to top

    Remarks on distributing

    If 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:
    1. Everybody that works with the file using the ASAP Utilities worksheet functions should have ASAP Utilities installed as default add-in.
    2. If somebody is going to work with your file but he/she doesn't have ASAP Utilities, then you should convert the formula-results to their values (Copy » Paste Special » Values) or in ASAP Utilities:
      ASAP Utilities » Formulas » Change formulas to their calculated values
    If you see the result of a function displayed as "#NAME?" this means that ASAP Utilities is not available or installed in another folder. (In case you do have ASAP Utilities installed it can also mean that there is a typing-error in the formula name).

    « Return to the list of “Formulas” tools.
    « Previous
    Insert "international" formula...
         		 Next »
    Correct the link to the ASAP Utilities worksheet functions
    Excel 2010 with ASAP Utilities in the menu
    Download ASAP Utitilities
    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
    © 1999-2013 - - - - The Netherlands EnglishDutch - NederlandsGerman - DeutschSpanish - EspañolFrançais (French)

    Connect with us and receive our "tip of the week" at:
    Follow us on TwitterFollow us on Facebook