ContentsArray UDF are marked with *. Some can act both as array and non-array, marked with **. Some can be used within standard functions such as SUM and thus convert to single cell output functions.
- CONCATENATEX concatenates all values into a string with a specified separator in between (easier to implement than CONCATENATE).
- GETWORD gets the nth "word" from a string, from beginning or end.
- GETWORDS * splits a string into all its "words" and gets all words or n words, starting from the mth word, counting m from start or end. GETWORDS can do the same as GETWORD, and more. GETWORDS (retrieving words from a sentences) is 'comparable' to standard function MID (retrieving characters from a word), but works on a 'higher level'.
- FINDINCELLS ** returns the (single or row/column) index or address of the first cell that contains the string to find. This function searches within cell contents of the 1 or 2-dimensional search range, unlike XLOOKUP or MATCH. You can set its search directions very precisely. By the way, using INDEX you can convert this array formula into a regular formula.
- DECIMALSEPARATOR returns the decimal separator in use, so you can make TEXT functions separator independent (prevent them to fail).
- GETCOLOR ** returns the single color code of a cell, or Red, Green, Blue values.
- INFILTER returns the multiple values used in a filter of pivot table.
- UNIQUEVALUES * returns the unique values in a range as a concatenated string or as separate values (array), unsorted or sorted. You can use standard Excel function UNIQUE (Office365/Excel 2021) for single column or row input only.
- SORTRANGE * sorts a single row or single column input (QuickSort) in ascending or descending order. You can also use standard Excel function SMALL/LARGE to sort numbers, or SORT (Office365/Excel 2021). SORTRANGE combined with RANGETOLIST and LISTORANGE enable you to sort all values of a 2-dimensional range as well.
- LEVENSHTEIN calculates the number of insertions, deletions, and substitutions needed to transform the first string into the second string. Good example of a very specific function that is pretty difficult and cumbersome (not entirely impossible) to realize with Excel's standard functionality.
Next 5 UDFs are merely relevant for handling hidden columns. If your case is about hidden rows only, then use standard function SUBTOTAL.
- CELLVISIBLE returns TRUE if cell is visible, else FALSE. Multiplying by 1 converts TRUE/FALSE into 1/0.
- SUMVISIBLE sums visible cell values.
- COUNTVISIBLE counts visible cells.
- AVERAGEVISIBLE averages visible cell values.
- VISIBLECELLS * returns all visible cell values in a range as a list (1-dimensional array) and can be used within SUM, COUNT, AVERAGE, MAX, MEDIAN, etc., but also SORT, UNIQUE (Office365/Excel 2021) so it enables more functionality than the above 4 UDF.
- More complex regular formulas may work as well, as can be seen in the demo file. If run time becomes an issue, then it is better to use regular formulas as these execute faster.
Next UDF can be considered array manipulation functions.
- RANGETOLIST * returns all cell values in a range as a list (1-dimensional array). It is also a helper function that can be used within SORT and UNIQUE (Office365/Excel 2021), to retrieve all unique values from a 2-dimensional range (which UNIQUE alone cannot handle). Or with UDF SORTRANGE.
- LISTTORANGE * converts a list into a range (2-dimensional), given the amount of columns to be created, and row or column fill priority.
- REVERSERANGE * reverses the rows or columns of a range. REVERSERANGE within REVERSERANGE reverses both rows and columns.
- EXTRACTVALUES * extracts values from all/indicated rows and all/indicated columns into a reshaped 2-dimensional range. It is a multipurpose function that enables extracting, duplicating, and resequencing data.
Also UDF of pages bilinear interpolation , linear interpolation , as-the-crow-flies distance are included. Those pages contain more info.
- GLOBEDISTANCE calculates the as-the-crow-flies distance
- INTERPOLATEX interpolates X on a 1-dimensional table
- INTERPOLATEXY interpolates X and Y on a 2-dimensional table
- SPLINES are not included.
The following are no UDF as such, but handy extra functionality. It requires a few lines of VBA code in your worksheet, to make it respond to a double click. VBA code is found below and in file.
- GETFOLDER fills a cell with the folder name selected via a dialog box. Triggered by double clicking on a named cell.
- GETFILE fills a cell with the file name selected via a dialog box. Triggered by double clicking on a named cell.
- DATEPICKER will set the date after selecting a date from a date form. This form is triggered by double clicking on cells formatted as date.
File preview (top part only)
VBA code to be put in a moduleIn MS Excel, press Alt+F11 to open the Visual Basic Editor (Windows). Via top menu: Insert → Module. Copy the code below into the module.
Or export module mdlVariousUDF from the demo file and import it into your own file.