Гугл таблицы формат номера телефона

Пользовательские форматы — мощная и полезная штука, о которой знают не все пользователи. Они доступны и в Excel, и в Google Таблицах.

Ячейки с приростом на скриншоте — это обычные пользовательские форматы, не условное форматирование.

С помощью них можно делать свои таблицы нагляднее, оформлять должным образом количественные данные, не трогая сами значения, и добавлять условия без условного форматирования — например, красить отрицательные числа красным или же добавлять какой-то символ к числам больше тысячи. А еще показывать телефонные номера в нужном формате и многое другое. При этом это все — обычное форматирование, а не условное (которое может существенно тормозить работу документов в Google Таблицах). Давайте разберемся с пользовательскими форматами.

Файл со всеми примерами из статьи

Где они настраиваются?

Формат -> Другие форматы -> Другие форматы чисел

(можно не только «чисел», но и пользовательские форматы «валютный» и «дата и время» — там более очевидный и удобный интерфейс, с ними проще работать).
Мы в основном поговорим про числа, с которыми разобраться сложнее. В конце статьи немного расскажу и о форматах даты-времени.

Символы, используемые в пользовательских форматах

0 цифра. Незначащий нуль, то есть если в числе меньше цифр, чем в таких нулей в формате, то будут отображаться «пустые» нули.

# цифра. Только значащая, без «пустых» лишних нулей (например, формат #,## покажет 2.3 как 2.3, а 2.37 как 2.37).

? цифра с добавлением пробела вместо незначащих нулей, чтобы десятичные запятые в разных числах были выровнены (ниже будет пример).

. точка=десятичная запятая.

, при применении в самом конце формата одна запятая означает округление до тысяч — пример, разумеется, ниже. При применении между символами числа 0,0 — добавляются разделители разрядов.

% проценты.

* (между форматом и текстом) выравнивает число по левому краю, а текст, указанный в формате — по правому.

*(до формата, после звездочки следует символ) заполняет ячейку указанным символом, чтобы в ней не оставалось пробелов.

[Color] или [ColorN] добавляет цвет шрифта для формата.

позволяет отображать «служебные» символы как просто текст — это касается символов * # %.

@ это текстовое значение. Например, формат @@ будет повторять текст в ячейке дважды.

«текст» в кавычках можно указать текст, который будет отображаться в данном формате. Например, формат «Сумма: «0 всегда будет показывать текст «Сумма: » перед числом.

Отдельные форматы для положительных, отрицательных чисел, нуля и текста

Можно предложить отдельные форматы для положительных чисел, отрицательных чисел, нуля и текста.

Перечислять их нужно через точку с запятой в этом порядке. Указывать все не обязательно. Можно прописать только два формата — для положительных и отрицательных, например.

Так, например, этот формат не будет отображать нули вообще, а отрицательные числа будут в скобках и без знака минус:

0;(0);

А такой формат вообще скроет любые данные (ведь для каждого из четырех вариантов мы указали пустой формат):

;;;

Видите число? А оно там есть.

Цвет в пользовательских форматах

Чтобы добавить к формату цвет, нужно указать его название на английском в квадратных скобках в начале формата.

[Green]0;[Red]-0;0;[Blue]@
это зеленый цвет для положительных чисел, красный для отрицательных, синий для текста.

Понятно, что таким образом вариантов цветов можно указать не так много.

Для большего разнообразия пользуйтесь кодом цвета в формате [ColorN]. Код N можно посмотреть в этой таблице — спасибо за нее порталу Excel Jet:

Условие на величину числа

В пользовательских форматах можно добавлять условия: [условие] формат; [условие] формат; формат для остальных случаев. В таком варианте уже нельзя будет добавлять четыре разных варианта для положительных-отрицательных-нуля-текста. Больше трех условий добавить не получится — два явных и одно для остальных случаев.

Давайте рассмотрим простой пример, а в следующем пункте будет более интересный. Если вы хотите в случае единицы отображать «1 штука», а в остальных случаях — «2 штуки», «3 штуки» и т.д. — нужен следующий формат:

[=1]0 » штука»;0 » штуки»

Напомню, формат на значение никак не влияет, истинное значение ячейки всегда можно видеть в строке формул. Наличие слова «Штуки» или любого другого текста в формате не повлияет на возможность проведения арифметических операций с числами. И не добавит никакого текста в сами ячейки, в их значения.

Добавим огня (символы в пользовательских форматах)

К пользовательским форматам можно добавлять символы. Например, огонь — как на скриншоте:

Как сделать такой формат?

Нужен следующий код:
[<2000][Red]#,# * 🔥;[>3000][Green]#,# * 🔥🔥🔥;[Blue]#,# * 🔥🔥

Расшифруем его:
[<2000] — условия. У нас есть формат для чисел меньше 2000, для чисел больше 3000 и для остальных случаев.
[Red] — цвет шрифта

