Formula Errors

How to Use an Excel Formula to Return 'All' Based on Comma Count

Algirdas Jasaitis Oct 10, 2026 869 views

Question details

The user needs to display the original text if a cell contains zero or one comma, and display the word "All" if it contains two or more commas.

How to Use an Excel Formula to Return 'All' Based on the Number of Commas

Product: Excel
Device & OS: not provided
Scenario: Managing a dataset where cells contain multiple comma-separated items (like colors) and standardizing cells with many items into a single category label.
Observed behavior: The user needs a logical function that evaluates the delimiter count in a text string and conditionally returns specific text strings based on that count.

Before you start

Verify that your target cells consistently use standard commas (,) as separators and do not mix them with semicolons or other punctuation marks.

Solution 1Recommended

Use the IF, LEN, and SUBSTITUTE Functions

This method calculates the number of commas by subtracting the length of the string without commas from the original string's length, then applying conditional logic.

By combining the LEN and SUBSTITUTE functions, you can accurately count the exact number of specific characters (like commas) within any text string. The IF function then determines the final output based on this count.

Select the output cell

Click on the cell next to your data (e.g., cell B2) where you want the result to appear.

Input the formula

Enter the following formula into the formula bar: =IF(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))<=1,A2,"All")

Apply and fill down

Press Enter to apply the formula. Then, click and drag the fill handle (the small square at the bottom-right corner of the cell) down to apply the formula to the rest of your column.

Use the IF, LEN, and SUBSTITUTE Functions

Logic Explained: The SUBSTITUTE formula removes all commas. Comparing the new length to the original length gives the exact number of commas. If the difference is 1 or 0, it returns the original text; otherwise, it outputs 'All'.

WPS Spreadsheet Data Management

Easily Process Complex Text Formulas in WPS Office

WPS Spreadsheet fully supports advanced text processing functions like IF, LEN, and SUBSTITUTE. You can flawlessly execute these operations to categorize data with zero compatibility issues.

1. Open your spreadsheet: Launch WPS Office and open your workbook in WPS Spreadsheet.
2. Select the target cell: Click on the blank cell where the conditional text should be displayed.
3. Enter the combined formula: Type the formula =IF(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))<=1,A2,"All") and press Enter.
4. Fill the remaining data: Double-click or drag the fill handle downward to instantly apply the logic across your entire dataset.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Built-in syntax highlighting to help you write and troubleshoot complex nested formulas easily.Lightweight, lightning-fast, and completely free to use for everyday data processing tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Can I change the formula to count different delimiters like semicolons?

Yes, simply replace the comma in the SUBSTITUTE function with your desired delimiter. For example, to evaluate semicolons, use: =IF(LEN(A2)-LEN(SUBSTITUTE(A2,";",""))<=1,A2,"All").

What if my data contains extra spaces around the commas?

The current formula only counts the comma character itself, so spaces will not affect the count logic. If you need to remove extra spaces from the returned original text, you can wrap the true-condition output in a TRIM function: =IF(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))<=1,TRIM(A2),"All").

How do I change the threshold to trigger "All" only for 3 or more commas?

You can adjust the logical test condition from <=1 to <=2. The updated formula would be =IF(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))<=2,A2,"All"), which returns the original text for up to two commas.

Excel & Data Problems