XL.Format.SetNumFormat
----------------------

Sets the number format identifier.

| Component | Version | macOS | Windows | Linux | Server | iOS SDK |
|---|---|---|---|---|---|---|
| [XL](component_XL.md) | [3.5](newinversion35.md) | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |

MBS( "XL.Format.SetNumFormat"; Book; Format; Value ) 

**MBS**( **"XL.Format.SetNumFormat";** /\* Sets the number format identifier. \*/  
**$Book**; /\* The reference number for the workbook.e.g. $ref \*/   
**$Format**; /\* The index of the format in the book to target.e.g. 0 \*/   
**$Value**) /\* The number format to use.e.g. 1 \*/ 

### Parameters

| Parameter | Description | Example |
|---|---|---|
| Book | The reference number for the workbook. | $ref |
| Format | The index of the format in the book to target. | 0 |
| Value | The number format to use. | 1 |

### Result

Returns OK or error.

### Description

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](XLBookAddCustomNumFormat.md).  
See built-in values in the table on this page:  
<http://www.libxl.com/format.html>  
  
| Constant | Value | Description | Example |
|---|---|---|---|
| NUMFORMAT\_GENERAL | 0 | general format |  |
| NUMFORMAT\_NUMBER | 1 | general number | 1000 |
| NUMFORMAT\_NUMBER\_D2 | 2 | number with decimal point | 1000.00 |
| NUMFORMAT\_NUMBER\_SEP | 3 | number with thousands separator | 100,000 |
| NUMFORMAT\_NUMBER\_SEP\_D2 | 4 | number with decimal point and thousands separator | 100,000.00 |
| NUMFORMAT\_CURRENCY\_NEGBRA | 5 | monetary value, negative in brackets | (1000$) |
| NUMFORMAT\_CURRENCY\_NEGBRARED | 6 | monetary value, negative is red in brackets | (1000$) |
| NUMFORMAT\_CURRENCY\_D2\_NEGBRA | 7 | monetary value with decimal point, negative in brackets | ($1000.00) |
| NUMFORMAT\_CURRENCY\_D2\_NEGBRARED | 8 | monetary value with decimal point, negative is red in brackets | ($1000.00) |
| NUMFORMAT\_PERCENT | 9 | percent value, multiply the cell value by 100 | 75% |
| NUMFORMAT\_PERCENT\_D2 | 10 | percent value with decimal point, multiply the cell value by 100 | 75.00% |
| NUMFORMAT\_SCIENTIFIC\_D2 | 11 | scientific value with E character and decimal point | 10.00E+1 |
| NUMFORMAT\_FRACTION\_ONEDIG | 12 | fraction value, one digit | 10 1/2 |
| NUMFORMAT\_FRACTION\_TWODIG | 13 | fraction value, two digits | 10 23/95 |
| NUMFORMAT\_DATE | 14 | date value, depends on OS settings | 3/11/2009 |
| NUMFORMAT\_CUSTOM\_D\_MON\_YY | 15 | custom date value | 11-Mar-09 |
| NUMFORMAT\_CUSTOM\_D\_MON | 16 | custom date value | 11-Mar |
| NUMFORMAT\_CUSTOM\_MON\_YY | 17 | custom date value | Mar-09 |
| NUMFORMAT\_CUSTOM\_HMM\_AM | 18 | custom date value | 8:30 AM |
| NUMFORMAT\_CUSTOM\_HMMSS\_AM | 19 | custom date value | 8:30:00 AM |
| NUMFORMAT\_CUSTOM\_HMM | 20 | custom date value | 8:30 |
| NUMFORMAT\_CUSTOM\_HMMSS | 21 | custom date value | 8:30:00 |
| NUMFORMAT\_CUSTOM\_MDYYYY\_HMM | 22 | custom datetime value | 3/11/2009 8:30 |
| NUMFORMAT\_NUMBER\_SEP\_NEGBRA | 37 | number with thousands separator, negative in brackets | (4,000) |
| NUMFORMAT\_NUMBER\_SEP\_NEGBRARED | 38 | number with thousands separator, negative is red in brackets | (4,000) |
| NUMFORMAT\_NUMBER\_D2\_SEP\_NEGBRA | 39 | number with thousands separator and decimal point, negative in brackets | (4,000.00) |
| NUMFORMAT\_NUMBER\_D2\_SEP\_NEGBRARED | 40 | number with thousands separator and decimal point, negative is red in brackets | (4,000.00) |
| NUMFORMAT\_ACCOUNT | 41 | account value | 5,000 |
| NUMFORMAT\_ACCOUNTCUR | 42 | account value with currency symbol | $5,000 |
| NUMFORMAT\_ACCOUNT\_D2 | 43 | account value with decimal point | 5,000.00 |
| NUMFORMAT\_ACCOUNT\_D2\_CUR | 44 | account value with currency symbol and decimal point | $5,000.00 |
| NUMFORMAT\_CUSTOM\_MMSS | 45 | custom time value | 30:55 |
| NUMFORMAT\_CUSTOM\_H0MMSS | 46 | custom time value | 20:30:55 |
| NUMFORMAT\_CUSTOM\_MMSS0 | 47 | custom time value | 30:55.0 |
| NUMFORMAT\_CUSTOM\_000P0E\_PLUS0 | 48 | custom value | 15.2E+3 |
| NUMFORMAT\_TEXT | 49 | text value | any text |

### Examples

Use a number format:

 # create new format   
Set Variable \[$format ; Value:MBS ( "[XL.Book.AddFormat](XLBookAddFormat.md)"; $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](XLSheetCellWriteNumber.md)"; $book ; $sheet ; 5; 3; 123; $format )\]  
Apply a new format to a column:

 #create new format   
Set Variable \[$format ; Value:MBS ( "[XL.Book.AddFormat](XLBookAddFormat.md)"; $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](XLSheetSetColumn.md)"; $BookRef ; 0; 6; 6; 50 ; $format ; 0 )\]  
\#loop over all rows   
Set Variable \[$lastRow ; Value:MBS ( "[XL.Sheet.GetLastRow](XLSheetGetLastRow.md)"; $BookRef ; $sheet )\]  
Set Variable \[$row ; Value:1\]  
Loop  
 Set Variable \[$r ; Value:MBS ( "[XL.Sheet.SetCellFormat](XLSheetSetCellFormat.md)"; $BookRef ; $sheet ; $row ; 6; $format )\]  
 #next  
 Set Variable \[$row ; Value:$row + 1\]  
 Exit Loop If \[$row &gt; $lastRow \]  
End Loop  
### See also

- [XL.Book.AddConditionalFormat](XLBookAddConditionalFormat.md)
- [XL.Book.AddCustomNumFormat](XLBookAddCustomNumFormat.md)
- [XL.Book.AddFormat](XLBookAddFormat.md)
- [XL.Date.Pack](XLDatePack.md)
- [XL.Format.GetNumFormat](XLFormatGetNumFormat.md)
- [XL.Sheet.CellWriteNumber](XLSheetCellWriteNumber.md)
- [XL.Sheet.GetLastRow](XLSheetGetLastRow.md)
- [XL.Sheet.SetCellFormat](XLSheetSetCellFormat.md)
- [XL.Sheet.SetCol](XLSheetSetCol.md)
- [XL.Sheet.SetColumn](XLSheetSetColumn.md)

### Release notes

- **Version 12.5**
    - Fixed [XL.Format.SetNumFormat](https://www.mbsplugins.eu/XLFormatSetNumFormat.shtml) and related functions to not crash if you pass an invalid format number.

### Example Databases

- [XL/Writing data](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/XL/Writing%20data.shtml#5ScriptAnchor_)
- [XL/XL Import and Export](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/XL/XL%20Import%20and%20Export.shtml#4ScriptAnchor_)

### Blog Entries

- [MBS FileMaker Plugin, version 12.5pr7](https://www.mbsplugins.de/archive/2022-11-04/MBS_FileMaker_Plugin_version_1/monkeybreadsoftware_blog_filemaker)

This function checks for a license.

Created 18th August 2014 , last changed 30th March 2024

  
[XL.Format.SetLocked](XLFormatSetLocked.md) - [XL.Format.SetPatternBackgroundColor](XLFormatSetPatternBackgroundColor.md)

[HTML Version](XLFormatSetNumFormat.shtml)