logo ASAP Utilities

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

English (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:
  • 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 Ctrl+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 Ctrl+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 occurrences 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 Ctrl+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 Ctrl+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 Ctrl+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 Ctrl+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 Ctrl+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 Ctrl+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 Ctrl+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 Ctrl+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 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 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 Ctrl+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() 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:
  • 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 than 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(optional reference)

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

Parameters:
  • reference = Optional, a cell on the sheet that you want to get the name from. If omitted the name of the current sheet is returned.


=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 Ctrl+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 Ctrl+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
ASAP Utilities makes YOU rock in Excel!

Practical tricks on how this can help you

Practical 'real world' examples on our blog that show you how this tool can help you in Excel. Enjoy!

Starting this utility

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

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

Because 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 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:
  • Everyone that works with the workbook that uses on or more of the ASAP Utilities functions in its formulas, should have ASAP Utilities installed. You can easily recognized the functions from ASAP Utilities because their names start with "ASAP". In doubt, you can easily create a report that lists all formulas used in your workbook.
  • 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 (which is easy to fix). (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 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
© 1999-2017 - - - - The Netherlands

Connect with us and receive our tips that show you how to benefit from the tools in ASAP Utilities at:
Follow us on TwitterFollow us on Facebook