Topics   All   MacOS (Only)   Windows (Only)   Linux (Only, Not)   iOS (Only, Not)  
Components   Crossplatform Mac & Win   Server   Client   Old   Deprecated   Guides   Examples   Videos
New in version: 11.3   11.4   11.5   12.0   12.1   12.2   12.3   12.4   12.5   13.0    Statistic    FMM    Blog  


Sets the number format identifier.

Component Version macOS Windows Linux Server iOS SDK
XL 3.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes ✅ Yes
MBS( "XL.Format.SetNumFormat"; Book; Format; Value )   More


Parameter Description Example
Book The reference to the workbook. Please use XL.LoadBook to load a file. $ref
Format The index of the format in the book to target. 0
Value The new setting. $value


Returns OK or error.


Sets the number format identifier.
The identifier must be a valid built-in number format identifier or the identifier of a custom number format. To create a custom format use XL.Book.AddCustomNumFormat.
See built-in values in the table on this page:

NUMFORMAT_GENERAL0general format
NUMFORMAT_NUMBER1general number1000
NUMFORMAT_NUMBER_D22number with decimal point1000.00
NUMFORMAT_NUMBER_SEP3number with thousands separator100,000
NUMFORMAT_NUMBER_SEP_D24number with decimal point and thousands separator100,000.00
NUMFORMAT_CURRENCY_NEGBRA5monetary value, negative in brackets(1000$)
NUMFORMAT_CURRENCY_NEGBRARED6monetary value, negative is red in brackets(1000$)
NUMFORMAT_CURRENCY_D2_NEGBRA7monetary value with decimal point, negative in brackets($1000.00)
NUMFORMAT_CURRENCY_D2_NEGBRARED8monetary value with decimal point, negative is red in brackets($1000.00)
NUMFORMAT_PERCENT9percent value, multiply the cell value by 10075%
NUMFORMAT_PERCENT_D210percent value with decimal point, multiply the cell value by 10075.00%
NUMFORMAT_SCIENTIFIC_D211scientific value with E character and decimal point10.00E+1
NUMFORMAT_FRACTION_ONEDIG12fraction value, one digit10 1/2
NUMFORMAT_FRACTION_TWODIG13fraction value, two digits10 23/95
NUMFORMAT_DATE14date value, depends on OS settings3/11/2009
NUMFORMAT_CUSTOM_D_MON_YY15custom date value11-Mar-09
NUMFORMAT_CUSTOM_D_MON16custom date value11-Mar
NUMFORMAT_CUSTOM_MON_YY17custom date valueMar-09
NUMFORMAT_CUSTOM_HMM_AM18custom date value8:30 AM
NUMFORMAT_CUSTOM_HMMSS_AM19custom date value8:30:00 AM
NUMFORMAT_CUSTOM_HMM20custom date value8:30
NUMFORMAT_CUSTOM_HMMSS21custom date value8:30:00
NUMFORMAT_CUSTOM_MDYYYY_HMM22custom datetime value3/11/2009 8:30
NUMFORMAT_NUMBER_SEP_NEGBRA37number with thousands separator, negative in brackets(4,000)
NUMFORMAT_NUMBER_SEP_NEGBRARED38number with thousands separator, negative is red in brackets(4,000)
NUMFORMAT_NUMBER_D2_SEP_NEGBRA39number with thousands separator and decimal point, negative in brackets(4,000.00)
NUMFORMAT_NUMBER_D2_SEP_NEGBRARED40number with thousands separator and decimal point, negative is red in brackets(4,000.00)
NUMFORMAT_ACCOUNT41account value5,000
NUMFORMAT_ACCOUNTCUR42account value with currency symbol$5,000
NUMFORMAT_ACCOUNT_D243account value with decimal point5,000.00
NUMFORMAT_ACCOUNT_D2_CUR44account value with currency symbol and decimal point$5,000.00
NUMFORMAT_CUSTOM_MMSS45custom time value30:55
NUMFORMAT_CUSTOM_H0MMSS46custom time value20:30:55
NUMFORMAT_CUSTOM_MMSS047custom time value30:55.0
NUMFORMAT_CUSTOM_000P0E_PLUS048custom value15.2E+3
NUMFORMAT_TEXT49text valueany text


Use a number format:

# create new format
Set Variable [$format; Value:MBS( "XL.Book.AddFormat"; $book)]
# use existing number format
Set Variable [$r; Value:MBS( "XL.Format.SetNumFormat"; $book; $format; 7)]
# and use format
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteNumber"; $book; $sheet; 5; 3; 123; $format)]

Apply a new format to a column:

#create new format
Set Variable [$format; Value:MBS( "XL.Book.AddFormat"; $BookRef )]
Set Variable [$r; Value:MBS( "XL.Format.SetNumFormat"; $BookRef; $format; 7)]
#set as format for column
Set Variable [$r; Value:MBS( "XL.Sheet.SetColumn"; $BookRef; 0; 6; 6; 50 ; $format; 0 )]
#loop over all rows
Set Variable [$lastRow; Value:MBS( "XL.Sheet.GetLastRow"; $BookRef; $sheet )]
Set Variable [$row; Value:1]
    Set Variable [$r; Value:MBS( "XL.Sheet.SetCellFormat"; $BookRef; $sheet; $row; 6; $format )]
    Set Variable [$row; Value:$row + 1]
    Exit Loop If [$row > $lastRow]
End Loop

See also

Release notes

  • Version 12.5

Example Databases

Blog Entries

Created 18th August 2014, last changed 19th March 2018

XL.Format.SetLocked - XL.Format.SetPatternBackgroundColor

Feedback: Report problem or ask question.

Start Chat