Migration


ASAP Utilities Functions

link - https://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=259

Library FunctionDescription
ASAPCELLCOLORINDEXReturns the color index number of the cell. FORMATGET
ASAPCOUNTBYCELLCOLORCounts the number of cells in the given range that have a certain fill color. COUNTFORMAT
ASAPCOUNTBYFONTCOLORCounts the number of cells in the given range that have a certain font color. COUNTFORMAT
ASAPCOUNTCHARCounts the number of times a character occurs in a text. COUNTSUBSTRING
ASAPCOUNTSHADESCounts the number of colored cells in your range. COUNTFORMAT
ASAPEXTRACTFILENAMEReturns the file name from a full path and filename. EXTRACTFILENAME
ASAPEXTRACTFOLDERNAMEReturns the folder name from a combined filepath and filename. EXTRACTFOLDERPATH
ASAPEXTRACTNUMBERSReturns the numbers from a text string. EXTRACTNUMBERS
ASAPFILENAMEReturns the name of your workbook. WORKBOOKFILENAME
ASAPFILEPATHReturns the filepath (the folder) where your workbook is stored. WORKBOOKPATH
ASAPFILEPROPERTIESReturns the value of one of the built-in document properties for the current workbook. FILEPROPERTIES
ASAPFONTCOLORINDEXReturns the color index number of the font of a cell. FORMATGET
ASAPFULLFILENAMEReturns the full filename of your workbook. WORKBOOKPATHFILENAME
ASAPGETCOMMENTReturns the text from the comment a cell. COMMENTGET
ASAPGETDOMAINReturns the (sub)domain from a given hyperlink (website address/url). HYPERLINKGET
ASAPGETFONTNAMEReturns the name of the font in a cell. FORMATGET
ASAPGETFONTSIZEReturns the font size of a cell. FORMATGET
ASAPGETFORMULAReturns the formula of a cell. FORMULAGET
ASAPGETFORMULAINTReturns the formula of a cell in the "international" notation. FORMULAGET
ASAPGETHYPERLINKReturns the hyperlink from a cell. HYPERLINKGET
ASAPGETNUMBERFORMATReturns the number format of a cell. NUMBERFORMATGET
ASAPISBOLDReturns TRUE if the cell is bold or FALSE if it isn't. ISBOLD
ASAPISFORMULAReturns TRUE if the cell has a formula or FALSE if it doesn't. ISFORMULA
ASAPLOADIMAGEInserts the specified image as an object and puts it at the left-top of your cell. IMAGEDISPLAY
ASAPMERGECELLSJoins several text strings into one text string. MERGECELLS
ASAPRANDOMPASSWORDReturns a random string that can be used as a password. RANDOMPASSWORD
ASAPSHEETNAMEReturns the name of the worksheet this formula is used on. WORKSHEETNAME
ASAPSPELLNUMBERReturns a spelled-out number or amount. SPELLNUMBER
ASAPSTRIPNUMBERSRemoves all numbers from a text string and removes all spaces at the beginning and end of the result. REMOVENUMBERS
ASAPSUMBYCELLCOLORAdds the cells that have a certain fill color. SUMFORMAT
ASAPSUMBYFONTCOLORAdds the cells that have a certain font color. SUMFORMAT
Library Function
Description
ASAPCELLCOLORINDEX
Returns the color index number of the cell. FORMATGET
ASAPCOUNTBYCELLCOLOR
Counts the number of cells in the given range that have a certain fill color. COUNTFORMAT
ASAPCOUNTBYFONTCOLOR
Counts the number of cells in the given range that have a certain font color. COUNTFORMAT
ASAPCOUNTCHAR
Counts the number of times a character occurs in a text. COUNTSUBSTRING
ASAPCOUNTSHADES
Counts the number of colored cells in your range. COUNTFORMAT
ASAPEXTRACTFILENAME
Returns the file name from a full path and filename. EXTRACTFILENAME
ASAPEXTRACTFOLDERNAME
Returns the folder name from a combined filepath and filename. EXTRACTFOLDERPATH
ASAPEXTRACTNUMBERS
Returns the numbers from a text string. EXTRACTNUMBERS
ASAPFILENAME
Returns the name of your workbook. WORKBOOKFILENAME
ASAPFILEPATH
Returns the filepath (the folder) where your workbook is stored. WORKBOOKPATH
ASAPFILEPROPERTIES
Returns the value of one of the built-in document properties for the current workbook. FILEPROPERTIES
ASAPFONTCOLORINDEX
Returns the color index number of the font of a cell. FORMATGET
ASAPFULLFILENAME
Returns the full filename of your workbook. WORKBOOKPATHFILENAME
ASAPGETCOMMENT
Returns the text from the comment a cell. COMMENTGET
ASAPGETDOMAIN
Returns the (sub)domain from a given hyperlink (website address/url). HYPERLINKGET
ASAPGETFONTNAME
Returns the name of the font in a cell. FORMATGET
ASAPGETFONTSIZE
Returns the font size of a cell. FORMATGET
ASAPGETFORMULA
Returns the formula of a cell. FORMULAGET
ASAPGETFORMULAINT
Returns the formula of a cell in the "international" notation. FORMULAGET
ASAPGETHYPERLINK
Returns the hyperlink from a cell. HYPERLINKGET
ASAPGETNUMBERFORMAT
Returns the number format of a cell. NUMBERFORMATGET
ASAPISBOLD
Returns TRUE if the cell is bold or FALSE if it isn't. ISBOLD
ASAPISFORMULA
Returns TRUE if the cell has a formula or FALSE if it doesn't. ISFORMULA
ASAPLOADIMAGE
Inserts the specified image as an object and puts it at the left-top of your cell. IMAGEDISPLAY
ASAPMERGECELLS
Joins several text strings into one text string. MERGECELLS
ASAPRANDOMPASSWORD
Returns a random string that can be used as a password. RANDOMPASSWORD
ASAPSHEETNAME
Returns the name of the worksheet this formula is used on. WORKSHEETNAME
ASAPSPELLNUMBER
Returns a spelled-out number or amount. SPELLNUMBER
ASAPSTRIPNUMBERS
Removes all numbers from a text string and removes all spaces at the beginning and end of the result. REMOVENUMBERS
ASAPSUMBYCELLCOLOR
Adds the cells that have a certain fill color. SUMFORMAT
ASAPSUMBYFONTCOLOR
Adds the cells that have a certain font color. SUMFORMAT

