Function Problems

How to Extract and Sort Function Names from an Excel Formula

Jason MillerJason Miller Nov 10, 2026 869 views

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.

How to Extract and Sort Function Names from an Excel Formula
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.
Before you start

Ensure your spreadsheet software supports dynamic arrays and regular expression functions (like REGEXEXTRACT), as these are required for advanced formula text extraction.

Solution 1Recommended

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.

1
Select the output cell

Click on a blank cell where you want the extracted list of function names to appear.

2
Enter the extraction formula

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)))))

3
Execute the formula

Press Enter. The cell will now display a comma-separated list of all unique functions used in A2.

Use TEXTJOIN with REGEXEXTRACT and UNIQUE
Regular Expression Pattern: The pattern '[A-Z][A-Z0-9.]+(?=\()' specifically identifies uppercase text that may include numbers and periods, followed immediately by an opening parenthesis. This perfectly matches Excel functions.
Advanced Spreadsheet Tools

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. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the formulas you want to analyze.
  2. 2. Access the Formulas tab: Navigate to the 'Formulas' tab on the top ribbon to access formula auditing tools.
  3. 3. Evaluate complex logic: Use the 'Evaluate Formula' feature to step through nested functions and understand their execution order.
  4. 4. Apply text functions: Use text and array functions to extract and document your formula logic directly within the sheet.
Fully compatible with Microsoft Excel formulas and functionsAdvanced text, regular expression, and array manipulation capabilitiesBuilt-in formula auditing to evaluate complex logic step-by-stepFree and lightweight spreadsheet solution
microsoft office alternative - wps office

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.