Function Problems

Check if Letters of One Word Can Form Another Word in Excel

Muhammad Talha Sep 25, 2026 869 views

Question details

The user needs an Excel formula to determine if every letter in a test word can be sourced from a main source word without using any character more times than it appears in the source word.

How to Check Whether Letters of a Word Can Be Formed from Another Word in Excel

Product: Excel
Device & OS: not provided
Scenario: Performing advanced text validation, such as checking anagrams or determining if a smaller string's characters are a valid subset of a larger string's characters based on exact frequencies.
Observed behavior: Requires a formula that returns TRUE if the test word is valid based on available letter counts (e.g., "found" in "foundational") and FALSE if invalid (e.g., "addition" in "foundational").

Before you start

Ensure your version of Excel supports modern dynamic array functions like LET, SEQUENCE, and MAP, which are required for this specific validation formula to calculate correctly.

Solution 1Recommended

Use LET, SEQUENCE, and MAP for Character Count Comparison

Create a dynamic array formula that compares the character frequencies between a source word and a range of test words.

To ensure that repeated letters are handled correctly, the best approach is to count how many times each letter appears in both the test word and the source word. By checking the difference in string lengths before and after removing a specific letter, Excel can calculate character frequencies.

Prepare your data

Enter your main source word in cell A1 (e.g., "foundational") and list your test words in cells C1 through C6.

Enter the formula

Select an empty cell next to your test words (e.g., D1) and input the following formula: =LET(src,LOWER($A$1),letters,CHAR(SEQUENCE(26,,97)),MAP(C1:C6,LAMBDA(w,LET(x,LOWER(w),AND(LEN(x)-LEN(SUBSTITUTE(x,letters,""))<=LEN(src)-LEN(SUBSTITUTE(src,letters,"")))))))

Review the results

Press Enter. The dynamic array formula will automatically spill down the column, displaying TRUE if all letters in the test word can be sourced from A1 without exceeding the available letter counts, and FALSE otherwise.

Use LET, SEQUENCE, and MAP for Character Count Comparison

How it works: The SEQUENCE(26,,97) function generates the ASCII codes for all 26 lowercase English letters. The formula then substitutes each letter with a blank space and compares the length difference to accurately determine the count of every letter.

Efficient Data Processing with WPS Spreadsheet

Analyze Text Data Easily with WPS Office

You can perform this same advanced text validation using WPS Spreadsheet. WPS Office offers comprehensive support for modern array formulas, allowing you to seamlessly process complex character comparisons without modifying your existing Excel workbooks.

1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing Spreadsheet document.
2. Input the text data: Type your main source word in cell A1 and your list of test words in column C.
3. Apply the validation formula: Paste the LET and MAP character-counting formula into an empty column and press Enter to instantly see your TRUE or FALSE array results.
4. Save your document: Save your work seamlessly in standard .xlsx format to retain full cross-platform compatibility.

Free, lightweight, and fast alternative to Microsoft Office100% compatible with Excel .xlsx formats and modern functionsIntuitive interface for complex formula creation and text analysisBuilt-in support for dynamic array functions like LET, SEQUENCE, and MAP

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return a #NAME? error?

A #NAME? error typically occurs if your spreadsheet software is an older version that does not support modern dynamic array functions like LET, SEQUENCE, or MAP. Updating to a newer version like Office 365 or using WPS Office can resolve this issue.

Is this formula case-sensitive?

No. The formula uses the LOWER function to convert both the source word and the test words to lowercase before comparing their character counts, making the validation fully case-insensitive.

How can I check just one word instead of a range of cells?

If you are testing a single cell (e.g., C1) instead of an array like C1:C6, you can remove the MAP function entirely. Use this simplified formula instead: =LET(src,LOWER($A$1),x,LOWER(C1),letters,CHAR(SEQUENCE(26,,97)),AND(LEN(x)-LEN(SUBSTITUTE(x,letters,""))<=LEN(src)-LEN(SUBSTITUTE(src,letters,"")))).

Excel & Data Problems