Power Utility Pack Functions

Library FunctionDescription
CellColorFORMATGET
CellHasFormulaISFORMULA
CellTypeCELLTYPE
Char2Excel 2013 added UNICODE
Code2Excel 2013 added UNICHAR
ContainsCONTAINS
CountAVisibleCOUNTAVISIBLE
CountBetweenCounts the number of values in a range that fall between two values. COUNTBETWEEN
CreditCard 
DaysInMonthReturns the number of days in a month. COUNTNUMBEROFDAYS
DollorTextSPELLNUMBER
ExcelDirEXCELDIR
ExtractElementReturns the nth element of a string that uses a separator character. WORDGET
FileExistsFILEEXISTS
FileNameWORKBOOKFILENAME
FontColorFORMATGET
HInterpolateHLOOKUPINTERPOLATE
InsertStringINSERTSTRING
IsLikeISLIKE
LastInColumnLASTINCOLUMN
LastInRowLASTINROW
MaxAllSheetsALLSHEETS_MAX
MinAllSheetsALLSHEETS_MIN
MonthWeekWEEKNUMBER
NumberFormatNUMBERFORMATGET
ParseName 
RemoveSpacesREMOVESPACES
SayIt 
ScrambleSCRAMBLE
SelectOneSELECTONE
SheetCountWORKSHEETSCOUNT
SheetNameWORKSHEETNAME
SheetOffsetSHEETOFFSET
StaticRandRANDOMNUMBER
SumAllSheetsALLSHEETS
SumVisibleSUMVISIBLE
TimeXX 
TimeXX_Add 
TimeXX_Subtract 
TimeXX_Sum 
TopAverageAVERAGETOP
UniqueItemsUNIQUEITEMS
UserUSERNAME
VInterpolateVLOOKUPINTERPOLATE
WhichDayWHICHDAY
XDateDATESBEFORE1900
XDate_AddDATESBEFORE1900
XDate_DayDATESBEFORE1900
XDate_DifDATESBEFORE1900
XDate_DowDATESBEFORE1900
XDATE_MonthDATESBEFORE1900
XDate_YearDATESBEFORE1900
XDate_YearDifDATESBEFORE1900
Library Function
Description
CellColor
FORMATGET
CellHasFormula
ISFORMULA
CellType
CELLTYPE
Char2
Excel 2013 added UNICODE
Code2
Excel 2013 added UNICHAR
Contains
CONTAINS
CountAVisible
COUNTAVISIBLE
CountBetween
Counts the number of values in a range that fall between two values. COUNTBETWEEN
CreditCard
DaysInMonth
Returns the number of days in a month. COUNTNUMBEROFDAYS
DollorText
SPELLNUMBER
ExcelDir
EXCELDIR
ExtractElement
Returns the nth element of a string that uses a separator character. WORDGET
FileExists
FILEEXISTS
FileName
WORKBOOKFILENAME
FontColor
FORMATGET
HInterpolate
HLOOKUPINTERPOLATE
InsertString
INSERTSTRING
IsLike
ISLIKE
LastInColumn
LASTINCOLUMN
LastInRow
LASTINROW
MaxAllSheets
ALLSHEETS_MAX
MinAllSheets
ALLSHEETS_MIN
MonthWeek
WEEKNUMBER
NumberFormat
NUMBERFORMATGET
ParseName
RemoveSpaces
REMOVESPACES
SayIt
Scramble
SCRAMBLE
SelectOne
SELECTONE
SheetCount
WORKSHEETSCOUNT
SheetName
WORKSHEETNAME
SheetOffset
SHEETOFFSET
StaticRand
RANDOMNUMBER
SumAllSheets
ALLSHEETS
SumVisible
SUMVISIBLE
TimeXX
TimeXX_Add
TimeXX_Subtract
TimeXX_Sum
TopAverage
AVERAGETOP
UniqueItems
UNIQUEITEMS
User
USERNAME
VInterpolate
VLOOKUPINTERPOLATE
WhichDay
WHICHDAY
XDate
DATESBEFORE1900
XDate_Add
DATESBEFORE1900
XDate_Day
DATESBEFORE1900
XDate_Dif
DATESBEFORE1900
XDate_Dow
DATESBEFORE1900
XDATE_Month
DATESBEFORE1900
XDate_Year
DATESBEFORE1900
XDate_YearDif
DATESBEFORE1900

