OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Counting the number of rows based on a text recurrence.

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
fruitjam
Power User
Power User


Joined: 21 Nov 2008
Posts: 97
Location: Uttarpara, West Bengal, India

PostPosted: Fri Nov 21, 2008 5:48 am    Post subject: Counting the number of rows based on a text recurrence. Reply with quote

Dear All,

I have a problem. I have the following sample data in a column

Mercedes Benz E Class
Mercedes Benz F Class
BMW 5 Series
Mercedes Benz SUV
Volvo
BMW 7 Series
Audi

I wish to count only those rows which have the word "Benz" embedded in them.

How do I do that?

Your help will be much appreciated.

Thanks in advance.
_________________
Yours respectfully,
Fruitjam
Back to top
View user's profile Send private message Yahoo Messenger
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Nov 21, 2008 6:35 am    Post subject: Reply with quote

=COUNTIF($A$1:$A$999;X1)
where X1 has the search expression, such as "Benz"
If no cigar, check menu:Tools>Options...Calc>Calculation:
[X] Regular expressions
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Fri Nov 21, 2008 10:24 am    Post subject: Reply with quote

Villeroy wrote:
=COUNTIF($A$1:$A$999;X1)
where X1 has the search expression, such as "Benz"
If no cigar, check menu:Tools>Options...Calc>Calculation:
[X] Regular expressions


Still no Cigar. I don't know the solution, but he's looking for a string within each cell. Should that not use the SEARCH() or FIND() function? I'm not too familiar with it, having had no need. Perhaps one of those with a helper column?

David.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Nov 21, 2008 11:09 am    Post subject: Reply with quote

Embarassed
Quote:
If no cigar, check menu:Tools>Options...Calc>Calculation:
[X] Regular expressions

I should have written "Play around with the obscure options in Tools>Options...Calc>Calculation"
Turn them all off, particulary "= and <> match whole cell".
With regular expressions you could set the criterion to:
.*Benz.* [contains Benz]
Benz.* [starts with Benz]
.*Benz [ends with Benz]
(Benz){2,3} [BenzBenz or BenzBenzBenz]
...
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
fruitjam
Power User
Power User


Joined: 21 Nov 2008
Posts: 97
Location: Uttarpara, West Bengal, India

PostPosted: Fri Nov 21, 2008 8:27 pm    Post subject: Reply with quote

Villeroy wrote:
Embarassed
Quote:
If no cigar, check menu:Tools>Options...Calc>Calculation:
[X] Regular expressions

I should have written "Play around with the obscure options in Tools>Options...Calc>Calculation"
Turn them all off, particulary "= and <> match whole cell".
With regular expressions you could set the criterion to:
.*Benz.* [contains Benz]
Benz.* [starts with Benz]
.*Benz [ends with Benz]
(Benz){2,3} [BenzBenz or BenzBenzBenz]
...


Smile

Works wonders! Thanks a ton!

However, there happens to be a slight problem. How should I reconstruct the formula such that only whole words of "Benz" is searched for in the contents of each row.

At present, even words like "Benzoil" is considered in the count.

Many thanks in advance.
_________________
Yours respectfully,
Fruitjam
Back to top
View user's profile Send private message Yahoo Messenger
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group