Measuring Text With LEN

Sometimes you need to know how many characters a value has. Maybe a product code must be exactly 8 characters, or a tweet must stay under a limit.

The LEN function counts the characters in a piece of text and returns that number. It is short for length, and it is one of the most useful helper functions in any spreadsheet.

LEN takes just one thing: the text (or a cell holding text). It returns the count of characters.

LEN("Hello") returns 5 because there are five letters. The result is always a plain number you can use in other formulas.

An important detail: LEN counts every character, including spaces and punctuation.

LEN("Hello world") returns 11 — five letters, a space, then five more letters. This is why LEN is great for spotting hidden spaces that you cannot see by looking at a cell.

In real sheets you point LEN at a cell. If A2 holds a customer ID like CUST-00417, you can check its length:

This returns 10. Drag the formula down a column and you instantly see if any ID has the wrong number of characters.

Combine LEN with IF to flag bad data. Suppose every code should be exactly 8 characters long.

This formula returns OK when the length matches and Check when it does not. It turns a tedious eyeball check into an automatic test.

When you use LEN on a number, the spreadsheet first treats it as text, then counts the digits.

If A2 holds 12345, then LEN(A2) returns 5. Be aware that formatting like commas or currency symbols is display only — LEN counts the underlying value, so 1,000 still counts as 4 characters.

What does LEN do with a blank cell? It returns 0.

This makes LEN a quick way to test whether a cell is truly empty. LEN(A2)=0 is TRUE only when there is no content at all — useful for catching cells that look empty but contain a stray space (which would return 1, not 0).

LEN often acts as a building block. Remember that RIGHT can grab characters from the end, and MID can start anywhere. LEN tells those functions where the end is.

For example, to drop the last 3 characters of A2, you grab LEN(A2) - 3 characters from the left. LEN calculates the right number for you no matter how long each cell is.

A clever trick uses LEN twice to count how many times a character appears. The idea: measure the length, then measure it again with the character removed, and subtract.

Here SUBSTITUTE removes every space from A2. The difference between the original length and the shorter version equals the number of spaces — a quick way to count words minus one.

LEN works identically in Microsoft Excel and Google Sheets. One character equals one count in both.

The only edge cases involve special characters from other languages, but for everyday text and numbers you can rely on LEN giving the same answer everywhere.

LEN also helps you make values a uniform length. Suppose every ID should be 6 characters, padded on the left with zeros.

You calculate how many zeros are missing with 6 - LEN(A2), then build that many zeros with REPT and join them in front. If A2 is 417, the result is 000417, perfectly aligned with the rest of your codes.

Test your understanding of LEN.

You learned to measure the length of text.

• LEN(text) returns the number of characters
• Spaces and punctuation are counted
• A blank cell returns 0
• Combine LEN with IF to validate fixed lengths
• Use LEN to feed character counts into LEFT, RIGHT, and MID

Next you will clean up messy spacing with TRIM.