How to Split Words into Individual Letters and Count Letter Occurrences in WPS Spreadsheets
Question details
The user needs to place each character of one or more words into separate cells and count how many times each letter appears, often for printing, labeling, or production sheets.
- Product
- WPS Spreadsheets / Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user has one or more words and wants a dynamic, formula-driven way to split every character into its own cell and create a letter frequency table.
- Observed behavior
- The desired result is a spill range containing individual letters and a separate A-Z frequency table that updates when the source words change.
Check whether your WPS Spreadsheets or Microsoft Excel version supports LET, LAMBDA, SEQUENCE, MAP, and HSTACK. If those functions are not available, use the Text to Columns and COUNTIF fallback method described below.
Use LET, LAMBDA, and SEQUENCE to Split Characters and Count Letters
Use this method when your workbook runs in Microsoft 365, Excel 2021, or a WPS Spreadsheets version that supports dynamic arrays and LAMBDA functions.
This formula-based approach spills each character into separate cells with MID and SEQUENCE, then builds an A-Z alphabet with CHAR(SEQUENCE(26,,65)) and counts each letter by comparing the alphabet against the split characters.
Type the words that need to be split in a vertical range, for example A2:A5.
Select a blank cell such as C1 and enter =LET(n,MAX(LEN(A2:A5)),MID(A2:A5,SEQUENCE(1,n),1)). Press Enter so each character spills into separate columns.
Select another blank cell below the split result, for example C8, and enter =LET(chars,C1#,alphabet,CHAR(SEQUENCE(26,1,65)),count,MAP(alphabet,LAMBDA(chr,SUM(SIGN(chars=chr)))),HSTACK(alphabet,count)). Press Enter to list A-Z with the count for each letter.
Check that the dynamic array filled the required cells and that the frequency table updates automatically when the source words change.
Split Text to Columns and Count Letters with COUNTIF
Use this fallback when LAMBDA or dynamic array functions are unavailable and the words are short or have a fixed-length pattern.
Use WPS Spreadsheets to Split Words into Letters and Count Letter Frequency
Open the workbook in WPS Spreadsheets and apply the split-and-count formulas. WPS Office supports Excel formulas and file formats, so you can create a dynamic letter frequency table without needing Microsoft 365.
- 1. Open the workbook: Launch WPS Office, click Menu > Open, and select the Excel file containing the word list.
- 2. Enter the split formula: In WPS Spreadsheets, select a blank cell and enter =LET(n,MAX(LEN(A2:A5)),MID(A2:A5,SEQUENCE(1,n),1)).
- 3. Enter the count formula: Below the split result, enter =LET(chars,C1#,alphabet,CHAR(SEQUENCE(26,1,65)),count,MAP(alphabet,LAMBDA(chr,SUM(SIGN(chars=chr)))),HSTACK(alphabet,count)).
- 4. Save or print the sheet: Click Menu > Save As and choose Excel Workbook (.xlsx), or use Menu > Print to print the resulting frequency table directly.
Frequently Asked Questions
Can I split a word into individual letters without using formulas in WPS Spreadsheets?
Yes. Select the word cells, click Data > Text to Columns, choose Fixed width, insert a column break after each character, and click Finish. This places each letter into its own cell manually.
What should I do if my WPS Spreadsheets version does not support LAMBDA or HSTACK?
Use the Text to Columns method to split the letters, then build a count table with =COUNTIF($B$2:$F$5,H2). Alternatively, update WPS Office to a newer version that includes dynamic array functions.
How do I count uppercase and lowercase letters separately?
Generate both uppercase and lowercase alphabets, for example with CHAR(SEQUENCE(26,,65)) and CHAR(SEQUENCE(26,,97)), then compare the split characters against each alphabet separately using MAP or COUNTIF.
Will the formula update automatically when I change the source words?
Yes. The LET and LAMBDA method uses dynamic array references, so if the source words in A2:A5 change, the split letters and letter count table update automatically.
Can I use this for multiple words with different lengths?
Yes. The split formula uses MAX(LEN(A2:A5)) to determine the longest word and MID with SEQUENCE to spill each character into columns, leaving blank cells for shorter words.
