How to Extract and Sort Function Names from an Excel Formula
Question details
The user needs to automatically extract all function names from an existing Excel formula, remove any duplicates, and sort them in alphabetical order.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing complex formulas by listing the functions used within them, including those containing periods like CEILING.MATH.
- Observed behavior
- A single cell output containing a comma-separated, alphabetically sorted list of unique function names extracted from the target formula.
Ensure your spreadsheet software supports dynamic arrays and regular expression functions (like REGEXEXTRACT), as these are required for advanced formula text extraction.
Use TEXTJOIN with REGEXEXTRACT and UNIQUE
This comprehensive formula extracts function names, handles names with periods, removes duplicates, and sorts the output alphabetically.
This approach uses a combination of dynamic array functions and regular expressions to parse the formula text. FORMULATEXT converts the target formula into a readable string, while REGEXEXTRACT pulls out matching function names.
Click on a blank cell where you want the extracted list of function names to appear.
Input the following formula, assuming the formula you want to analyze is in cell A2: =TEXTJOIN(", ",TRUE,SORT(UNIQUE(TOCOL(REGEXEXTRACT(FORMULATEXT(A2),"[A-Z][A-Z0-9.]+(?=\\()",1)))))
Press Enter. The cell will now display a comma-separated list of all unique functions used in A2.

Use ARRAYTOTEXT for Direct Array Formatting
A slightly shorter alternative using ARRAYTOTEXT to format the sorted array of function names without removing duplicates.
Analyze Complex Formulas Easily with WPS Spreadsheet
WPS Spreadsheet provides robust formula auditing tools and supports advanced text manipulation functions, making it easy to break down, extract, and analyze complex formulas.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the formulas you want to analyze.
- 2. Access the Formulas tab: Navigate to the 'Formulas' tab on the top ribbon to access formula auditing tools.
- 3. Evaluate complex logic: Use the 'Evaluate Formula' feature to step through nested functions and understand their execution order.
- 4. Apply text functions: Use text and array functions to extract and document your formula logic directly within the sheet.

Frequently Asked Questions
Why does the formula extraction pattern include periods?
Some Excel functions, such as CEILING.MATH or IS.EVEN, contain periods in their names. The regex pattern '[A-Z0-9.]+' ensures these characters are captured correctly so the entire function name is returned.
What does the FORMULATEXT function do in this context?
FORMULATEXT converts the actual calculation formula in a referenced cell (like A2) into a standard text string. This allows text manipulation functions like REGEXEXTRACT to search through it just like regular text.
Why use TOCOL after extracting the function names?
REGEXEXTRACT may return the matched function names in a multi-dimensional array or a horizontal row. TOCOL transforms this output into a single vertical column, which is required for the SORT and UNIQUE functions to process the list properly.