OzGrid Functions

Library FunctionDescription
AutoFilter_Criteria 
CalculationMode 
CalculationState 
CellColorReturn the cells fill color as either an Index number (default) or it's color name as text. FORMATGET
ColorRankFORMATGET
ConvertCurrencyToEnglish 
Count_Once 
CountWordsWORDSCOUNT
FindNth 
Five_Con_VLookup 
Four_Con_VLookup 
Get_WordWORDNO
GetAddressHYPERLINKGET
GetCommentTextCOMMENTGET
GetFilenameReturns the file name of the active workbook. WORKBOOKFILENAME
GetMaxBetweenReturns the largest value that is between a range. MAXBETWEEN
HowManyDaysInMonth 
IsFormulaISFORMULA
LastDayOfMonthLASTDATE_INTHISMONTH
Lookup_Occurrence 
MyFullNameReturns the folder path and file name of the active workbook. WORKBOOKPATHFILENAME
MyNameReturns the application's user name or the domain username. USERNAME
NthDayOfMonth 
OzGridLookup 
RandLottoRANDOMNUMBER
ReturnLastWordWORDNO-LAST
ReverseCellReturns the contents of a particular cell with all the characters reversed. REVERSE
SheetNameReturns the name of the worksheet this formula is used on. WORKSHEETNAME
SpellNumberReturns the word equivalent for a numerical number. NUMBERSTOWORD
SumEveryNth 
Three_Con_VLookup 
Two_Con_VLookup 
VLookAllSheetsVLOOKUPALL
WorkbookExistsReturns whether a particular file exists. FILEEXISTS
Library Function
Description
AutoFilter_Criteria
CalculationMode
CalculationState
CellColor
Return the cells fill color as either an Index number (default) or it's color name as text. FORMATGET
ColorRank
FORMATGET
ConvertCurrencyToEnglish
Count_Once
CountWords
WORDSCOUNT
FindNth
Five_Con_VLookup
Four_Con_VLookup
Get_Word
WORDNO
GetAddress
HYPERLINKGET
GetCommentText
COMMENTGET
GetFilename
Returns the file name of the active workbook. WORKBOOKFILENAME
GetMaxBetween
Returns the largest value that is between a range. MAXBETWEEN
HowManyDaysInMonth
IsFormula
ISFORMULA
LastDayOfMonth
LASTDATE_INTHISMONTH
Lookup_Occurrence
MyFullName
Returns the folder path and file name of the active workbook. WORKBOOKPATHFILENAME
MyName
Returns the application's user name or the domain username. USERNAME
NthDayOfMonth
OzGridLookup
RandLotto
RANDOMNUMBER
ReturnLastWord
WORDNO-LAST
ReverseCell
Returns the contents of a particular cell with all the characters reversed. REVERSE
SheetName
Returns the name of the worksheet this formula is used on. WORKSHEETNAME
SpellNumber
Returns the word equivalent for a numerical number. NUMBERSTOWORD
SumEveryNth
Three_Con_VLookup
Two_Con_VLookup
VLookAllSheets
VLOOKUPALL
WorkbookExists
Returns whether a particular file exists. FILEEXISTS

