Counting # of cells containing an apostrophe

[Guest]

Is there a way to count the total number of cells in a worksheet that contain
an apostrophe as the first character? I've tried using COUNTIF and
SUMPRODUCT, but to no avail. Any help would be greatly appreciated.
Thanks,
Bob

 

[T. Valko]

Try this:

=SUMPRODUCT(--(LEFT(A1:A10)="'"))

After the = is a double quote ", an apostrophe ' (or single quote) then
another double quote ".

Or:

=SUMPRODUCT(--(LEFT(A1:A10)=CHAR(39)))

Biff

 

[Guest]

I have already tried your two suggestions, but with no luck.
Thanks all the same.
Bob

 

[Ron Rosenfeld]

Is there a way to count the total number of cells in a worksheet that contain
an apostrophe as the first character? I've tried using COUNTIF and
SUMPRODUCT, but to no avail. Any help would be greatly appreciated.
Thanks,
Bob

I may be wrong, but I think that if the "'" is the first character in a cell,
it is treated as a prefix indicating that the cell is TEXT, rather than as
"cell contents".

So if a cell contains '123, you would only see displayed 123.

If this is what you are trying to figure out, then you need to check the cell
prefix.

The CELL worksheet function does not return an array; so if you were going to
do that, you would require a helper column with the formula:

=CELL("prefix",cell_ref)

copy drag down.

Then use COUNTIF to count the number of apostrophes in the helper column.

Or you could use VBA.

Or you could download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr


and use the formula:

=SUMPRODUCT((XLM.GET.CELL(52,rng)="'")*1)

where rng is the range of cells to check.


--ron

 

[Guest]

Ron,
Thanks! I prefer to use a function that returns an array, so I will
download Longre's free morefunc.xll add-in and try it out. Thanks again for
your help.
Bob

 

[Ron Rosenfeld]

Ron,
Thanks! I prefer to use a function that returns an array, so I will
download Longre's free morefunc.xll add-in and try it out. Thanks again for
your help.
Bob

You're welcome. Let me know if it does what you want.
--ron

 

[Guest]

Ron,
The morefunc.xll add-in works great! Using the XLM.GET.CELL function tells
me that I have 100+ instances of an apostrophe in my worksheet! But when I
try to go find them using Excel's Find command, I can't find any. As the
search criteria, I used "'" (double quotes, apostrophe, double quotes). Can
you advise me as to what I'm doing wrong?
Thanks again.
Bob

 

[Ron Rosenfeld]

Ron,
The morefunc.xll add-in works great! Using the XLM.GET.CELL function tells
me that I have 100+ instances of an apostrophe in my worksheet! But when I
try to go find them using Excel's Find command, I can't find any. As the
search criteria, I used "'" (double quotes, apostrophe, double quotes). Can
you advise me as to what I'm doing wrong?

The FIND command is designed to locate text strings. As I wrote previously,
the "'" is a prefix indicating that the cell is to be treated as TEXT. Not the
same thing as a text string within a cell.

I'm not sure what you're trying to do. If you want to locate these cells and
do something to them, you could use a VBA macro.

e.g.:

======================
Sub FindApos()
Dim c As Range
Dim RngToCheck As Range

Set RngToCheck = [a1:c20]

For Each c In RngToCheck
If c.PrefixCharacter = "'" Then
c.Interior.Color = vbRed
'or do something else here
End If
Next c
End Sub
======================
--ron

 

[Ron Rosenfeld]

Ron,
Sorry for the confusion on my part. Thanks for the macro! I sincerely
appreciate it.
Bob

You're very welcome. Hopefully the macro will help accomplish what it is you
require.

Best wishes,
--ron