Преобразование чисел, дат и строк в настраиваемый текстовый формат из кода VBA Excel с помощью функции Format. Синтаксис, параметры, символы, примеры.
Определение функции Format
Format – это функция, которая преобразует число, дату или строку в текст, отформатированный в соответствии с именованным выражением формата или инструкциями, составленными из специальных символов.
Синтаксис и параметры
Format(Expression, [FormatExpression], [FirstDayOfWeek], [FirstWeekOfYear])
- Expression – любое допустимое выражение (переменная), возвращающее числовое значение или строку (обязательный параметр).
- FormatExpression – выражение формата, именованное или содержащее инструкции из специальных символов (необязательный параметр).
- FirstDayOfWeek – константа, задающая первый день недели (необязательный параметр).
- FirstWeekOfYear – константа, задающая первую неделю года (необязательный параметр).
Именованные выражения форматов
Именные форматы даты и времени
| Имя формата | Описание |
|---|---|
| General Date | Стандартное отображение даты и времени в соответствии с параметрами системы. |
| Long Date | Длинный формат даты. |
| Medium Date | Средний формат даты. |
| Short Date | Краткий формат даты. |
| Long Time | Длинный формат времени. |
| Medium Time | Средний формат времени. |
| Short Time | Краткий формат времени. |
Проверьте отображение даты и времени с использованием именованных форматов на вашем компьютере при помощи следующего кода VBA Excel:
|
1 2 3 4 5 6 7 8 9 |
Sub FormatDateTime() MsgBox "General Date: " & Format(Now, "General Date") & vbNewLine _ & vbNewLine & "Long Date: " & Format(Now, "Long Date") & vbNewLine _ & vbNewLine & "Medium Date: " & Format(Now, "Medium Date") & vbNewLine _ & vbNewLine & "Short Date: " & Format(Now, "Short Date") & vbNewLine _ & vbNewLine & "Long Time: " & Format(Now, "Long Time") & vbNewLine _ & vbNewLine & "Medium Time: " & Format(Now, "Medium Time") & vbNewLine _ & vbNewLine & "Short Time: " & Format(Now, "Short Time") End Sub |
Скорее всего, результат будет таким:
Именованные форматы чисел
| Имя формата | Описание |
|---|---|
| General Number | Стандартное отображение числа без знака разделителя групп разрядов. |
| Currency | Денежный формат. |
| Fixed | Отображение числа без знака разделителя групп разрядов с двумя цифрами после разделителя целой и дробной части. |
| Standard | Отображение числа со знаком разделителя групп разрядов и с двумя цифрами после разделителя целой и дробной части. |
| Percent | Процентный формат: отображение числа, умноженного на 100, со знаком процента (%), добавленного справа. |
| Scientific | Отображение числа в экспоненциальном виде. |
| Yes/No | Возвращается «Нет», если число равно 0, иначе отображается «Да». |
| True/False | Возвращается «Ложь», если число равно 0, иначе отображается «Истина». |
| On/Off | Возвращается «Выкл», если число равно 0, иначе отображается «Вкл». |
Проверяем работу именованных форматов на числах 2641387.7381962 и 0 с помощью кода VBA Excel:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Sub FormatNumber() Dim n As Double n = 2641387.7381962 'n = 0 MsgBox "Форматируемое число = " & n & vbNewLine _ & vbNewLine & "General Number: " & Format(n, "General Number") & vbNewLine _ & vbNewLine & "Currency: " & Format(n, "Currency") & vbNewLine _ & vbNewLine & "Fixed: " & Format(n, "Fixed") & vbNewLine _ & vbNewLine & "Standard: " & Format(n, "Standard") & vbNewLine _ & vbNewLine & "Percent: " & Format(n, "Percent") & vbNewLine _ & vbNewLine & "Scientific: " & Format(n, "Scientific") & vbNewLine _ & vbNewLine & "Yes/No: " & Format(n, "Yes/No") & vbNewLine _ & vbNewLine & "True/False: " & Format(n, "True/False") & vbNewLine _ & vbNewLine & "On/Off: " & Format(n, "On/Off") End Sub |
Получаем следующий результат:
Вместо вопросительного знака в отображении числа в формате Currency, по идее, должен быть знак валюты (₽ или руб.).
Специальные символы для выражений форматов
Символы для форматов даты и времени
| Символ | Описание |
|---|---|
| Точка (.) | Разделитель компонентов даты (день, месяц, год). Используется при отображении месяца в виде числа. |
| Пробел | Разделитель компонентов даты (день, месяц, год). Используется при отображении месяца прописью. |
| Двоеточие (:) | Разделитель компонентов времени (часы, минуты, секунды). |
| d | День в виде числа без нуля в начале (1–31). |
| dd | День в виде числа с нулем в начале (01–31). |
| m | Месяц в виде числа без нуля в начале (1–12). Если (m) следует после (h) или (hh), отображаются минуты (0–59). |
| mm | Месяц в виде числа с нулем в начале (01–12). Если (mm) следует после (h) или (hh), отображаются минуты (00–59). |
| mmm | Месяц прописью в сокращенном виде (янв–дек). |
| mmmm | Полное название месяца (январь–декабрь). |
| y | День года в виде числа (1–366). |
| yy | Год в виде 2-значного числа (00–99). |
| yyyy | Год в виде 4-значного числа (1900–9999). |
| h | Часы в виде числа без нуля в начале (0–23). |
| hh | Часы в виде числа с нулем в начале (00–23). |
| n (m) | Минуты в виде числа без нуля в начале (0–59). |
| nn (mm) | Минуты в виде числа с нулем в начале (00–59). |
| s | Секунды в виде числа без нуля в начале (0–59). |
| ss | Секунды в виде числа с нулем в начале (00–59). |
В этой таблице перечислены далеко не все символы для выражений форматов даты и времени. Вы можете ознакомиться со всеми символами, в том числе и для форматирования чисел, на сайте разработчика.
Примеры отображения даты с помощью разных по количеству наборов символа d:
|
1 2 3 4 5 6 7 8 |
Sub DataIsD() MsgBox "d: " & Format(Now, "d") & vbNewLine _ & vbNewLine & "dd: " & Format(Now, "dd") & vbNewLine _ & vbNewLine & "ddd: " & Format(Now, "ddd") & vbNewLine _ & vbNewLine & "dddd: " & Format(Now, "dddd") & vbNewLine _ & vbNewLine & "ddddd: " & Format(Now, "ddddd") & vbNewLine _ & vbNewLine & "dddddd: " & Format(Now, "dddddd") End Sub |
Символы для числовых форматов
| Символ | Описание |
|---|---|
| Точка (.) | Десятичный разделитель. |
| Запятая (,) | Разделитель групп разрядов. В отображаемых числах заполняется пробелом. |
| (0) | Заполнитель, который отображает цифру или ноль. Используется, когда нужны ведущие нули или нули в конце числа. |
| (#) | Заполнитель, который отображает цифру или ничего не отображает. Используется, когда не нужны ведущие нули или нули в конце числа. |
| (%) | Заполнитель процента. Выражение умножается на 100, а знак процента (%) вставляется на той позиции, где он указан в строке формата. |
| (E- E+ e- e+) | Экспоненциальный формат. |
Примеры использования символов в выражениях числовых форматов VBA Excel:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub FormatNumber2() Dim n As Double n = 2641387.7381962 'n = 0.2397842 MsgBox "Форматируемое число = " & n & vbNewLine _ & vbNewLine & "0.##: " & Format(n, "0.##") & vbNewLine _ & vbNewLine & "000.###: " & Format(n, "000.###") & vbNewLine _ & vbNewLine & "#,###.###: " & Format(n, "#,###.###") & vbNewLine _ & vbNewLine & "0 %: " & Format(n, "0 %") & vbNewLine _ & vbNewLine & "0.### E-: " & Format(n, "0.### E-") & vbNewLine _ & vbNewLine & "0.### E+: " & Format(n, "0.### E+") End Sub |
Символы для текстовых форматов
| Символ | Описание |
|---|---|
| At-символ (@) | Заполнитель для символов, отображающий знак или пробел. |
| Амперсанд (&) | Заполнитель для символов, отображающий знак или ничего (пустая строка). |
| Меньше (<) | Принудительный перевод всех буквенных символов в нижний регистр. |
| Больше (>) | Принудительный перевод всех буквенных символов в верхний регистр. |
Примеры использования символов в выражениях строковых форматов VBA Excel:
|
1 2 3 4 5 6 |
Sub FormatString() MsgBox "Номер телефона: " & Format("1234567890", "+7 (@@@) @@@-@@-@@") & vbNewLine _ & vbNewLine & "Серия и номер паспорта: " & Format("1234567890", "&& && &&&&") & vbNewLine _ & vbNewLine & "Нижний регистр: " & Format("Нижний регистр", "<") & vbNewLine _ & vbNewLine & "Верхний регистр: " & Format("Верхний регистр", ">") End Sub |
Форматы для различных значений одного выражения
Различные форматы для разных числовых значений
В выражении формата для чисел предусмотрено от одного до четырех разделов, отделяемых друг от друга точкой с запятой. Отображаемая строка зависит от значения, возвращенного параметром Expression функции Format.
| Количество разделов | Результат форматирования |
|---|---|
| Один раздел | Выражение формата применяется ко всем значениям. |
| Два раздела | Первый раздел применяется к положительным значениям и нулям, второй – к отрицательным значениям. |
| Три раздела | Первый раздел применяется к положительным значениям, второй – к отрицательным значениям, третий – к нулям. |
| Четыре раздела | Первый раздел применяется к положительным значениям, второй – к отрицательным значениям, третий – к нулям, четвертый – к значениям Null. |
Пример использования четырех разделов в выражении формата числовых значений:
|
1 2 3 4 5 6 7 8 9 10 |
Sub FormatDifferentValues() MsgBox "Число 1234,5678: " & _ Format(1234.5678, "#,##0.00 руб.;Отрицательное число;Ноль рублей;Значение Null") _ & vbNewLine & vbNewLine & "Число -25: " & _ Format(-25, "#,##0.00 руб.;Отрицательное число;Ноль рублей;Значение Null") _ & vbNewLine & vbNewLine & "Число 0: " & _ Format(0, "#,##0.00 руб.;Отрицательное число;Ноль рублей;Значение Null") _ & vbNewLine & vbNewLine & "Null: " & _ Format(Null, "#,##0.00 руб.;Отрицательное число;Ноль рублей;Значение Null") End Sub |
Различные форматы для разных строковых значений
В выражении формата для строк предусмотрено до двух разделов, отделяемых друг от друга точкой с запятой. Отображаемая строка зависит от текста, возвращенного параметром Expression функции Format.
| Количество разделов | Результат форматирования |
|---|---|
| Один раздел | Выражение формата применяется ко всем строковым данным. |
| Два раздела | Первый раздел применяется к строковым данным, второй – к значениям Null и пустым строкам («»). |
Пример использования двух разделов в выражении формата строк:
|
1 2 3 4 5 6 7 8 9 10 |
Sub FormatString2() MsgBox "Строка «Белка»: " & _ Format("Белка", "@;Пустая строка или Null") _ & vbNewLine & vbNewLine & "Пустая строка: " & _ Format("", "@;Пустая строка или Null") _ & vbNewLine & vbNewLine & "Строка «Null»: " & _ Format("Null", "@;Пустая строка или Null") _ & vbNewLine & vbNewLine & "Значение Null: " & _ Format(Null, "@;Пустая строка или Null") End Sub |
Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.
Исправить в табл. «Символы для форматов даты и времени» для символов m и mm количество месяцев (1-31) и (01-31)
Спасибо, Владимир! Исправил.
Евгений, здравствуйте!
Никак не получается решить данную задачку, может поможете.
В столбце «A» из списка выбираются значки валют — ₽, $, €, £. В столбце «B» прописаны числовые значения (стоимость). Необходимо сделать функцию, которая бы при выборе одного из значка валют, соответствующим образом меняла формат числа («#,##0.00 $») из соседней ячейки (столбец «B»).
Например:
A B
$ 100 $
₽ 1,000 ₽
€ 10 €
Заранее спасибо!
Здравствуйте, Николай!
Выделите столбец B и задайте три правила условного форматирования в зависимости от содержимого ячейки A1 (по относительному адресу, без значков $).
Правило 1: Формула —
=A1="$", Формат —Финансовый $ французский (Канада)Правило 2: Формула —
=A1="₽", Формат —Финансовый ₽Правило 3: Формула —
=A1="€", Формат —€ ЕвроЗдравствуйте!
Очень прошу помочь с решением такой задачи:
имеем уже заполненный столбец с серийными номерами оборудования. Номера могут быть разных стандартов: 4-, 5-, 6-значные числа; номера, состоящие из цифр и букв; 9-значные номера только из цифр, где первым символом должен быть ноль.
Если пользователь перед вводом такого номера не выбрал формат ячеек как текстовый, то этот значащий ноль будет потерян, номер сохранится как 8-значное число. При этом, в столбце могут встречаться и правильные номера в текстовом формате из 9 цифр с нулем в начале.
Как можно макросом выполнить проверку в таком столбце и автоматически исправить все 8-значные числа добавлением к ним лидирующего ноля?
Заранее спасибо.
Здравствуйте, Владимир!
Вы можете использовать следующий код:
Евгений, всё получилось, Ваш код идеально отрабатывает мою задачу! Огромное спасибо за помощь и очень оперативный ответ, Вы просто волшебник!) Надо учиться VBA самому — такой классный инструмент, можно будет творить великие вещи)
Здравствуйте, подскажите можно ли задать ячейке формат обыкновенной дроби, если да то как это сделать?
Добрый день, Максим!
Это возможно: «Формат ячеек» >> «Дробный» >> «Простыми дробями» (или «Дробями до двух цифр» и т.д.). После целой части сначала пробел, затем дробную часть, как обычно, через «/».
здравствуйте, подскажите, как можно заставить в коде присвоить переменной количество часов более 24?
Здравствуйте, Дмитрий!
Ответ на ваш вопрос: x = 56 (или любое другое количество часов). Предполагаю, что вы имели в виду что-то другое. Если это так, приведите пример.
верно, неточно сформулировал. считаю сумму часов в формате «чч:мм». Сумма составила 24:31 ч., а переменной присвоено 00:31… Не хочется переводить все в минуты и обратно. Может есть более простой способ?
По-моему, с переменной As Date собрать больше 23:59 не получится.
Какой функции эксель на русском соответствует свойство range numberformat?
Хотелось бы не вба макросом определять код формата, а функцией в экселе.
Только вручную через «Формат ячеек». Функция ТЕКСТ задает формат, например, возвращает число с ведущими нулями:
=ТЕКСТ(1234;"0000000"), но в текстовом формате.