MoreFunc Functions

Library FunctionDescription
ANSI128Strips the accents from a string
ARRAY.FILTERReturns only the visible cells of a range (in an array)
ARRAY.JOINJoins various items (ranges, unions, strings, numbers...) in a single array
CHBASEConverts a value from a base into another base
COUNTDIFFNumber of unique values in a range or array
COUNTIF.3DSame as COUNTIF working with 3D references
DATE.DIFFReturns an age in years, months and days
EASTERDATEDate of Easter Sunday for a given year
EVALEvaluates a formula or expression
FILENAMEName of the workbook
FORMULATEXTReturns the formula of a cell
GEOMEAN.EXTSame as GEOMEAN, higher capacity
GETVReturns a temporary variable value stored by SETV
HEX.ANDBitwise AND between hexadecimal numbers
HEX.FORMATFormats and checks the validity of an hexadecimal number
HEX.NEGInverts the sign of an hexadecimal number (2's complement)
HEX.NOTNegates (NOT) an hexadecimal number
HEX.ORBitwise OR between hexadecimal numbers
HEX.SUMAdds hexadecimal numbers
HSORTSorts a range or an array (supports up to 14 sort keys) - vertical
HSORT.IDXSort index of a range or array (supports up to 14 sort keys) - vertical
INDIRECT.EXTsame as INDIRECT working also with closed workbooks
INTVECTORReturns a vector of consecutive integers in the ascending order
ISO.WEEKNUMISO-compliant week number of a day. Excel 2013 added ISOWEEKNUM
LASTROWLast filled cell in a column. LASTINROW
MATRIXROUNDAdjusts an array of rounded numbers (=>sum of percentages = always 100%)
MCONCATConcatenates all values in a range or an array
MDETERM.EXTSame as MDETERM, faster and supporting larger arrays
MINVERSE.EXTSame as MINVERSE, faster and supporting larger arrays
MMAXReturns the N highest numbers of a range or an array
MMINReturns the N lowest numbers of a range or an array
MMULT.EXTSame as MMULT, faster and supporting larger arrays
MRANDSeries of random integers without repetitions
NBTEXTConverts a positive number into spelled-out text (supports 13 languages)
PAGENUMPage number of a cell
PN.ISPRIMEReturns TRUE if a number is a prime number (up to 255 digits)
PN.NEXTReturns the next prime number (up to 255 digits)
RECALLReturns the previous value of the calling cell before the last calculation
REGEX.COMPReturns TRUE if a text matches a regular expression
REGEX.COUNTCounts the matches of a regular expression in a string
REGEX.FINDLocation of a substring matching a regular expression
REGEX.LENCounts the matches of a regular expression in a string
REGEX.MIDReturns a substring matching a regular expression
REGEX.SUBSTITUTEReplaces a substring matching a regular expression with a new string
SETVStores a value in a temporary variable
SHEETNAMEName of a sheet in the workbook. WORKSHEETNAME
SHEETOFFSETReturns a range that is a specified number of sheets from a range
STDEV.GROUPEDStandard deviation based on a sample (data grouped in classes)
STDEVP.GROUPEDStandard deviation based on the entire population (grouped data)
TEXTREVERSEReverses the characters of a string. REVERSE
THREEDCoerces a 3D range into a single array - enables array formulae with 3D references
UNION.OFFSETOFFSET-like function working with unions (discontinguous ranges)
UNIQUEVALUESReturns the unique items of a range or an array. UNIQUEITEMS
VAR.GROUPEDVariance based on a sample (data grouped in classes)
VARP.GROUPEDVariance based on the entire population (grouped data)
VSORTSorts a range or an array (supports up to 14 sort keys) - horizontal
VSORT.IDXSort index of a range or array (supports up to 14 sort keys) - horizontal
WEIGHTED.AVERAGEReturns a weighted average
WMIDExtracts a word or a group of words from a text
WORDCOUNTNumber of words in a text
XLM.GET.CELLInformations about the formatting, location or contents of a cell or a range
XLM.GET.DOCUMENTInformations about the workbook (number of sheets, author, summary...)
XLM.GET.WORKBOOKInformations about the current sheet or the workbook (protection, type of sheet...)
XLM.GET.WORKSPACEInformations about the workspace (country version of Excel, startup path...)
Library Function
Description
ANSI128
Strips the accents from a string
ARRAY.FILTER
Returns only the visible cells of a range (in an array)
ARRAY.JOIN
Joins various items (ranges, unions, strings, numbers...) in a single array
CHBASE
Converts a value from a base into another base
COUNTDIFF
Number of unique values in a range or array
COUNTIF.3D
Same as COUNTIF working with 3D references
DATE.DIFF
Returns an age in years, months and days
EASTERDATE
Date of Easter Sunday for a given year
EVAL
Evaluates a formula or expression
FILENAME
Name of the workbook
FORMULATEXT
Returns the formula of a cell
GEOMEAN.EXT
Same as GEOMEAN, higher capacity
GETV
Returns a temporary variable value stored by SETV
HEX.AND
Bitwise AND between hexadecimal numbers
HEX.FORMAT
Formats and checks the validity of an hexadecimal number
HEX.NEG
Inverts the sign of an hexadecimal number (2's complement)
HEX.NOT
Negates (NOT) an hexadecimal number
HEX.OR
Bitwise OR between hexadecimal numbers
HEX.SUM
Adds hexadecimal numbers
HSORT
Sorts a range or an array (supports up to 14 sort keys) - vertical
HSORT.IDX
Sort index of a range or array (supports up to 14 sort keys) - vertical
INDIRECT.EXT
same as INDIRECT working also with closed workbooks
INTVECTOR
Returns a vector of consecutive integers in the ascending order
ISO.WEEKNUM
ISO-compliant week number of a day. Excel 2013 added ISOWEEKNUM
LASTROW
Last filled cell in a column. LASTINROW
MATRIXROUND
Adjusts an array of rounded numbers (=>sum of percentages = always 100%)
MCONCAT
Concatenates all values in a range or an array
MDETERM.EXT
Same as MDETERM, faster and supporting larger arrays
MINVERSE.EXT
Same as MINVERSE, faster and supporting larger arrays
MMAX
Returns the N highest numbers of a range or an array
MMIN
Returns the N lowest numbers of a range or an array
MMULT.EXT
Same as MMULT, faster and supporting larger arrays
MRAND
Series of random integers without repetitions
NBTEXT
Converts a positive number into spelled-out text (supports 13 languages)
PAGENUM
Page number of a cell
PN.ISPRIME
Returns TRUE if a number is a prime number (up to 255 digits)
PN.NEXT
Returns the next prime number (up to 255 digits)
RECALL
Returns the previous value of the calling cell before the last calculation
REGEX.COMP
Returns TRUE if a text matches a regular expression
REGEX.COUNT
Counts the matches of a regular expression in a string
REGEX.FIND
Location of a substring matching a regular expression
REGEX.LEN
Counts the matches of a regular expression in a string
REGEX.MID
Returns a substring matching a regular expression
REGEX.SUBSTITUTE
Replaces a substring matching a regular expression with a new string
SETV
Stores a value in a temporary variable
SHEETNAME
Name of a sheet in the workbook. WORKSHEETNAME
SHEETOFFSET
Returns a range that is a specified number of sheets from a range
STDEV.GROUPED
Standard deviation based on a sample (data grouped in classes)
STDEVP.GROUPED
Standard deviation based on the entire population (grouped data)
TEXTREVERSE
Reverses the characters of a string. REVERSE
THREED
Coerces a 3D range into a single array - enables array formulae with 3D references
UNION.OFFSET
OFFSET-like function working with unions (discontinguous ranges)
UNIQUEVALUES
Returns the unique items of a range or an array. UNIQUEITEMS
VAR.GROUPED
Variance based on a sample (data grouped in classes)
VARP.GROUPED
Variance based on the entire population (grouped data)
VSORT
Sorts a range or an array (supports up to 14 sort keys) - horizontal
VSORT.IDX
Sort index of a range or array (supports up to 14 sort keys) - horizontal
WEIGHTED.AVERAGE
Returns a weighted average
WMID
Extracts a word or a group of words from a text
WORDCOUNT
Number of words in a text
XLM.GET.CELL
Informations about the formatting, location or contents of a cell or a range
XLM.GET.DOCUMENT
Informations about the workbook (number of sheets, author, summary...)
XLM.GET.WORKBOOK
Informations about the current sheet or the workbook (protection, type of sheet...)
XLM.GET.WORKSPACE
Informations about the workspace (country version of Excel, startup path...)


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext