libreoffice-calc
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.
It's a matter of formatting the cells. You will have to apply the format code [HH]:MM:SS. It's already predefined:
(Screenshot from LibreOffice 3.4.3)
What works for me in LibreOffice:
=SUMPRODUCT(($Total.D$1:D$1048576="Bug")*($Total.E$1:E$1048576="Basse"))
I don't know about Excel, but I found claims on the net that the "*" is forcing Excel to interpret the evaluation of the condition as number.
You might have to tell LibreOffice to accept the english names for the formulas:
Extras -> Options -> LibreOffice Calc -> Formula -> Use english function names
Best Answer
Are you sure you need a macro? One potential non-macro setup:
=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=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.=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.