In Microsoft Excel, the LEN function gives the length of a text string by counting its characters. For example, if A2 contains Maple, enter =LEN(A2) in another cell to get 5; spaces count too.
The Excel Function for String Length
LEN is Excel’s built-in function for finding how many characters are in a value. It is useful when you need to check text length, find entries that exceed a limit, compare two strings, or prepare data for import. The answer is a number, so you can use it in calculations and logical tests as well as display it on its own.
The function works in Excel for Microsoft 365 on Windows, Mac, and the web. Its basic syntax is =LEN(text), where text is the string or cell reference to count. In a typical worksheet, the simplest approach is to put each text value in one column and its length formula in the next.
For example, with the word Maple in A2, the formula =LEN(A2) returns 5. The function does not need a special text format or a separate setup step: type the formula in a cell and press Enter.
What LEN Counts
LEN counts characters in the value supplied to it. That includes letters, numbers stored as text, punctuation, and spaces. It counts every ordinary space, including spaces at the beginning or end of a string that may be hard to see. A cell containing red fox has a length of 7: three letters, one space, and three more letters.
Excel counts the cell’s value, not necessarily the way that value looks on screen. Changing a cell’s font, column width, or alignment does not change its length. Number formatting can also affect the distinction between a displayed value and its underlying value: a date cell is stored as a serial number, so LEN applied directly to it counts the text representation of that number, not the date as displayed.
LEN counts characters, not bytes or the visual width of a string. A narrow letter and a wide letter each count as one ordinary character. Some less common Unicode symbols, including certain emoji, may be counted differently depending on how Excel handles the character pair and the workbook’s compatibility settings. If exact character counts for emoji or other supplementary Unicode symbols matter, test representative values in the Excel version and workbook you use.
Syntax and Everyday Examples
The syntax has one argument: LEN(text). The argument can be a cell reference, a text value in quotation marks, or an expression that produces text. In a formula, text literals need straight double quotation marks. In many regional settings, Excel uses commas between function arguments; this function has only one argument, so no separator is needed.
With text in A2, enter =LEN(A2) to count it. If A2 contains Order #482, the result is 11 because the space and the punctuation mark count along with the letters and digits. To get lengths for a list, place the formula beside the first item and use the fill handle or copy it down. Excel adjusts the reference from A2 to A3, A4, and so on.
LEN also accepts a text value directly. The formula =LEN("Good morning") returns 12, counting the space between the words. If the supplied cell is blank, LEN returns 0. If a cell contains a number, Excel can count its text representation; however, the result reflects the value as converted to text, not necessarily a custom display format.
A length check can be part of a rule. With a user-entered description in A2, =LEN(A2)>20 returns TRUE when it has more than 20 characters and FALSE otherwise. That makes LEN useful for identifying entries that may exceed a system’s field limit or a form’s stated maximum.
Spaces and Other Hard-to-Spot Characters
Unexpected length results often come from invisible characters rather than a mistake in LEN. A leading space, trailing space, line break, or nonbreaking space can make two entries look alike while their counts differ. For example, pear has length 4, while pear with a trailing space has length 5. This matters when matching imported names, product codes, or form responses.
To count characters other than ordinary spaces, first consider the cell layout: with the text to inspect in A2, =LEN(SUBSTITUTE(A2," ","")) returns the length after removing every ordinary space. If A2 contains red fox, the result is 6. This does not remove tabs, line breaks, or nonbreaking spaces, which are different characters.
To see whether trimming changes the length, use the layout of text in A2 and enter =LEN(A2)-LEN(TRIM(A2)). TRIM removes leading and trailing ordinary spaces and reduces repeated spaces between words to one, so this difference shows how many spaces were removed. For instance, if A2 contains two spaces, then red, three spaces, then fox, and one trailing space, the original length is 12 and the trimmed length is 7; the formula returns 5. It is not a trailing-space-only test because TRIM also changes repeated internal spaces.
Imported data may contain nonbreaking spaces that TRIM does not remove. If a count remains unexpectedly high after cleaning, inspect the source or test for those characters rather than assuming the function is wrong. Cleaning formulas should be chosen for the actual kind of whitespace present.
Using LEN with Other Excel Functions
LEN is often most useful as one part of a larger formula. Paired with IF, it can label entries that need attention. With the text in A2, =IF(LEN(A2)>20,"Too long","OK") displays a warning when the value exceeds 20 characters. This checks the value in the cell, not the length of what might be visible after a different number format is applied.
For dates, convert the value to the display text you want to assess before counting it. If A2 contains a date and you want the length of a month-day-year label, =LEN(TEXT(A2,"mmm d, yyyy")) counts the text created by TEXT. For a date displayed as Jan 5, 2026, the result is 11. The format string controls the text being counted; use a format appropriate to the date style you need.
LEN can also help compare two text values. If A2 and B2 contain two versions of an identifier, =LEN(A2)=LEN(B2) reports whether they have equal lengths. A TRUE result does not prove that the strings are identical: AB12 and CD34 both have four characters. To test whether the values themselves match, use a direct comparison such as =A2=B2.
Common Mistakes and Troubleshooting
- Counting spaces incorrectly: Spaces are characters. Check for leading, trailing, or repeated spaces before concluding that the result is wrong.
- Confusing characters with words: LEN counts all characters, not words. A phrase’s spaces and punctuation contribute to its length.
- Expecting formatting to be counted: Font size, bold text, and cell width have no effect. For dates or numbers, use TEXT if you specifically need the length of a chosen display representation.
- Comparing lengths instead of values: Equal LEN results mean only that two inputs have the same count, not the same content.
- Using a localized argument separator: Some regional Excel settings display semicolons instead of commas in formulas with multiple arguments. LEN itself takes one argument, but the separator matters in combined formulas.
If a formula displays as text instead of returning a result, check whether the cell is formatted as Text or whether an apostrophe appears before the equals sign. Change the cell to General, then re-enter the formula. Also confirm that the formula begins with = and that the cell reference points to the intended value.
LEN in Excel, Sheets, and Programming
For Excel, use LEN. Google Sheets also has a LEN function with the same basic purpose and familiar syntax. In programming languages, the name may differ: Python uses the built-in len(), while JavaScript commonly uses the length property on a string. These are related ideas, but their handling of Unicode characters and string types can differ, so do not assume every tool will return the same count for every symbol.
In Excel for Microsoft 365 on Windows, Mac, and the web, the function name and basic use are the same. Keyboard shortcuts and some interface details differ by platform, but there is no separate LEN command to find in a ribbon menu. Type the formula directly into a worksheet cell. For many non-English regional settings, Excel may use translated function names or different formula separators, so follow the conventions used by that installation.
Frequently Asked Questions
What function gives the length of a string in Excel?
Use LEN. If the string is in A2, enter =LEN(A2). The result is the number of characters in the cell’s value, including spaces.
Does LEN count spaces in Excel?
Yes. Ordinary spaces count as characters, including spaces before or after the visible text. If you want to exclude ordinary spaces, remove them within a formula using SUBSTITUTE before counting.
How do I count characters without spaces?
With the text in A2, use =LEN(SUBSTITUTE(A2," ","")). This removes ordinary space characters before LEN counts the remaining text. Other whitespace, such as tabs or nonbreaking spaces, is not removed by that formula.
Does LEN count words or bytes?
No. LEN counts characters, not words, file size, or bytes. Spaces and punctuation are included in the character count, and the visual width of a character does not determine its count.
Why is my LEN result larger than the visible text?
The cell may contain extra spaces, a line break, or another invisible character. Imported data is a common source. Check the source text and use a cleaning method suited to the specific character before relying on the count.
Related
- Energy Audit Template in Excel: What to Include
- How to Indent Text in an Excel Cell
- Encrypt an Excel File with a Password: Steps and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Get the Highest Number in Excel with the MAX FunctionTo get the highest number in a range, use Excel’s MAX function. With sample values in A2:A8, enter…
- IFERROR Formula in Excel: Syntax, Examples, and FixesIn Excel, use IFERROR to return a chosen result when a formula produces an error: it has the…
- How to Sort in Excel: Steps, Sort Options, and FixesTo sort data in Excel, select a cell in the list, open Data > Sort, choose the column…
- INDIRECT in Excel: How to Drag Formulas to New CellsIf you want an INDIRECT formula to follow different cells as you drag it, build its address from…
- Excel Conditional Formatting Icon Sets: Formula GuideYou cannot directly type a formula into the icon set rule dialog box. Instead, you use a formula…
- Excel Filter Shortcut: Turn Filters On and Open MenusIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn filter buttons on or off for a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.