In bug 114418, a link to Number format codes [1] was added to Format function help page [2]. The next step should be to clarify the textual description of the 'format' argument of the function, specifically: 1. Remove the incomplete information from [2] that duplicates part of [1], and instead, put another reference to [1], with a clarification that only format string elements for en-US are accepted in Basic format string; 2. Extend the "Predefined format" section, mentioning all the accepted special Basic-only predefined formats, namely: * "<" (lowercase) * ">" (uppercase) * "c" (same as "General Date") * "n" (minute (1-2-digit)) * "nn" (minute (2-digit)) * "w" (weekday) * "y" (day of year) * "General Date" (short date format, optionally with "H:MM:SS AM/PM") * "General Number" (0.############) * "Currency" (depends on current locale settings) * "Fixed" (0.00) * "Standard" (@0.00) * "Percent" (0.00%) * "Scientific" (#.00E+00) * "Yes/No" (Yes or no, localized) * "True/False" (True or False, localized) * "On/Off" (On or Off, localized) [1] https://help.libreoffice.org/7.2/en-US/text/shared/01/05020301.html?&DbPAR=BASIC [2] https://help.libreoffice.org/7.2/en-US/text/sbasic/shared/03120301.html?DbPAR=BASIC
There are more special format strings in Basic: * "Long Date" ("System long date format" (in fact, depends on LO locale)) * "Medium Date" ("DD-MMM-YY") * "Short Date" ("System short date format" (in fact, depends on LO locale)) * "Long Time" ("H:MM:SS AM/PM") * "Medium Time" (HH:MM AM/PM) * "Short Time" (HH:MM) * "ddddd" (same as "Short Date") * "dddddd" (same as "Long Date") * "ttttt" (H:MM:SS AM/PM) (see getFormatInfo() and pFormatInfoTable)
OMG, how convoluted is it all. There is a special processing in Basic for text input. * "!" in the first position of format string means "take first character only (and ignore the rest of both input string, and of format string)": > Format("FooBar", "!!abc") => "F" (everything after initial "1" is discarded) * "\" in the first position of format string means "starting from this, start outputting character by character from the input string, until another "\" is found, then break": > Format("FooBar", "\.!?\abc") => "FooBa" (5 character output: one for each "\", > ".", "!", "?", and "\"). * Everything else in the format string just means "output the input string verbatim". This effectively disables normal format string handling for strings that can't convert to text - so e.g. this doesn't produce an expected result: > Format("BAZ", "foo@bar") => "BAZ", not expected "fooBAZbar", nor > "fooBbazAR", as VBA gives IIUC, VBA doesn't have such a strange thing. Where does this come from? Even https://wiki.openoffice.org/w/images/c/c1/BasicGuide_OOo3.2.0.pdf doesn't mention those strange things. Should we just drop that special processing? See printfmtstr in basic/source/sbx/sbxscan.cxx
The printfmtstr extra codes for strings may return unexpected values if not carefully coded in Basic msgbox Format("FooBar", "!!abc") '=> "F" (everything after initial "1" is discarded) msgbox Format("123.456", "!") '=> return ! msgbox Format("123.456", "!!abc") '=> return !!abc msgbox Format("123.abc456", "!!abc") '=> return 1 So, if the string contents is a valid number, the '!' format string return the format string itself. otherwise, return the first character of the input string. Also the "&" format code seems to return the format string itself. msgbox Format("ADCDEFGHIJKLMNOPQRSTUVWXYZ1234567890", "&abc") msgbox Format(123.456, "&abc") msgbox Format("123.456", "&abc") all return &abc.
(In reply to Olivier Hallot from comment #3) > Also the "&" format code seems to return the format string itself. > msgbox Format("ADCDEFGHIJKLMNOPQRSTUVWXYZ1234567890", "&abc") > msgbox Format(123.456, "&abc") > msgbox Format("123.456", "&abc") > > all return &abc. No, the first one returns "ADCDEFGHIJKLMNOPQRSTUVWXYZ1234567890" - the handling of "&" in printfmtstr is the same as treatment of everything else ("default" clause in the switch), with the only difference of also modifying position of the pFmt, which is only used for return value of the function ("the number of characters used from the format"), which is in fact unused in the only place where the function is called - hence I didn't mention the "&" in comment 2. When the string may be converted to number, it is handled as a number, using different rules. In general, I suppose we need to drop this "printfmtstr" idiocy, as I suggested in tdf#143193.