![]() The same tandem of Google Sheets LEN and SUBSTITUTE is used whenever you need to count specific characters, letters, or numbers. =LEN(SUBSTITUTE(A2, " ", "")) Google Sheets: count specific characters Now try and assemble all these into Google Sheets LEN and you'll see that no space is taken into account: But since I'm describing how to count characters without all spaces, I suggest you omit this argument as it is optional. occurence_number is normally used to specify the instance to replace.If you're going to ignore spaces, you need to replace them with literally nothing (empty string): "" replace_with should contain empty double-quotes.search_for should be a space character in double-quotes: " ".text_to_search is the range you work with: column A, or A2 to be exact. ![]() =SUBSTITUTE(text_to_search, search_for, replace_with, ) Though its main purpose is to replace one character with another, there's a trick to make it reduce spaces completely: You can go further and make your formula disregard even those single spaces between words. As you can see, when on its own, Google Sheets LEN counts them all:īut as soon as you integrate TRIM, all extra spaces are ignored: I added spaces in different positions in column A. When TRIM is paired with LEN, the latter doesn't count all those odd spaces. It checks the text for leading, trailing, and repeated spaces in-between. =ArrayFormula(SUM(LEN(A2:A6))) How to count characters without spaces in Google SheetsĪs I noted above, Google Sheets LEN function counts each and every character it sees including spaces.īut what if there are extra spaces added by mistake and you don't want to consider them for the result?įor cases like this, there's the TRIM function in Google Sheets. To make it work, you will have to add another function – ArrayFormula: But SUM in Google Sheets doesn't process arrays from other functions. Of course, you could incorporate the SUM function instead. In my case, the range is returned by the LEN function: To total characters in several cells, you should wrap your LEN in SUMPRODUCT – the function that tallies up the numbers from entered ranges. But, as bizarre as it is, it doesn't simply work this way. You may think that in a similar manner you could do a character count for the entire range of cells, like this: LEN(A2:A6). The LEN function computes all characters: letters, numbers, spaces, punctuation marks, etc.
0 Comments
Leave a Reply. |