To count the characters in text after removing extra spaces, put the text in A2 and enter =LEN(TRIM(A2)) in another cell. TRIM cleans up ordinary spaces; LEN counts the characters left, including the single spaces between words. This is useful for checking names, imported text, and other entries where extra spaces can distort a character count.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows. The formula also works in Excel for Mac and Excel for the web.
- A cell or column containing the text you want to check. In the example below, the source text is in A2.
- An empty cell for the result. If you have several entries, leave room for a formula beside each one.
You do not need to enable an add-in or change a setting. TRIM and LEN are standard Excel functions. The formula returns a number in a separate cell; it does not alter the original text.
Step-by-Step
- Identify the text to clean. Find the cell with the entry you want to count. This guide uses A2 as the source cell. For example, A2 might contain two spaces before the word Excel, three spaces between Excel and formulas, and two spaces after formulas. The extra spaces may be hard to spot in the worksheet, but they affect a direct character count.
- Choose a result cell. Click an empty cell beside the source, such as B2. Keeping the calculation in a separate column lets you compare the cleaned count with the original entry and leaves the source data unchanged. If your worksheet already has a header row, add a clear heading such as Cleaned character count above the results.
- Enter the combined formula. With the source text in A2 and the result cell in B2, type
=LEN(TRIM(A2))in B2 and press Enter. The inner TRIM function processes the text first. LEN then counts the characters in the text that TRIM returns. Make sure the formula begins with an equals sign and that the cell reference points to your source, not the result cell. - Check the result against a simple example. With the text described in step 1 in A2, TRIM reduces the repeated spaces to one space between the words and removes spaces at the beginning and end. The resulting text is Excel formulas: Excel has five letters, the space is one character, and formulas has eight letters. The formula therefore returns 14. If your result differs, check the actual cell contents and whether the spaces are ordinary spaces.
- Use the formula for other rows. If each row has a source value in column A, select B2 and use the small square at the lower-right corner of its selection, called the fill handle. Drag it down, or double-click the fill handle when there is adjacent data to guide the fill. Excel adjusts the reference as it fills: the next row uses A3, then A4, and so on. Check a few filled rows to make sure the results line up with the intended source entries.
- Change the reference for a different layout. If your text is in D5 instead, enter
=LEN(TRIM(D5))in the cell where you want that result. The formula structure stays the same; replace A2 with the address of the source cell. For a reference to a cell on another worksheet, select the destination cell, type the formula prefix, click the source cell on its sheet, and complete the closing parenthesis. Excel inserts the worksheet reference for you. - Keep the cleaned count or clean the text itself. The combined formula reports a number; it does not place the cleaned wording in a new cell. If you need cleaned text as well, create a separate helper column using
=TRIM(A2)with the source in A2 and the helper result in B2. You can then use LEN on that helper cell, or keep the combined formula for a compact count. Keeping the original column is a sensible choice when you need to review changes. - Turn formula results into fixed values only if needed. A formula result updates when the source changes. If you need a snapshot, copy the result cells, then use Home > Paste > Paste Values to replace the formulas with their current numbers. Do this only after checking the results, because fixed values will not recalculate if the source text is edited later. Excel for Mac and Excel for the web use the same function and a similar Paste Values command, though the ribbon layout can vary slightly.
The key idea is that TRIM runs before LEN. LEN is not measuring the raw contents of A2; it is measuring the text returned by TRIM. This makes the formula useful when you want to know how long an entry is after ordinary extra spaces have been normalized.
Common Mistakes
- Expecting TRIM to remove every kind of whitespace. Excel’s TRIM handles ordinary space characters: it removes leading and trailing spaces and reduces repeated spaces between words to one. It does not reliably remove tabs, line breaks, or nonbreaking spaces, which often appear in text copied from websites or other systems. A formula can look correct and still count hidden characters that TRIM did not change.
- Assuming LEN counts only letters. LEN counts spaces and punctuation too. After TRIM, a single space between two words still contributes one character. If you are validating a maximum length, decide whether spaces and punctuation belong in the count before interpreting the result.
- Putting the formula in the source cell. Replacing the original text with a formula that refers to that same cell creates a circular reference or overwrites the data you meant to inspect. Put the calculation in a separate cell or column.
- Using the wrong cell reference. When copying a formula down, a mistyped reference can count a different row or a blank cell. Select the result cell and check the formula bar, then compare its reference with the source in the same row.
- Expecting a cleaned string instead of a number. LEN(TRIM(…)) returns a numeric count. Use TRIM by itself in a helper cell if the goal is to display the cleaned wording rather than its length.
Tips
- Label the result column clearly. A heading such as Length after TRIM tells other people that the number is based on cleaned text, not the untouched source. This distinction matters when results are used for data checks or reviewed by someone else.
- Test a few different kinds of entries. Check a short value, a value with multiple words, and a value that appears to have spaces around it. This helps catch unexpected source data before you fill the formula through hundreds or thousands of rows.
- Use a helper column for review. If you are cleaning an imported list, a column with TRIM’s text output makes invisible spacing problems easier to diagnose. You can filter or inspect the cleaned text before deciding whether to replace the original data.
- Remember that blank inputs return zero. If A2 is empty, or contains a formula that returns an empty string, the combined formula returns 0. That is a numeric result, not a text value. If you prefer the result cell to appear blank for empty source cells, use the optional formula in Troubleshooting.
- Keep the calculation simple when you can. For ordinary extra spaces, the combined formula is enough. Add SUBSTITUTE or CLEAN only when you have evidence that the source contains other characters; extra processing can make a formula harder to inspect without improving its result.
Troubleshooting
The result is higher than expected. The source may contain a nonbreaking space, a tab, or a line break. A common fix for nonbreaking spaces is to replace character 160 with an ordinary space before applying TRIM. With the text in A2 and the result in B2, enter =LEN(TRIM(SUBSTITUTE(A2,CHAR(160)," "))) in B2. SUBSTITUTE changes nonbreaking spaces to ordinary spaces, TRIM normalizes those spaces, and LEN counts the result. This does not remove every possible unusual character, so inspect the data if the count is still unexpected.
There are tabs or control characters in the text. CLEAN can remove many nonprinting ASCII control characters. With the source in A2 and the result in B2, try =LEN(TRIM(CLEAN(A2))). CLEAN and TRIM address different issues: CLEAN targets certain control characters, while TRIM handles ordinary spaces. CLEAN does not remove nonbreaking spaces, so combine it with the SUBSTITUTE approach when you have reason to suspect both types. Neither function is a universal cleaner for every Unicode whitespace character.
The formula appears as text instead of calculating. Check whether the cell is formatted as Text. Change its format to General using Home > Number Format, then edit the cell and press Enter so Excel evaluates the formula. Also check that there is no apostrophe before the equals sign. A formula entered as text will display rather than return a number.
You see a formula error or a result of zero. Confirm that the source reference is valid and that the formula has balanced parentheses. A zero is expected for a blank cell or a formula-produced empty string; it is not an error. To show a blank result for either case, with source text in A2 and the formula in B2, use =IF(A2="","",LEN(TRIM(A2))). This checks whether A2 is empty before calculating the cleaned length.
The formula works in one row but not after filling down. Check that each row has a source value in the expected column and that the formula did not skip or shift a row. Ordinary references such as A2 adjust as you fill down. If the source is on another sheet or your data is in a different column, verify the reference in a row where the answer is known.
Frequently Asked Questions
What does the TRIM LEN formula do?
It applies TRIM to the referenced text, then uses LEN to count the resulting characters. For ordinary spaces, this removes spaces at the start and end and reduces repeated spaces between words to one.
Does TRIM LEN count spaces between words?
Yes. TRIM leaves one ordinary space between words, and LEN counts that space as one character. The result is the length of the normalized text, not a count of letters only.
Does the formula change the original cell?
No. It returns a number in the cell where you enter the formula. To display cleaned wording, use TRIM in a separate helper cell; to replace source data, review the cleaned output first and then choose an appropriate copy-and-paste method.
Why does TRIM not remove a space I can see?
The character may be a nonbreaking space or another whitespace character rather than an ordinary space. Try the SUBSTITUTE formula in Troubleshooting for nonbreaking spaces, or use CLEAN for certain control characters. If the source comes from an external system, its characters may need more specific cleanup.
Can I use this formula in Excel for Mac or Excel for the web?
Yes. TRIM and LEN work in Excel for Mac and Excel for the web, and the formula syntax is the same. The ribbon layout and some keyboard shortcuts can differ, but you can enter the formula directly in a cell in either version.
What is the difference between LEN(A2) and LEN(TRIM(A2))?
LEN(A2) counts the text as it is stored, including extra ordinary spaces at the beginning, end, or between words. LEN(TRIM(A2)) counts after those ordinary spaces have been cleaned up. Choose the version that matches what you want to measure.
Related
- Excel Solver Answer Report: Create and Read It
- How to Run Solver in Excel: Setup and Optimization
- K-Means Clustering in Excel: Solver and Easier Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Simple Gradebook Spreadsheet: Setup, Formulas, and TipsA simple gradebook spreadsheet can track students, assignment scores, and current averages in Microsoft Excel. With a student…
- Number Sequence in Excel: How to Continue a SeriesTo continue a number sequence in Excel, enter the first two values, select both cells, then drag the…
- Unique in Excel: Get Values Without DuplicatesTo list each value once in Excel for Microsoft 365, enter =UNIQUE(A2:A10) in an empty cell, replacing A2:A10…
- Excel SUM: How to Add Numbers with a FormulaTo add numbers in Excel, select the cell where you want the total, type a SUM formula, and…
- IFERROR in Excel: Leave Blank When a Formula ErrorsTo make an Excel formula show a blank instead of an error, wrap it in IFERROR and use…
- Excel PV vs. NPV: Formulas, Examples, and DifferencesIn Excel, use PV to find the value today of a fixed series of equal payments, and use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.