ExcelGoogle docsHow to

Count characters in a cell without spaces – Excel & Google docs

By November 14, 2013 No Comments

By using =Len() you’re going to return the character count including spaces by default.

Example:

 =len("hey there")

This formula will return 9, this is because there’s a space between “hey” and “there”.

Here’s the formula to get character count without spaces:

=len(SUBSTITUTE("hey there"," ",""))

This will return: 8

How does this work?

By nesting the Substitute formula, we can “substitute” (remove) the whitespaces and then the len() formula will count the characters.  If you were to use the substitute formula on its own here, it would return “heythere”, which is why the len() formula wrapper will return 8.

There you go. This will work in both Excel and Google docs spreadsheets 🙂

Dave

Author Dave

More posts by Dave