What are .NumberFormat Options In Excel VBA?
Note this was done on Excel for Mac 2011 but should be same for Windows
Macro:
Sub numberformats() Dim rng As Range Set rng = Range("A24:A35") For Each c In rng Debug.Print c.NumberFormat Next cEnd Sub
Result:
General GeneralNumber 0Currency $#,##0.00;[Red]$#,##0.00Accounting _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)Date m/d/yyTime [$-F400]h:mm:ss am/pmPercentage 0.00%Fraction # ?/?Scientific 0.00E+00Text @Special ;;Custom #,##0_);[Red](#,##0)
(I just picked a random entry for custom)
Thanks to this question (and answers), I discovered an easy way to get at the exact NumberFormat string for virtually any format that Excel has to offer.
How to Obtain the NumberFormat String for Any Excel Number Format
Step 1: In the user interface, set a cell to the NumberFormat you want to use.
In my example, I selected the Chinese (PRC) Currency from the options contained in the "Account Numbers Format" combo box.
Step 2: Expand the Number Format dropdown and select "More Number Formats...".
Step 3: In the Number tab, in Category, click "Custom".
The "Sample" section shows the Chinese (PRC) currency formatting that I applied.
The "Type" input box contains the NumberFormat string that you can use programmatically.
So, in this example, the NumberFormat of my Chinese (PRC) Currency cell is as follows:
_ [$¥-804]* #,##0.00_ ;_ [$¥-804]* -#,##0.00_ ;_ [$¥-804]* "-"??_ ;_ @_
If you do these steps for each NumberFormat that you desire, then the world is yours.
I hope this helps.
dovers gives us his great answer and based on it you can try use it like
public static class CellDataFormat{ public static string General { get { return "General"; } } public static string Number { get { return "0"; } } // Your custom format public static string NumberDotTwoDigits { get { return "0.00"; } } public static string Currency { get { return "$#,##0.00;[Red]$#,##0.00"; } } public static string Accounting { get { return "_($* #,##0.00_);_($* (#,##0.00);_($* \" - \"??_);_(@_)"; } } public static string Date { get { return "m/d/yy"; } } public static string Time { get { return "[$-F400] h:mm:ss am/pm"; } } public static string Percentage { get { return "0.00%"; } } public static string Fraction { get { return "# ?/?"; } } public static string Scientific { get { return "0.00E+00"; } } public static string Text { get { return "@"; } } public static string Special { get { return ";;"; } } public static string Custom { get { return "#,##0_);[Red](#,##0)"; } }}