Formulas using Excel wild card or regular expression in Symphony Spreadsheet

Symphony 3.0 supports formulas with Excel wildcard with some restrictions

In Excel, some formulas can support wildcard(*, ?, ~). When open Excel file in Symphony 3.0, Symphony can calculate these formulas correctly if these formulas have wildcard. User can also edit these formulas with wildcard in Symphony. When user save the file in Symphony 3.0 and open it in Excel, all formulas with wildcard work well.

However, Symphony does not support Excel wildcard in formula if user new/edit a spreadsheet document or open/edit a ODS document. Instead of wildcard, Symphony supports regular expression for ODS file. So user should not save a Excel file with wildcard formulas as ODS file format, which may cause incorrectly calculation of formulas with wildcard. If user do it, Symphony will open a warning message box like following.


Regular expression is a more powerful tool than Excel wildcard. The meta elements for regular expression are in following table.

Character	Result/Use
.	Represents any single character. The search term "sh.rt" finds both "shirt" and "short".
^Peter	Finds the search term only if it appears at the beginning of a paragraph. Special characters (for example, blank spaces and character-anchored frames) at the beginning of a paragraph are ignored.
Peter$	Finds the search term only if it appears at the end of a paragraph. Special characters (for example, blank spaces and character-anchored frames) at the end of a paragraph are ignored.
*	Represents any number of preceding character. For example, "Ab*c" finds "Ac", "Abc", "Abbc", "Abbbc", and so on.
+	The character before this symbol must appear at least once: "AX.+4" finds "AX 4", but not "AX4" The longest possible text within the paragraph is always found. If the paragraph contains the text AX 4 AX4, the search finds the first A to the last 4.
?	Finds words that include the characters that come before the character that is in front of the "?". For example, "Texts?" finds the words "Texts" and "Text".
\	Finds the character (no numbers) that you specify. For example, if you search for \C , only 'C' is found.
\n	Finds a hard row break that was inserted with Shift+Enter. To change row breaks into paragraph breaks, enter \n in the Search for and Replace with boxes, and then click the Replace all button.
\t	Finds a tab space. You can also use this expression in the Replace with box.
\>	Finds the search term only if it appears at the end of a word. For example, "book\>" finds "checkbook", but not "bookmark".
\<	Finds the search term only if it appears at the beginning of a word. For example, "\>book" finds "bookmark", but not "checkbook".
^$	Searches for empty paragraphs.
^.	Searches for the first character of a paragraph.
&	Use this character in the Replace with box to add the characters that you enter to the search criteria when a replacement is made. For example, if you enter "window" in the Search for box and "&frame" in the Replace with box, the word "window" is replaced with "window frame". You can also enter a "&" in the Replace with box to modify the Attributes or a different Format of the search criteria you entered in the Search for box.
[abc123]	Finds the characters that are between the brackets.
[a-e]	Finds the characters that are between a and e.
[a-eh-x]	Finds the characters that are between a-e and h-x.
[^a-s]	Finds all characters except for a-s
\xXXXX	Finds a special character based on its four-digit hexadecimal code (XXXX). The code for the special character depends on the font used. You can view the codes by clicking Create > Special Character .
|	Finds the search terms that occur before and after the "|". For example, "this|that" finds both "this" and "that" when you search.
{2}	Defines the number of times that the character in front of the opening bracket occurs. For example, "tre{2}" finds "tree".
{1,2}	Defines the number of times that the character in front of the opening bracket can occur. For example, "tre{1,2}" finds both "tree" and "treated".
( )	Defines the characters inside the brackets as a reference. You can then refer to the first reference in the current expression with "\1", to the second reference with "\2", and so on. For example, if your text contains the number 13487889 and you search using the regular expression (8)7\1\1, "8788" is found.
[:digit:]?	Finds a single digit number. To find a number with more than one digit, use [:digit:]*.
[:space:]?	Finds white space such as spaces and tabs.
[:print:]?	Finds printable characters.
[:cntrl:]?	Finds nonprinting characters.
[:alnum:]?	Finds alphanumeric characters (numbers and text characters).
[:alpha:]?	Finds alphabetic characters.
[:lower:]?	Finds lowercase characters if Match case is selected in the Options area.
[:upper:]?	Finds uppercase characters if Match case is selected in the Options area.

When new a spreadsheet document or open an ODS file in Symphony, please make sure supporting regular expression in formula is enable. User can go to File > Preferences > Symphony > Lotus Symphony Spreadsheet > Calculations page to enable regular expression in formulas.

Because regular expression has different syntax and semantics with Excel wildcard. User need pay attention to the difference.