Configure a custom number format

By defining a custom number format, you can change the display format without altering the value of the cell.
This feature can be used when 'Use the old format for interpreting the cell’s display format' is not checked in the sheet settings

Attention

After setting a custom number format, checking 'Use the old format for interpreting the cell’s display format' may change the cell display depending on the specified format. For example, the number format 'yyyy/mm/dd (aaa)' will show '2024/10/25 (Fri)' when unchecked, but will display '2024/10/25 (aaa)' when checked.

Configure a custom number format

This document explains how to configure a custom number format.

Create a new number format

You can input a number format from scratch and apply it to a cell.
For example, apply a thousands separator to the number and add the unit 'kg' along with a half-width space at the end.
  1. Select the cell or range where you want to set a custom number format, right-click, and choose 'Format Cells'.
../../../_images/img_01.en195.png
  1. Select 'Custom' from the 'Category'.
../../../_images/img_02.en179.png
  1. Enter the number format in 'Type' and click the 'OK' button.
../../../_images/img_03.en144.png

Tip

When specifying '#,### "kg"_', the number 5000 will be displayed as follows.

../../../_images/img_08.en80.png

Customize the built-in number formats

You can customize a number format provided by CELF and apply it to a cell.
For example, add the day of the week at the end of a date in the format yyyy/mm/dd.
  1. Select the cell or range where you want to set a custom number format, right-click, and choose 'Format Cells'.
../../../_images/img_04.en128.png
  1. Select the 'yyyy/mm/dd' format from the 'Date' category.
../../../_images/img_05.en115.png
  1. Select 'Custom'.
../../../_images/img_06.en92.png
  1. Edit the string displayed in 'Type' and click the 'OK' button.
../../../_images/img_07.en85.png

Tip

When specifying 'yyyy/mm/dd (aaa)', '2024-10-25' will be displayed as follows.

../../../_images/img_09.en69.png

Hint

  • In 'Type', you will register the display formats to be used in that sheet.
  • Number formats that are no longer used in the sheet’s cells will be removed from 'Type' the next time the sheet is displayed.
  • Number formats specified from actions, such as the 'Set Format' action, will not be registered.
  • If the 'Type' is empty when you click the 'OK' button, the number format will be set to 'General'.

Supported format symbols

CELF supports the following format symbols.
Category Format symbol Description
Date m Display the month.
  mm Display the month in two digits.
  mmm Display the name of the month in three English letters.
  mmmm Display the name of the month in English.
  mmmmm Display the name of the month in one English character.
  d Display the day of the month.
  dd Display the day as two digits.
  ddd Display the day of the week using the 3-letter English abbreviation.
  dddd Display the day of the week in English.
  aaa Display the day of the week using its single-character Japanese abbreviation.
  aaaa Display the day of the week in Japanese.
  yy Display the year in 2 digits.
  yyyy Display the year in 4 digits.
  g Display the Japanese era name using its first alphabetic letter.
  gg Display the first letter of Japanese era name in kanji.
  ggg Display the Japanese era name in kanji.
  e Display the Japanese calendar year.
  ee Display the Japanese calendar year in two digits.
  r Display the Japanese calendar year in two digits.
  rr Display the Japanese era name in Kanji and the Japanese calendar year in two digits.
  bb Display the last 2 digits of the French calendar year.
  bbbb Display the French calendar year.
Time h Display the hour.
  hh Display the hour in two digits.
  m Display minute.
  mm Display minute in two digits.
  s Display second.
  ss Display second in two digits.
  [h] Display elapsed time.
  [hh] Display the elapsed time. Pad single-digit values with a leading zero to display as two digits.
  [m] Display elapsed minutes.
  [mm] Display the elapsed minutes. Pad single-digit values with a leading zero to display as two digits.
  [s] Display elapsed seconds.
  [ss] Display elapsed seconds; Pad single-digit values with a leading zero to display as two digits.
  AM/PM Display time in 12-hour format.
Number 0 Display the number with leading zeros to match the specified number of zeros.
  # If the number has fewer digits than the specified number of # symbols, display it without padding zeros.
  ? Pad the number with spaces to ensure alignment when the number of digits is insufficient.
  . Display the decimal point.
  , Display the thousands separator.
  #, Display numbers rounded to the nearest thousand. For example, 1234567 would be formatted as 1235.
  % Display numbers in percentage format.
  E Display numbers in exponential format.
Repeat * Repeat the next single-byte character until the cell is filled.
Other _ Display spaces that match the width of the following character.
  \ Display the Yen symbol.
  ! Escape the character immediately following this.
  @ Display the string as it is
  " Display the string enclosed in double quotation marks as it is.

Attention

  • Fraction display format is not supported.

About locale codes

Locale codes for Japanese and English (United States) can be specified.
The following notations are supported:
  • Japanese : [$-411]、[$-ja]、[$-ja-JP]
  • English (United States) : [$-409]、[$-en]、[$-en-US]

About text color

Text color can be specified by color names.
By specifying a color name within brackets, such as '[black]', the text will be displayed in that color.
The color names that can be specified are black, white, red, green, blue, yellow, magenta, and cyan.

Attention

  • Specifying colors using numbers, such as '[color1]', is not supported.

Related keywords

format, TEXT