4

I would like to count the words and characters of a range of cells, excluding all cells that start with "§".

The text is arbitrary UTF8-text, for example:

"my test string" = 3 words, 14 characters

"über den Wolken" = 3 words, 15 characters (16 bytes)

"план" = 1 word, 4 characters (8 bytes)

"§anything" = 0 words, 0 characters (excluded because it starts with §)

The macro should add up all these values und give a grand total of words/characters.

1 Answer 1

9

Are you sure you need a macro? One potential non-macro setup:

  • Text is in column A
  • In column B =IF(LEFT(A1;1)="§";"";TRIM(A1)) This replaces the string with an empty string if if starts with § and otherwise removes any leading, trailing, or double spaces
  • In column C =IF(LEN(B1)=0;0;LEN(B1)-LEN(SUBSTITUTE(B1;" ";""))+1) This is the number of words. For empty strings it shows 0 words. Otherwise it counts the number of spaces and adds 1 to calculate the number of words.
  • In column D =LEN(B1) This is the number of characters. If some of your strings have double spaces that you do want counted you might have to adjust this last formula (since the TRIM function removes double spaces as well as trailing spaces).

All formulas can be copy-pasted down however many columns you need. Then you can sum up the word and character counts in another cell.

2
  • The problem is that these are large lists and I cannot sacrifice a whole column for that - I need to put the results after the end of the list. Apr 22, 2015 at 15:37
  • You can hide columns if you don't want them shown on the screen: right-click on the gray column letter near the top of the spreadsheet and choose "Hide". No screen real estate needs to be "sacrificed". And just put in cells below the end of the list =SUM(C1:C25000) (or whatever amount of rows you have) for the words and =SUM(D1:D25000) for the characters. Or even ="Word total: " & SUM(C1:C25000) & ", Character total: " & SUM(D1:D25000) if you wanted it all in one cell.
    – Lyrl
    Apr 22, 2015 at 20:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .