Sub Format01() Dim Str As String Dim i As Long For i = 2 To 11 '2行目から11行目まで繰り返す。 Str = Range("B" & i).NumberFormatLocal 'B列の表示設定(結果)をStrへ代入します。 Range("C" & i) = Str 'Strへ代入した表示設定をC列に表示します。 Next i End Sub
書式の設定(表示形式) | 入力値 | 実際の表示 |
---|---|---|
####.# | 1234.567 | 1234.6 |
#.000 | 1234.567 | 1,235 |
#,000.### | 1234.567 | 1234.56 |
#,000.000 | 1234.567 | 1234.56 |
???.??? | 1234.567 | 1234.560 |
#,### | 1234.567 | 1,235 |
#, | 123456789 | 123,457 |
#,###.0, | 123456789 | 123,456.8 |
#.0,, | 123456789 | 123.5 |
0_);[赤](0) | -1234567 | (1234567) |
0_);(0) | -1234567 | (1234566) |
0_ ;[赤]-0 | -1234567 | -1234565 |
0;"▲ "0 | -1234567 | ▲1234564 |
Sub Format02() '数値 Range("C2").NumberFormatLocal = "####.#" Range("C3").NumberFormatLocal = "#,000" Range("C4").NumberFormatLocal = "#,000.###" Range("C5").NumberFormatLocal = "#,000.000" Range("C6").NumberFormatLocal = "???.???" Range("C7").NumberFormatLocal = "#,###" Range("C8").NumberFormatLocal = "#," Range("C9").NumberFormatLocal = "#,###.0," Range("C10").NumberFormatLocal = "#.0,," Range("C11").NumberFormatLocal = "0_);[赤](0)" Range("C12").NumberFormatLocal = "0_);(0)" Range("C13").NumberFormatLocal = "0_ ;[赤]-0" Range("C14").NumberFormatLocal = "0;""▲ ""0" End Sub
書式の設定(表示形式) | 入力値 | 実際の表示 |
---|---|---|
#,##0 | 1234567 | 1,234,567 |
#,##0_);[赤](#,##0) | -1234567 | (1,234,567) |
#,##0"$" | 1234567 | 1,234,567$ |
#,##0;[赤]-#,##0 | -1234567 | -1,234,567 |
¥#,##0;[赤]¥-#,##0 | 1234567 | ¥1,234,567 |
¥#,##0;[青]¥-#,##0 | -1234567 | ¥-1,234,567 |
#,##0.00;[赤]-#,##0.00 | 12345.67 | 12,345.67 |
#,##0"円";[赤]-#,##0"円" | -1234567 | -1,234,567円 |
#,##0,"千円";[赤]-#,##0,"千円" | 1234567 | 1,235千円 |
Sub Format03() '通貨 Range("C2").NumberFormatLocal = "####.#" Range("C3").NumberFormatLocal = "#,000" Range("C4").NumberFormatLocal = "#,000.###" Range("C5").NumberFormatLocal = "#,000.000" Range("C6").NumberFormatLocal = "???.???" Range("C7").NumberFormatLocal = "#,###" Range("C8").NumberFormatLocal = "#," Range("C9").NumberFormatLocal = "#,###.0," Range("C10").NumberFormatLocal = "#.0,," End Sub
書式の設定(表示形式) | 入力値 | 実際の表示 |
---|---|---|
yyyy/m/d | 2020/1/1 | 2020/1/1 |
[$-F800]dddd, mmmm dd, yyyy | 2020/1/1 | 2020年1月1日 |
yyyy"年"m"月";@ | 2020/1/1 | 2020年1月 |
m"月"d"日";@ | 2020/1/1 | 1月1日 |
[$-409]yyyy/m/d h:mm AM/PM;@ | 2020/1/1 | 2020/1/1 12:00 AM |
yyyy/m/d h:mm;@ | 2020/1/1 | 2020/1/1 0:00 |
[$-409]d-mmm;@ | 2020/1/1 | 1-Jan |
[$-409]mmmm-yy;@ | 2020/1/1 | January-20 |
[$-409]mmmmm;@ | 2020/1/1 | J |
[$-409]mmmmm-yy;@ | 2020/1/1 | J-20 |
[$-411]ge.m.d | 2020/1/1 | R2.1.1 |
[$-411]ggge"年"m"月"d"日" | 2020/1/1 | 令和2年1月1日 |
Sub Format04() '日付 Range("C2").NumberFormatLocal = "yyyy/m/d" Range("C3").NumberFormatLocal = "[$-F800]dddd, mmmm dd, yyyy" Range("C4").NumberFormatLocal = "yyyy""年""m""月"";@" Range("C5").NumberFormatLocal = "m""月""d""日"";@" Range("C6").NumberFormatLocal = "[$-409]yyyy/m/d h:mm AM/PM;@" Range("C7").NumberFormatLocal = "yyyy/m/d h:mm;@" Range("C8").NumberFormatLocal = "[$-409]d-mmm;@" Range("C9").NumberFormatLocal = "[$-409]mmmm-yy;@" Range("C10").NumberFormatLocal = "[$-409]mmmmm;@" Range("C11").NumberFormatLocal = "[$-409]mmmmm-yy;@" Range("C12").NumberFormatLocal = "[$-411]ge.m.d" Range("C13").NumberFormatLocal = "[$-411]ggge""年""m""月""d""日""" End Sub
書式の設定(表示形式) | 入力値 | 実際の表示 |
---|---|---|
[$-F400]h:mm:ss AM/PM | 12:34:50 | 12:34:50 |
[$-409]h:mm AM/PM;@ | 12:34:50 | 12:34 PM |
[$-409]h:mm:ss AM/PM;@ | 12:34:50 | 12:34:50 PM |
h"時"mm"分";@ | 12:34:50 | 12時34分 |
h"時"mm"分"ss"秒";@ | 12:34:50 | 12時34分50秒 |
Sub Format05() '時刻 Range("C2").NumberFormatLocal = "[$-F400]h:mm:ss AM/PM" Range("C3").NumberFormatLocal = "[$-409]h:mm AM/PM;@" Range("C4").NumberFormatLocal = "[$-409]h:mm:ss AM/PM;@" Range("C5").NumberFormatLocal = "h""時""mm""分"";@" Range("C6").NumberFormatLocal = "h""時""mm""分""ss""秒"";@" End Sub
書式の設定(表示形式) | 入力値 | 実際の表示 |
---|---|---|
G/標準 | あいう | あいう |
@ | ABC | ABC |
0% | 1 | 100% |
[ | 1001234 | 100-1234 |
[ | 1112345678 | (11) 1234-5678 |
[DBNum1][$-411]G/標準 | 1234 | 千二百三十四 |
[DBNum2][$-411]G/標準 | 1234 | 壱阡弐百参拾四 |
[DBNum3][$-411]0 | 1234 | 1234 |
[DBNum3][$-411]#,##0 | 1234 | 1,234 |
Sub Format06() '標準・その他 Range("C2").NumberFormatLocal = "G/標準" Range("C3").NumberFormatLocal = "@" Range("C4").NumberFormatLocal = "0%" Range("C5").NumberFormatLocal = "[<=999]000;[<=9999]000-00;000-0000" Range("C6").NumberFormatLocal = "[<=99999999]####-####;(00) ####-####" Range("C7").NumberFormatLocal = "[DBNum1][$-411]G/標準" Range("C8").NumberFormatLocal = "[DBNum2][$-411]G/標準" Range("C9").NumberFormatLocal = "[DBNum3][$-411]0" Range("C10").NumberFormatLocal = "[DBNum3][$-411]#,##0" End Sub