Philip Goodfellow wrote:
I have MS Excel spreadsheets that fequently use COUNTIF (range,"A~*") to
count the how often the entry "A*" occurs.
The ~ is required as an escape character since, in Excel * otherwise
maps to any characters. (This must be a common requirement in UK
education since in UK exams A* is used as a grade.)
When imported into OpenOffice calc no conversion is made so the count
fails to find the A* values. (It is now looking for "A~*" values since
the ~ as escape is no longer required).
Clearly I could do a search/replace to change the formulae manually but
would rather not since, if resaved as .xls the =COUNTIF(H8:AG8;"A*")
now reports grade A as A* in error. (so no conversion is made in the
reverse direction either).
Is this the sort of thing I should report as a bug/request for
enhancement? If so, how should I go about it?
Philip Goodfellow
OS : Windows XP Pro SP2
Open Office 2.0.3
Hi, Philip
OpenOffice.org does not support pattern matching by wild cards. It can
use more powerful regular expressions (aka "regexes"). There is an
option Menu:Tools>Calc>Calculation "Enable regular expressions in
formulae", which saved individually for each Calc-document, but it is
turned off whenever you open an xls. So this is not related to your
problem since you want to avoid any pattern matching.
Well, there is a solution for your particular requirement.
You need COUNTIF (range,"A~*") in Excel and COUNTIF (range,"A*") when
the same xls-file is openened with Calc.
Call the named references dialog: Ctrl+F3
Name: shSep
Assigned To: MID(ADDRESS(1;1;1;"Foo");4;1)
Button "Add"
Name: xlEsc
Assigned To: IF(shSep="!";"~";"")
Button "Add"
Button "OK"
shSep calculates the different sheet-separators "!" and ".".
ADDRESS(1;1;1;"Foo") --> Foo!$A$1 in Excel and Foo.$A$1 in Calc.
The MID function extracts the 4th char "!" or "."
xlEsc calculates a "~" if the file is opened in Excel.
Test with two formulae in sheet cells:
=shSep --> "!" in Excel, "." (dot) in Calc
=xlEsc --> "~" in Excel, "" (empty string) in Calc
Change your formulae to COUNTIF (range,"A"& xlEsc &"*")
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
