Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

According to the offical help, COUNTA counts "text entries ..., even when they contain an empty string of length 0". So it is behaving "correctly".

Depending on the nature of your data, you could try this:

=COUNTIF(A1:A2, "[:alnum:]")

which returns "0" for your example, since COUNTIF can use regular expressions. I don't know if that example is bullet-proof, though. (And you might need to enable Regular Expressions in Formula: go to Options > LibreOffice Calc > Calculate for the check-box.)

According to the offical help, COUNTA counts "text entries ..., even when they contain an empty string of length 0". So it is behaving "correctly".

Depending on the nature of your data, you could try this:

=COUNTIF(A1:A2, "[:alnum:]")

which returns "0" for your example, since COUNTIF can use regular expressions. I don't know if that example is bullet-proof, though. (And you might need to enable Regular Expressions in Formula: go to Options > LibreOffice Calc > Calculate for the check-box.)

You could also see if there is inspiration in this StackOverflow Q&A about the same situation in Google Docs spreadsheet, with a different solution offered. Don't know if that will work in your case, but it might be worth a look.

click to hide/show revision 3
No.3 Revision

According to the offical official help, COUNTA counts "text entries ..., even when they contain an empty string of length 0". So it is behaving "correctly".

Depending on the nature of your data, you could try this:

=COUNTIF(A1:A2, "[:alnum:]")

which returns "0" for your example, since COUNTIF can use regular expressions. I don't know if that example is bullet-proof, though. (And you might need to enable Regular Expressions in Formula: go to Options > LibreOffice Calc > Calculate for the check-box.)

You could also see if there is inspiration in this StackOverflow Q&A about the same situation in Google Docs spreadsheet, with a different solution offered. Don't know if that will work in your case, but it might be worth a look.

According to the official help, COUNTA counts "text entries ..., even when they contain an empty string of length 0". So it is behaving "correctly".

Depending on the nature of your data, you could try this:

=COUNTIF(A1:A2, "[:alnum:]")
"[a-z0-9\.]+")

which returns "0" for your example, since COUNTIF can use regular expressions. I don't know if that example is bullet-proof, though. (And you might need to enable Regular Expressions in Formula: go to Options > LibreOffice Calc > Calculate for the check-box.)

check-box.) "" Explanation of regex

[ ... ]  square brackets group alternates
a-z0-9   any alphanumeric characters
\.       can include a literal dot (escaped)
+        repeated any number of times

So if these rows represented A1:A5

""

Abc
3.1412
x

That COUNTIF returns a value of 3.


You could also see if there is inspiration in this StackOverflow Q&A about the same situation in Google Docs spreadsheet, with a different solution offered. Don't know if that will work in your case, but it might be worth a look.

(Update: regex is tricksy!)

According to the official help, COUNTA counts "text entries ..., even when they contain an empty string of length 0". So it is behaving "correctly".

Depending on the nature of your data, you could try this:

=COUNTIF(A1:A2, "[a-z0-9\.]+")

which returns "0" for your example, since COUNTIF can use regular expressions. I don't know if that example is bullet-proof, though. (And you might need to enable Regular Expressions in Formula: go to Options > LibreOffice Calc > Calculate for the check-box.) "" check-box.)

Explanation of regex

[ ... ]  square brackets group alternates
a-z0-9   any alphanumeric characters
\.       can include a literal dot (escaped)
+        repeated any number of times

So if these rows represented A1:A5

""

Abc
3.1412
x

That COUNTIF returns a value of 3.


You could also see if there is inspiration in this StackOverflow Q&A about the same situation in Google Docs spreadsheet, with a different solution offered. Don't know if that will work in your case, but it might be worth a look.

(Update: regex is tricksy!)

According to the official help, COUNTA counts "text entries ..., even when they contain an empty string of length 0". So it is behaving "correctly".

Depending on the nature of your data, you could try this:

=COUNTIF(A1:A2, "[a-z0-9\.]+")

which returns "0" for your example, since COUNTIF can use regular expressions. I don't know if that example is bullet-proof, though. (And you might need to enable Regular Expressions in Formula: go to Options > LibreOffice Calc > Calculate for the check-box.)

Explanation of regex

[ ... ]   square brackets group alternates
 a-z0-9   any alphanumeric characters
 \.       can include a literal dot (escaped)
+         repeated any number of times

So if these rows represented A1:A5

""

Abc
3.1412
x

That COUNTIF returns a value of 3.


You could also see if there is inspiration in this StackOverflow Q&A about the same situation in Google Docs spreadsheet, with a different solution offered. Don't know if that will work in your case, but it might be worth a look.

(Update: regex is tricksy!)