Ask Your Question
0

Counting occurences of text in column

asked 2016-09-20 22:24:18 +0100

bobthewatercat gravatar image

updated 2016-09-21 12:24:23 +0100

Lupp gravatar image

I am stumped with wild cards.

I have col F which has text entires.

Example Col F: NIGHT COLD NIGHT NIGHT WIND Day Day light In NIGHT TIME

My end goal is to count the entries in col F that contain the word NIGHT, but the wildcards are not working for me. The above example would be 4.

The following formula works for entries of just NIGHT alone =COUNTIF(F1:F100000,"NIGHT") But I would like to count those cells that may have NIGHT in the middle of text, beginning or at the end.

I tried =COUNTIF(F1:F100000,"*.NIGHT.*") <== NOTE there should be an asterisk before and after periods in the string although they are not showing up in the post. (Edited for readability: The asterisks are present now. Use the code tool.) (Editing again: See also the comment by the OQ below.) Returns nothing in the box.

Office Libre Ver. 5.0.6.3

I am sure this is something simple but after 4 hours of messing and searching for an answer, I give up.

edit retag flag offensive close merge delete

Comments

The regular expression you need should be .*NIGHT.*. You may also change the settings to 'Enable wildcards in formulae'. In this case your search expression should be *NIGHT*. (This variant is by far less powerful.)

Lupp gravatar imageLupp ( 2016-09-21 00:32:44 +0100 )edit

Thank you Lupp. By checking the box [Enable regular expressions in formulas] found in Preferences>LibreOffice Calc>Calculate.

Everything I was trying to do became possible!!!

Thank you floris v for the help with syntax. But even when the syntax was entered correctly it didn't work because the above mentioned check box.

bobthewatercat gravatar imagebobthewatercat ( 2016-09-21 01:15:36 +0100 )edit

It doesn't matter now. But the edit for readability is incorrect. "*.NIGHT.*" is what I meant.

bobthewatercat gravatar imagebobthewatercat ( 2016-09-21 01:19:11 +0100 )edit

@bobthewatercat: I relied on your explanation "...there should be an asterisk before and after periods...".
Nonetheless I will edit the question regarding the new information.

Lupp gravatar imageLupp ( 2016-09-21 12:22:38 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2016-09-21 00:07:40 +0100

floris v gravatar image

There should only be an asterisk following the period, not before. An asterisk at the start of the search expression wouldn't even be correct - it requires a character or an expression to its left.

If this answer helped you, please vote it with ✔ (here on the left). That will help other people with the same question.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-09-20 22:24:18 +0100

Seen: 640 times

Last updated: Sep 21 '16