| View previous topic :: View next topic |
| Author |
Message |
fruitjam
Power User
Joined: 21 Nov 2008
Posts: 97
Location: Uttarpara, West Bengal, India
|
 Posted: Fri Nov 21, 2008 5:48 am Post subject: Counting the number of rows based on a text recurrence. |
 |
|
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 |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Nov 21, 2008 6:35 am Post subject: |
|
|
=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 http://forum.openoffice.org |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Fri Nov 21, 2008 10:24 am Post subject: |
|
|
| 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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Nov 21, 2008 11:09 am Post subject: |
|
|
| 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 http://forum.openoffice.org |
|
| Back to top |
|
|
fruitjam
Power User
Joined: 21 Nov 2008
Posts: 97
Location: Uttarpara, West Bengal, India
|
| Posted: Fri Nov 21, 2008 8:27 pm Post subject: |
|
|
| Villeroy wrote: |
| 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]
... |
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 |
|
|
|