#,# — числовой формат с разделителями разрядов
🔥 — текст, который мы хотим видеть в ячейке помимо числа (вы можете взять любой другой символ или написать разные слова для разных случаев)

* — выравнивает число слева, а текст справа

Незначащие нули

Если вы хотите отображать незначащие нули — введите столько нулей, сколько должно быть цифр в ячейке. Это подходит для индексов и артикулов.
Например, вам нужно отображать числа как артикулы из семи знаков — 874 как 0000874, 9872 как 0009872 и так далее.
Для этого случая подойдет формат:
0000000

Пример с 10 знаками:

Пользовательские форматы для номеров телефонов

Чтобы отображать телефонные номера, введенные просто как последовательность цифр, в нормальном виде — нужны пользовательские форматы.
Давайте посмотрим, как ими воспользоваться.

Формат (Alt+O) -> Числа (N) -> Другие форматы — > Другие форматы чисел

А дальше можно вводить формат. В самом простом виде просто вводим нули для цифр и знаки, которые хотим отображать:
0(000)000-00-00

Но такой формат не будет работать для номеров вида 9101234567, 1234567.
Только для 79161234567 или 89161234567.

Поэтому можно пойти дальше и вспомнить, что есть возможность задавать условия в пользовательских форматах.
Так, можно предусмотреть отдельный формат для чисел меньше 9999999 — без кода:
[<9999999]000-00-00;0(000)000-00-00

Такой вариант корректно отобразит и номера из 11 цифр, и номера из 7.
Но все еще не справится с номером из 10 цифр — как 9101234567.

Так что можно сделать еще один шаг и прописать два условия:

[<9999999]000-00-00; [<10000000000](000)000-00-00; 0(000)000-00-00

Теперь у нас предусмотрены три варианта.

Пользовательские форматы: округление до тысяч и миллионов

Продолжим исследовать бездонный мир пользовательских форматов и поговорим про округление.

Достаточно одной запятой в конце числового формата, чтобы округлить число до тысяч:
0,

Если хочется отображать в тысячах с десятыми/сотыми/прочими долями тысячи, можно добавить знаки после запятой:
0.00,

Сработает округление и для форматов с разделителями разрядов:
#,##0,
(То есть мы будем показывать число в тысячах, но если округленный результат будет иметь больше 10 тыс. — то в нем появятся разделители разрядов)

Можно добавить надпись после числа — чтобы не запутаться и точно понимать, что здесь числа в тысячах:
#,##0, тыс

Помня о том, что мы можем задавать отдельные форматы (через точку с запятой) для положительных и отрицательных чисел, можно задать формат с округлением для положительных чисел, а отрицательные показывать в обычном формате:
#,##0,;#,##0

И, конечно, можно пойти дальше и отображать число в миллионах или миллиардах:
#,##0,, — миллионы с разделителями разрядов
0,,, — миллиарды без разделителей разрядов

Знак вопроса для выравнивания по десятичной запятой

Знаки вопроса после запятой — это значащее число после десятичной запятой, и при этом вместо незначащих будет появляться пробел. Таким образом, числа будут выравниваться по запятой, но пустые нули не будут отображаться. В следующем формате будет до трех знаков после запятой:

0.???

Заполнение ячейки символом

Если поставить после звездочки символ, то вся ячейка до упора будет заполняться этим символом. В следующем варианте у нас будет число, а все свободное пространство заполнится дефисами:

*-0

Функция ТЕКСТ / TEXT

С помощью этой функции можно отобразить значение из ячейки в любом нужном формате, не залезая в меню форматов. Особенно полезно, когда мы соединяем с помощью амперсанда разный текст из нескольких ячеек — в таких случаях форматирование теряется, остаются только значения.

Синтаксис у функции простой — значение (ссылка на ячейку, как правило); формат (в кавычках, так как это текстовое значение).

Вот пример задачи, для которой ТЕКСТ полезна — составляем целую фразу из разных значений формулой, и для формулы прироста указываем процентный формат без знака минус в отрицательных случаях:

Пользовательские форматы даты и времени

Как я уже писал, с ними чуть проще. Есть встроенный мини-редактор, в котором можно «собрать» нужный формат в конструкторе.  Найти его можно по адресу:

Формат -> Другие форматы -> Другие форматы даты и времени

Здесь щелкаем на стрелку и там появляются все возможные варианты. «Продолжительность» подойдет для времени больше суток  — например, если у вас событие, которое длится 30 часов.

Выбираем что-то, например «День», он появляется слева в окне формата и далее можно на него щелкнуть и выбрать нужный тип отображения.

В итоге можно собирать самые разные форматы — на скриншоте во всех ячейках одна и та же функция ТДАТА / NOW, возвращающая текущие время и дату:

Меню «Другие форматы даты и времени» — не единственный вариант создания форматов даты. Можно использовать и коды, которые пригодятся для той же функции ТЕКСТ / TEXT и которые можно использовать в числовых форматах.

