How to Count Underscores or Any Character in an Excel Cell in WPS Spreadsheet
Question details
The user needs to count how many times a specific character, such as an underscore, appears in a text cell in WPS Spreadsheet or Excel.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- Text analysis and data cleaning in a WPS Spreadsheet or Excel workbook where character frequency must be measured.
- Observed behavior
- The user wants a formula that returns the number of times a specified character appears in a cell, such as counting underscores in a text string.
Open your workbook in WPS Spreadsheet and identify the cell that contains the text to analyze. Decide whether the character match must be case-sensitive, because SUBSTITUTE distinguishes uppercase and lowercase letters.
Count underscores or any character with LEN and SUBSTITUTE
Use this formula when you need an exact count of a single character or symbol in a text cell.
The formula calculates the total length of the original text, then subtracts the length after all occurrences of the target character are removed. The difference equals how many times that character appears.
Click the cell where you want the count to appear, such as B1.
Type =LEN(A1)-LEN(SUBSTITUTE(A1,"_","")) and press Enter. This counts underscores in cell A1.
To count a different character, replace "_" in the formula with the character you need, for example =LEN(A1)-LEN(SUBSTITUTE(A1,"a","")).
Count Any Character in WPS Spreadsheet Using the LEN and SUBSTITUTE Formula
Use the same Excel-style LEN and SUBSTITUTE formula in WPS Spreadsheet to count underscores, letters, numbers, or symbols without leaving your workbook.
- 1. Open WPS Spreadsheet: Launch WPS Office and open an .xlsx workbook, or create a new spreadsheet.
- 2. Select the result cell: Click the cell where the count should appear, such as B1.
- 3. Enter the formula: Type =LEN(A1)-LEN(SUBSTITUTE(A1,"_","")) to count underscores in cell A1.
- 4. Press Enter and review: Press Enter to calculate the number of underscores. Replace the target character in the formula to count a different one.
Frequently Asked Questions
Does the formula distinguish between uppercase and lowercase letters?
Yes. SUBSTITUTE is case-sensitive when you replace a letter, so =LEN(A1)-LEN(SUBSTITUTE(A1,"a","")) counts only lowercase "a". Use "A" to count uppercase "A".
Can I count a word instead of a single character?
This specific formula is designed for one character. For a word, use =(LEN(A1)-LEN(SUBSTITUTE(A1,"word","")))/LEN("word") to divide the length difference by the word length.
Why does my formula return 0?
This usually means the target character does not appear in the referenced cell. Check the cell reference and make sure the target character inside the formula matches the data, including case.
Can this formula work in Microsoft Excel too?
Yes. LEN and SUBSTITUTE are standard Excel-compatible functions, so the same formula also works in Microsoft Excel and WPS Spreadsheet.
