You can do this too
Download now, it's easy
ASAP Utilities menu
Language:
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 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 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".
=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(cell)
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.
=ASAPMERGECELLS(reference, optional delimiter = "", optional skip_empty_cells = True)
Joins several text strings into one text string.
An easy alterative for the Excel =CONCATENATE() function. The benefit of this ASAP Utilities function:
- You can specify a range to join, for example "A1:G1".
- The number format 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 value as "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: Insert a formula from the ASAP Utilities function library
Starting this utility
In the Excel menu choose ASAP Utilities » Formulas » Insert function from the ASAP Utilities library...Additional keywords:
return cell's
return cell's
« Return to the list of “Formulas” utilities.
© 1999-2010 - Bastien Mensink - ASAP Utilities - Excel tools - A Must in Every Office BV - The Netherlands 