Используются там следующие символы:

S — секунда, M — минута, H — час (в случае с продолжительностью — в квадратных скобках)

D — день, M — месяц, Y — год

В зависимости от количества символов меняется формат. То есть D — это дата без нуля впереди, DD — с нулем, DDD — краткий формат дня недели, DDDD — полное название дня недели.

YY — краткий формат года (20), YYYY — полный (2020).

Файл со всеми примерами из статьи

Источники — что почитать по теме

Джон Уокенбах — «Формулы в Microsoft Excel»

Excel custom number formats

***

Мой курс по Google Таблицам в Skillbox

Here’s a link, though I don’t think it would do much. https://docs.google.com/spreadsheets/d/1-tjCZe6JTt-f4gEYISc5-pDvum_pILJIOK5T1vF1utc/edit?usp=sharing

There’s two things that needs to happen: format and data cleaning. First format.

I don’t think there is a built-in format for phone numbers (but I could be wrong), so we make our own. Select your column, go to your menus > Format > Number > Custom Number Format. You’ll see a place where you can input things. enter in 000-000-0000. This will format numbers and numbers only into this format. ###-###-#### is probably the more correct form, but I prefer 000-000-0000 because it adds leading zeroes. But choose which one works better for you.

Now for data cleaning. The best way I could figure is to use replace with regex. press Ctrl + H and check the ‘Search using regular expressions’. Set the search to specific range and set it to the column of phone numbers, because this method can be very invasive. in the find field input [()- ]. This is a regex pattern that will find all parentheses, dashes, and spaces. Keep the replace field blank. Then just to be safe, replace one at a time.

That should do it. But if you are willing to play around with AppScript, you can take this code of mine that I first thought of when answering.

function onOpen() {
  menu = SpreadsheetApp.getUi().createMenu("⚙️ Custom")
    .addItem("Fix all selected phone numbers", "numberFixer")
    .addToUi()
}

function numberFixer() {
  const activeSheet = SpreadsheetApp.getActiveSheet();
  const selection = activeSheet.getSelection().getActiveRange();
  const selectionValue = selection.getValues();
  for (let i = 0; i < selectionValue.length; i++) {
    for (let j = 0; j < selectionValue[i].length; j++) {
      const currentCellValue = selectionValue[i][j];
      if (typeof currentCellValue === 'string' && !currentCellValue.match(/w/g) && currentCellValue !== '') {
        selection.getCell(i + 1, j + 1)
          .setValue(currentCellValue.replace(/[()- ]/g, ''))
          .setNumberFormat("000-000-0000");
      }
    }
  }
}

This will create a custom menu that contains a function. When the function is run, it will remove all parentheses, dashes, and spaces if the cell doesn’t contain any letters. Afterwards, it will format the cell to a phone number.

Here’s a link, though I don’t think it would do much. https://docs.google.com/spreadsheets/d/1-tjCZe6JTt-f4gEYISc5-pDvum_pILJIOK5T1vF1utc/edit?usp=sharing

There’s two things that needs to happen: format and data cleaning. First format.

I don’t think there is a built-in format for phone numbers (but I could be wrong), so we make our own. Select your column, go to your menus > Format > Number > Custom Number Format. You’ll see a place where you can input things. enter in 000-000-0000. This will format numbers and numbers only into this format. ###-###-#### is probably the more correct form, but I prefer 000-000-0000 because it adds leading zeroes. But choose which one works better for you.

Now for data cleaning. The best way I could figure is to use replace with regex. press Ctrl + H and check the ‘Search using regular expressions’. Set the search to specific range and set it to the column of phone numbers, because this method can be very invasive. in the find field input [()- ]. This is a regex pattern that will find all parentheses, dashes, and spaces. Keep the replace field blank. Then just to be safe, replace one at a time.

That should do it. But if you are willing to play around with AppScript, you can take this code of mine that I first thought of when answering.

function onOpen() {
  menu = SpreadsheetApp.getUi().createMenu("⚙️ Custom")
    .addItem("Fix all selected phone numbers", "numberFixer")
    .addToUi()
}

function numberFixer() {
  const activeSheet = SpreadsheetApp.getActiveSheet();
  const selection = activeSheet.getSelection().getActiveRange();
  const selectionValue = selection.getValues();
  for (let i = 0; i < selectionValue.length; i++) {
    for (let j = 0; j < selectionValue[i].length; j++) {
      const currentCellValue = selectionValue[i][j];
      if (typeof currentCellValue === 'string' && !currentCellValue.match(/w/g) && currentCellValue !== '') {
        selection.getCell(i + 1, j + 1)
          .setValue(currentCellValue.replace(/[()- ]/g, ''))
          .setNumberFormat("000-000-0000");
      }
    }
  }
}

This will create a custom menu that contains a function. When the function is run, it will remove all parentheses, dashes, and spaces if the cell doesn’t contain any letters. Afterwards, it will format the cell to a phone number.