VBA basics
31. Format Cells (display format, NumberFormatLocal)

Excel macro basics and applications, introduction to Excel VBA
last updated:09-01-2019

31. Format Cells (display format, NumberFormatLocal)

Some of this topic was processed by machine translation. Original

By specifying the display format of the cell, you can make various appearances even if the value in the cell is the same value.
You can keep the cell value as it is and make it look just like the user.
Even with the same number, 12345
12345, 12,345, $12,345
You can specify different display methods.

However, the numerical value is the same, 12345, and can be treated as the same in calculations.
It just changes the way it looks on the screen.

Specifying the display format in a macro

Range.NumberFormatLocal = "Display format specification character"
Range. Becomes a Range object such as Range, Cells, Rows, Columns, etc.
NumberFormatLocal is a property of the Range object.

Range.NumberFormatLocal = "@" '文字列
Range.NumberFormatLocal = "#,###"'Suppressed with commas
Range.NumberFormatLocal = "0.00" 'Fixed to 2 decimal places
Range.NumberFormatLocal = "yyyy/mm/dd" 'Date, month and day are 2 digits

Display format specification character

n the worksheet, click Format Cell> Display Format.
Here, it is the same as the format specification character specified in “User Defined”.
There are many ways to specify format specification characters.
A typical place will be posted.

Format specification character

G/Standard Standard display format.
_(Under bar) You can leave a character space by the width of the character following _ (under bar).
" Displays the character string enclosed by".
The input value is displayed as a character string.
# 0 is not displayed if either side of the decimal point is less than the number of # symbols in the display format.
0 If the number of digits in the number is less than the number of 0 digits in the display format, 0 is added to the digits in the display format.
? Spaces are inserted at non-significant zeros, so the decimal places are aligned within the column.
. (Period) Displays a decimal point in numeric values.
,(Comma) Displays a thousands separator in numeric values.
yy Displays the year as a 2-digit number.
yyyy Displays the year as a 4-digit number.
m Displays the number of months. 1 to 12 can be displayed.
mm 01 to 12 can be displayed. 1 to 9 are displayed with 01 to 09 and 0.
mmmm January~December
mmmmm J to D initials are displayed.
d Displays the number of days. 1 to 31 can be displayed.
dd 01 to 31 can be displayed.
ddd Sun~Sat
dddd Sunday~Saturday
h Displays the hour. 0 to 23 can be displayed.
hh 00-23 can be displayed.
m Displays minutes. 0 to 59 can be displayed.
mm 00 to 59 can be displayed.
s Displays seconds. 0 to 59 can be displayed.
ss 00 to 59 can be displayed.
[h] Displays the time exceeding 24 hours.
[m] The minutes exceeding 60 minutes are displayed.
[s] Displays seconds exceeding 60 seconds.

The above are not all, but only those that are likely to be used in macros.

How to check display format specification characters

In the first place, the display format is specified as

Positive number format; negative number format; zero format; string format
It will be specified like this, but this will be kept as the basis of Excel.

If there is no change once set, it is more efficient to set in advance on the sheet instead of the macro.
It is better to limit the display format in the macro to a special part.

If you do not know the formatting characters when formatting with a macro,
You should check the display in “User Defined”.
If "Format Cells" → "Number" → "Custom
If you don't understand this,
I'm sorry, but please re-learn the basics of Excel before the macro.

Display format, user-defined basics

About Range.NumberFormat

About Range.NumberFormat
There is also a property called NumberFormat that doesn't have Local at the end.
The display format changes depending on the language locale.

The display format is the basic of Excel's basics.

Please hold down firmly.

Articles with the same theme "VBA basics"

28. Select cell / row / column (Select, Activate)
29. Delete / Insert cell / row / column (Delete, Insert)
30. General practice question 3
31. Format Cells (display format, NumberFormatLocal)
32. Format Cells (Alignment)
33. Format Cells (Font)
34. Format Cells (fill, interior)
35. Format Cells (ruled line, border)
36. General practice question 4
37. Specifying a book sheet
38. Set formula in cells

  • >
  • >
  • 31. Format Cells (display format, NumberFormatLocal)

  • If you find this site useful, please share and bookmark.

    I'm going to pay close attention to the description,
    If you have any mistakes or suggestions,「Contact Us」I hope you will let me know.
    Use the posted VBA code at your own risk.
    We are not liable for any damages such as data corruption.

    If you find this site useful, please share and bookmark.
    To the bottom of the text