To count the characters in a cell, put the text in A2, select another cell, and enter =LEN(A2). Excel returns the number of characters in A2, including spaces and punctuation. The LEN function is useful for checking text lengths, finding unexpected spaces, and making sure entries meet a character limit.
What You’ll Need
- Excel for Microsoft 365 on Windows, or another recent version of Excel. LEN is a long-standing Excel function and is also available in Excel for Mac and Excel for the web.
- A cell containing the text or value you want to measure. For the examples below, the text goes in column A and the LEN results go in column B.
- A blank cell for the result. If you want to check many rows, leave a results column beside your source data.
The formulas below use commas between arguments, as in a standard Microsoft 365 installation. Depending on your regional settings, Excel may require semicolons instead. If a formula with commas is rejected, check the separator Excel uses in formulas on your computer.
Step-by-Step
- Put the text in a cell. Enter or paste the item you want to measure into a worksheet cell. For a simple example, type
Excel tipsin A2. LEN works with one cell at a time, so each row of a list will usually have its own result. - Select the cell for the count. Click B2, or another empty cell beside your text. Keeping results in a separate column means you can see the original value and its character count together.
- Enter the LEN formula. With the sample text in A2, type
=LEN(A2)in B2 and press Enter. The result is 10: “Excel” has five characters, the space adds one, and “tips” adds four. The formula refers to A2, so if you change the text there, the result updates automatically. - Use a direct cell reference for other text. If the item you need to check is in a different cell, replace A2 with that cell reference. For example, if the text is in D7, enter
=LEN(D7)in a blank cell. This counts the contents of D7, not the formula or label in the result cell. - Count a whole list by filling the formula down. Suppose your text is in A2:A20 and the first result is in B2. Select B2, then drag the small square at the lower-right corner of the selection (the fill handle) down through B20. Excel adjusts the references row by row, so B3 checks A3, B4 checks A4, and so on. You can also copy B2, select B3:B20, and paste.
- Check that the results match the kind of count you need. LEN counts spaces, punctuation, and other characters in the cell. If A2 contains
Excel tips, the count is 10, not 9, because the space counts. If the count seems one character higher than expected, inspect the start, end, and middle of the text for spaces or other invisible characters. - Use LEN to test a character limit if needed. With a message in A2 and a maximum allowed length of 20 characters, enter
=IF(LEN(A2)<=20,"OK","Too long")in B2. For the sample textExcel tips, the result is OK because 10 is no more than 20. Change 20 to the limit you need. The formula reports a status; it does not prevent someone from entering longer text. - Save or reuse the result. Once the count is correct, you can leave the formula in the worksheet, copy it to other rows, or use the result in another calculation. If you need to send a fixed count rather than a live formula, copy the result cells and use Paste Special > Values. This replaces the formulas with their current results.
On Excel for Mac and Excel for the web, the LEN formula and cell references work the same way. Ribbon and fill-handle details can look slightly different, but you can still enter the formula in a cell and copy or fill it down. The instructions here are for the Microsoft 365 desktop app on Windows.
Common Mistakes
Forgetting that spaces count. LEN includes spaces before, after, and between words. A sentence with two words separated by a space has that extra character in its count. This is often the reason a result differs from a count made by eye.
Confusing LEN with a word count. LEN counts characters, not words. A cell containing red blue green has 14 characters, including its two spaces, but only three words. LEN alone cannot tell you how many words are present.
Measuring a number’s displayed format instead of its value. Excel stores numbers as values and may display them with currency symbols, decimal places, or other formatting. LEN does not count the formatting that you see in the cell. A date, for example, is stored as a serial number, so counting the cell directly may return the length of that number rather than the number of characters in the date as displayed.
If A2 contains a date and you want to count the characters in a particular date display, with the intended display set to month/day/year, use =LEN(TEXT(A2,"m/d/yyyy")). This returns 8 for a date displayed as 1/2/2024. The format string controls the text being measured; adjust it to match the display you actually need.
Expecting formatting or visual width to change the count. Changing a font, column width, number format, or text wrapping does not change the underlying text that LEN measures. A cell can show only part of a long entry because the column is narrow while LEN still counts all of its contents.
Removing the wrong spaces. A regular space is not the same as every invisible character. Text copied from a website or another system may include nonbreaking spaces or line breaks. A formula that removes ordinary spaces will not necessarily remove those other characters.
Filling down without checking references. When a formula is copied, ordinary references adjust automatically. That is usually what you want for a row-by-row count, but it can lead to unexpected results if the source cells are not in the same relative position. Check a few rows after filling the formula down.
Tips
Count without ordinary spaces. If A2 contains Excel tips and you want to ignore standard space characters, enter =LEN(SUBSTITUTE(A2," ","")) in B2. The result is 9 because SUBSTITUTE removes the one ordinary space before LEN counts the remaining characters. This removes every ordinary space, including spaces inside a phrase, so it is not the same as trimming only the ends.
Ignore extra leading and trailing spaces. If A2 contains two spaces before Excel tips and two after it, enter =LEN(TRIM(A2)) in B2. The result is 10. TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one. It does not remove every type of whitespace, and it changes the text being measured only inside the formula; it does not edit A2.
Use a separate cleanup step when the source data must be corrected. If you need the actual cell contents cleaned, create a separate cleaned-text column rather than assuming the count formula has changed the source. Review the cleaned result before replacing original data, especially if the spaces have meaning in codes or identifiers.
Use a clear column heading. Label the results column “Character count” or “Length.” A heading helps other people understand that the values are counts, not part of the original data. It is also useful when you sort, filter, or export the worksheet.
Check the maximum before importing or submitting data. For product descriptions, form fields, or codes with a fixed limit, put the required limit in the status formula and scan for results marked as too long. Keep the source text intact until you know whether a long entry should be shortened, corrected, or accepted as an exception.
Troubleshooting
The formula appears as text instead of showing a number. Check whether the cell is formatted as Text, or whether an apostrophe was entered before the equals sign. Change the cell format to General, then edit the formula and press Enter again. If many formulas are displayed rather than calculated, check whether Show Formulas is enabled on the Formulas tab and turn it off.
Excel shows a formula error after you enter it. Confirm that the formula begins with an equals sign, the parentheses are balanced, and the referenced cell exists. If the error is a parsing or name error, check whether your regional settings require semicolons instead of commas between formula arguments. Also make sure quotation marks in any text arguments are straight double quotes.
The result is larger than expected. Click the source cell and use the formula bar to inspect the actual contents. Look for spaces at the beginning or end, repeated spaces, line breaks, or text copied from another program. To test whether ordinary spaces are inflating the count, compare the normal result with the count that removes ordinary spaces. If only the latter seems correct, decide whether those spaces should actually be removed from the source.
The result is shorter than the visible value. Check whether the cell contains a number or date with display formatting. LEN measures the underlying value’s text representation, not necessarily the formatted appearance. If the intended target is a specific display, convert the value to text with the appropriate TEXT format inside the calculation, as shown in the date example.
Only the first result changes when the source list changes. Check that each result row points to the matching source row. Select a later result cell and inspect its reference in the formula bar. If the formula was copied as text or pasted into cells formatted as Text, convert those cells to General and re-enter or refill the formula.
TRIM does not remove the suspicious space. The character may be a nonbreaking space rather than an ordinary space. This can happen with copied web content. Identify or replace that character in a separate cleanup column before counting, and verify the cleaned text carefully; stripping unfamiliar characters blindly can damage names, codes, or other data.
Frequently Asked Questions
Does LEN count spaces and punctuation?
Yes. It counts ordinary spaces, punctuation marks, letters, and digits that are part of the cell contents. That includes spaces at the beginning or end of text.
Can LEN count characters in a range all at once?
LEN evaluates the value supplied to it. For a list, put a LEN formula beside the first row and fill it down so each row is counted separately. The results then remain aligned with their source cells.
Does LEN count words?
No. It returns a character count, not a word count. Spaces are included in the character count, but LEN does not identify word boundaries or distinguish words from punctuation.
Will LEN count the characters shown by number formatting?
Not necessarily. It does not count the visual formatting added by a currency, percentage, or date format. If you need the length of a specific displayed representation, convert the value to text in the formula using the format you want to measure.
Why does a blank-looking cell return a count?
The cell may contain spaces, a line break, or another invisible character. Select it and inspect the formula bar, or compare the result with a version that trims ordinary spaces. A formula that returns an empty string can also look blank, so check whether the cell contains a formula.
Is LEN available on Mac and in Excel for the web?
Yes. LEN and the basic formula syntax are supported in Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Interface details may vary, but the function itself works the same way.
Related
- Excel Accounting Format: Show Negative Numbers in Brackets
- Excel Data Analysis: How to Build a Useful Model
- Remove a Data Model from Excel: Steps and Effects
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MATCH: Find, Filter, and Export Matching DataExcel’s MATCH function does not export data: it finds the position of a value in a range. To…
- Excel Absolute Reference: How to Lock a Cell in a FormulaTo make a cell reference absolute in Excel, put a dollar sign before both its column letter and…
- Compare Two Sheets in Excel: Methods, Formulas, and TipsTo compare two Excel sheets, check corresponding cells with a formula such as =IF(January!A2=February!A2,"Match","Different"), then fill it across…
- Excel MAX Ignore Errors: Formula, Examples, and TipsTo find the largest value in Excel while ignoring error cells, use =AGGREGATE(4,6,A2:A10). In this formula, 4 selects…
- PMT Function Negative in Excel? The Sign Rule ExplainedYour PMT function is usually negative because Excel treats the loan amount and its repayments as cash flows…
- Excel FV Function Examples: How to Calculate Future ValueExcel’s FV function calculates the future value of an investment or savings plan from a fixed interest rate…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.