How to Find a word in a cell formula ?

General discussion on OpenOffice Calc and its spreadsheets.

How to Find a word in a cell formula ?

Postby MikeLON » Mon Jul 07, 2014 9:23 am

Hi,

I'm new to OO.Calc (as in total noob). Hope someone could help me with my problem. (also, I'm not fluent in english =/ sorry bout that)
I need a formula on how to find a specific word in a sentence inside a cell, and would then add a +1 in a specific cell somewhere in the sheet.
Here is my formula: COUNTIF(A1:A5;".*TEST.*")
My formula can add a count (not sure if "count" is the proper word to use) for samples shown in cell A1 to A4 but for some reason, I can't seem to get it right with the one shown in cell A5 (shown below):

A1:
AA BB TEST.

A2:
AA - [BB] TEST.

A3:
TEST AA.

A4:
- [01/01/14 01:01AM] AA TEST.

A5:
- [01/01/14 01:01AM]
AA.

- [01/01/14 01:01AM]
BB.

- [01/01/14 01:01AM]
TEST.

I used ctrl+enter in Cell A5. It seems that my formula works only with single-line sentences.

Thanks in advance
MikeLON
 
Posts: 32
Joined: Tue Mar 25, 2014 6:18 am

Re: How to Find a word in a cell formula ?

Postby UniSET » Mon Jul 07, 2014 11:45 am

Hi,

Yes, that's correct. Pattern matching (wildcards) doesn't match across paragraph boundaries. When you use Ctrl+Enter in a cell,
you're entering a paragraph break: TEST occurs in the 8th paragraph.
You can use FIND("TEST";A5) to find text in a cell. That works across paragraphs but I don't know how to use it with COUNTIF.

I'd use something like =SUMPRODUCT(ISNUMBER(FIND("TEST";A1:A5)))

Hope this will solve your problem
Thanks,
UniSET
 
Posts: 22
Joined: Tue Mar 25, 2014 6:33 am


Return to OpenOffice.org Calc

Who is online

Users browsing this forum: No registered users and 0 guests

cron