Как привести телефонные номера к единому формату?
Как это сделать?
В качестве примера, возьмем номер телефона в виде «9264177054», нам нужно добавить код страны и преобразовать этот номер к виду +7 (926) 417-70-54. Как это сделать? Очень просто!
Для того чтобы создать свой формат данных, переведите курсор в ячейку, к которой вы хотите применить пользовательский формат, и вызовите диалоговое окно Формат ячеек.
Для вызова этого окна используйте сочетание клавиш «Ctrl+1» или нажмите правую клавишу мышки и в контекстном меню выберите пункт «Формат ячеек. «:
В открывшемся окне, во вкладке «Число» выберите пункт «все форматы». В появившееся справа поле Тип: введите маску нужного Вам формата. В нашем примере это будет: +7″ «(#)» «000-00-00. Обратите внимание, что в окошке «Образец» данные поменяли свой вид. Если вас устраивает такой вид данных нажмите «Оk». Данные в указанной ячейке примут соответствующий вид +7 (926) 417-70-54.
Теперь ваш формат сохранен в Excel и его можно использовать для форматирования. Выделите ячейки, к которым надо применить пользовательский формат, и вызовите диалоговое окно Формат ячеек. В списке форматов найдите свой (он будет последним), выделите его и нажмите «Оk».
Как это работает?
На самом деле все очень просто. Excel использует несколько спецсимволов в масках форматов:
Номера телефонов в формате Excel
Форматирование телефонных номеров в Excel (Содержание)
Формат телефонных номеров в Excel
Возможно форматирование телефонных номеров в Excel. Это небольшая, но очень инновационная функция в Excel. В каждой стране свой код страны и формат написания телефонных номеров. В некоторых номерах номера телефонов разделены пробелами, в некоторых разрешены скобки для кода страны, в некоторых разница в длине номера, а в некоторых ставятся пробелы после кода страны, а остаток номера телефона следует последовательности.
Форматирование телефонных номеров может быть выполнено путем доступа к ячейкам « Формат» в главном меню в раскрывающемся списке « Формат ».
Как отформатировать телефонные номера в Excel?
Форматирование телефонных номеров в Excel очень просто и удобно. Давайте разберемся с разными методами на нескольких примерах.
Давайте рассмотрим несколько случайных телефонных номеров для их форматирования. У нас есть номера телефонов длиной 10 символов, как показано ниже.
Теперь для форматирования телефонных номеров в Excel сначала выберите телефонные номера, затем перейдите в Главное меню и нажмите Формат, как показано ниже. Как только мы нажмем на «Формат», мы получим выпадающий список, в котором мы выберем « Формат ячеек», который расположен внизу списка, как показано на скриншоте ниже.
Как только мы нажмем на «Форматировать ячейки», мы получим диалоговое окно, которое содержит различные функции, связанные с форматированием ячейки, такие как «Число», «Выравнивание», «Шрифт», «Граница», «Заливка» и «Защита».
Поскольку наша задача состоит в том, чтобы отформатировать телефонные номера, мы перейдем на вкладку « Число » и выберем « Пользовательский параметр», как показано на скриншоте ниже.
Как мы видим и проверяем, прокручивая вверх и вниз поле Тип, нет предустановленного формата телефонных номеров. Поэтому для настройки телефонных номеров удалите уже заполненные номера или текст и введите свой собственный формат. Как здесь, мы удалили слово General из раздела type и добавили « +91 » в качестве кода страны Индии, затем «-» и затем 10 нулей, которые являются стандартными 10-значными числами.
Как только мы это сделаем, мы увидим, что в одну из выбранных ячеек с номером телефона мы также добавим «+ 91-» чуть выше к отформатированной структуре в качестве образца. Затем нажмите ОК, как показано ниже.
Как только мы нажмем кнопку ОК, мы увидим, что выбранные ячейки будут отформатированы в соответствии с типом, который мы исправили в поле Формат ячейки, как показано на снимке экрана ниже.
После того, как мы отформатировали несколько тестовых телефонных номеров, мы можем отформатировать любую ячейку, содержащую телефонные номера, в том же формате.
Существует еще один способ форматирования телефонных номеров в Excel. Давайте рассмотрим те же наборы образца телефонного номера, которые мы видели в примере-1. Теперь для форматирования телефонных номеров сначала выберите данные, затем сделайте прямо на них. И выберите параметры форматирования ячеек, как показано на скриншоте ниже.
После этого мы получим диалоговое окно «Формат ячеек», как показано ниже.
Здесь мы введем новый формат для номера телефона. Здесь мы разделили код страны с помощью скобок и разделили половину чисел на «-» на 3 части и нажмите «ОК», как показано ниже.
Как мы видим ниже, формат, который мы установили в ячейках формата для телефонных номеров, теперь отражается на выбранных данных.
После того, как наш формат установлен, мы можем использовать его где угодно, когда видим, что телефонные номера должны быть отформатированы.
Чтобы проверить, виден ли наш недавно созданный формат телефонных номеров в ячейках формата или нет, сделайте это в любом месте на листе и выберите параметры Формат ячеек в нижней части меню. После этого появится то же самое окно ячеек формата. Перейдите на вкладку Number и выберите Custom option. Прокрутите вниз, чтобы найти наш недавно созданный формат.
Как мы можем видеть на скриншоте ниже, мы обвели форматы телефонных номеров, которые мы создали в примере 1 и примере 2.
Таким образом, мы можем создавать различные типы форматов телефонных номеров и использовать их в соответствии с нашими требованиями.
Плюсы формата телефонных номеров в Excel
Для данных большого телефонного номера становится очень легко понять код страны и фактический номер телефона, если данные правильно структурированы. Мы можем классифицировать и сортировать телефонные номера разных стран по их коду.
То, что нужно запомнить
Рекомендуемые статьи
Отображение чисел в виде почтовых индексов, страховых номеров (SSN) и номеров телефонов
Excel предлагает специальные числовые форматы для почтовых индексов, номеров социального страхования и номеров телефонов. Если они вам не соответствуют, вы можете создать и применить собственный числовом формате.
Применение к числам заранее задав формат почтового индекса
Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.
На вкладке Главная рядом с полем Общие в поле Формат 
щелкните стрелку и выберите другие числовые форматы.
В диалоговом окне Формат ячеек в категории нажмитекнопку Особый.
В списке Тип выберите почтовый индекс или Почтовый индекс + 4.
Примечание: Эти специальные форматы номеров доступны только в том случае, если в формате Расположение (язык) за установлено положение Сша (английский). В разных региональных данных для почтовых индексов не заметив или не заметив специальный числовой формат.
Создание пользовательского формата почтового индекса
Если специальные форматы почтовых индексов вам не соответствуют, можно создать пользовательский формат почтового индекса.
Важно: Пользовательские числовые форматы влияют только на способ отображения числа и не влияют на его базовое значение. Пользовательские числовые форматы хранятся в активной книге и недоступны для открываемых новых книг.
Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.
На вкладке Главная рядом с полем Общие в поле Формат щелкните стрелку и выберите другие числовые форматы.
В диалоговом окне Формат ячеек в разделе Категория выберите пункт Другой.
В списке Тип выберите числовой формат, который нужно настроить. Код, используемый для числовых форматов, появится в поле Тип над списком Тип.
Совет: Чтобы настроить предопределяемый формат Почтовый индекс + 4, выберите 00000-0000 в списке Тип. При выборе встроенного числого формата в списке Тип Excel создает его копию, которую можно настроить.
В поле Тип внесите необходимые изменения в выбранный числовой формат.
Совет: Если диапазон ячеек содержит как пятизначный, так и девятизначный почтовый индекс, вы можете ввести [
Включать в почтовые индексы ведущие символы
Важно: Пользовательские числовые форматы влияют только на способ отображения числа и не влияют на его базовое значение. Пользовательские числовые форматы хранятся в активной книге и недоступны для открываемых новых книг.
Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.
На вкладке Главная рядом с полем Число в поле Числовом формате щелкните стрелку и выберите другие числовые форматы.
В диалоговом окне Формат ячеек в разделе Категория выберите пункт Другой.
В поле Тип введите *0 и нужный формат почтового индекса.
Совет: Например, для 5-значного почтового индекса введите *0#####
Отображение номеров социального страхования полностью
Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.
На вкладке Главная рядом с полем Число в поле Числовом формате щелкните стрелку и выберите другие числовые форматы.
В диалоговом окне Формат ячеек в категории нажмитекнопку Особый.
В списке Тип выберите номер социального обеспечения.
Примечание: Этот специальный числовом формате доступен только в том случае, если в формате Расположение (язык) за установлено положение Сша (английский). В разных региональных службах для номеров социального страхования (или без них) не огося.
Отображение только последних цифр номеров социального страхования
Для общих мер безопасности может потребоваться отобразить только последние цифры номера социального обеспечения и заменить остальные цифры нулями или другими знаками. Для этого можно использовать формулу, включаемую функции С ФУНКЦИИ С CONCATENATE и RIGHT. В этой процедуре предполагается, что все номера социального страхования находятся в одном столбце и что у вас есть второй столбец, в котором будут отображаться только последние несколько цифр номеров социального страхования.
Важно: Чтобы запретить другим пользователям просматривать весь номер социального обеспечения, сначала скроете столбец с этим номером, а затем защитите его, чтобы неавторизованные пользователи не могли открыть данные. Лучше всего не хранить полные номера социального страхования (SOCIALS) в книгах. Вместо этого все номера социального страхования хранятся в расположении, которое соответствует строгим стандартам безопасности (например, в программе для работы с базами данных), а затем используйте только четыре последних цифры чисел в книгах.
В пустом столбце щелкните первую ячейку.
На вкладке Формулы нажмите кнопку Вставить функцию,введите ТЕКСТ,а затем введите С ФУНКЦИЮ С CONCATENATE.
В области формул отредактируем формулу так, чтобы она выглядела так:
=СОВЕКАN(«000-00-«; ПРАВАЯ( : ;4))
где — это первая ячейка (например, A1), а — последняя ячейка (например, A10) в диапазоне ячеек, содержащих номера социального страхования.
Excel отображает текстовую строку «000-00» вместо первых 5 цифр номера социального обеспечения и объединяет ее с четырьмя последними цифрами номера социального обеспечения (например, 000-00-1234).
Чтобы заполнить остальные ячейки в диапазоне, перетащите в ячейку, содержаную формулу, и перетащите его вниз по столбце.
Отображение номеров в качестве номеров телефонов
Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.
На вкладке Главная рядом с полем Число в поле Числовом формате щелкните стрелку и выберите другие числовые форматы.
В диалоговом окне Формат ячеек в категории нажмитекнопку Особый.
В списке Тип выберите Телефон число.
Примечание: Этот специальный числовом формате доступен только в том случае, если в формате Расположение (язык) за установлено положение Сша (английский). В разных региональных значениях для номеров телефонов не за исключением специальных форматов номеров.
Применение к числам заранее задав формат почтового индекса
Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.
На вкладке Главная в областиЧисло во всплывающее меню Числовом формате выберите пункт Пользовательский.
В диалоговом окне Формат ячеек в категории нажмитекнопку Особый.
В списке Тип выберите почтовый индекс или Почтовый индекс + 4.
Примечание: Эти специальные форматы номеров доступны только в том случае, если в формате Расположение (язык) за установлено положение Сша (английский). В разных региональных данных для почтовых индексов не заметив или не заметив специальный числовой формат.
Создание пользовательского формата почтового индекса
Если специальные форматы почтовых индексов вам не соответствуют, можно создать пользовательский формат почтового индекса.
Важно: Пользовательские числовые форматы влияют только на способ отображения числа и не влияют на его базовое значение. Пользовательские числовые форматы хранятся в активной книге и недоступны для открываемых новых книг.
Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.
На вкладке Главная в областиЧисло во всплывающее меню Числовом формате выберите пункт Пользовательский.
В диалоговом окне Формат ячеек в разделе Категория выберите пункт Другой.
В списке Тип выберите числовой формат, который нужно настроить. Код, используемый для числовых форматов, появится в поле Тип над списком Тип.
Совет: Чтобы настроить предопределяемый формат Почтовый индекс + 4, выберите 00000-0000 в списке Тип. При выборе встроенного числого формата в списке Тип Excel создает его копию, которую можно настроить.
В поле Тип внесите необходимые изменения в выбранный числовой формат.
Совет: Если диапазон ячеек содержит как пятизначный, так и девятизначный почтовый индекс, вы можете ввести [
Включать в почтовые индексы ведущие символы
Важно: Пользовательские числовые форматы влияют только на способ отображения числа и не влияют на его базовое значение. Пользовательские числовые форматы хранятся в активной книге и недоступны для открываемых новых книг.
Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.
На вкладке Главная в областиЧисло во всплывающее меню Числовом формате выберите пункт Пользовательский.
В диалоговом окне Формат ячеек в разделе Категория выберите пункт Другой.
В поле Тип введите *0 и нужный формат почтового индекса.
Совет: Например, для 5-значного почтового индекса введите *0#####
Отображение номеров социального страхования полностью
Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.
На вкладке Главная в областиЧисло во всплывающее меню Числовом формате выберите пункт Пользовательский.
В диалоговом окне Формат ячеек в категории нажмитекнопку Особый.
В списке Тип выберите номер социального обеспечения.
Примечание: Этот специальный числовом формате доступен только в том случае, если в формате Расположение (язык) за установлено положение Сша (английский). В разных региональных службах для номеров социального страхования (или без них) не огося.
Отображение только последних цифр номеров социального страхования
Для общих мер безопасности может потребоваться отобразить только последние цифры номера социального обеспечения и заменить остальные цифры нулями или другими знаками. Для этого можно использовать формулу, включаемую функции С ФУНКЦИИ С CONCATENATE и RIGHT. В этой процедуре предполагается, что все номера социального страхования находятся в одном столбце и что у вас есть второй столбец, в котором будут отображаться только последние несколько цифр номеров социального страхования.
Важно: Чтобы запретить другим пользователям просматривать весь номер социального обеспечения, сначала скроете столбец с этим номером, а затем защитите его, чтобы неавторизованные пользователи не могли открыть данные. Лучше всего не хранить полные номера социального страхования (SOCIALS) в книгах. Вместо этого полностью храните номера социального страхования в расположении, которое соответствует строгим стандартам безопасности (например, в программе для работы с базами данных), а затем используйте только четыре последних цифры чисел в книгах.
В пустом столбце щелкните первую ячейку.
На вкладке Формулы в области Функциянажмите кнопку Вставить, наведите указатель мыши на пункт Тексти выберите пункт СОВЕКАТЬ.
В области формул отредактируем формулу так, чтобы она выглядела так:
=СОВЕКАN(«000-00-«; ПРАВАЯ( : ;4))
где — это первая ячейка (например, A1), а БДЛН — последняя ячейка (например, A10) в диапазоне ячеек, содержащих номера социального страхования.
Excel отображает текстовую строку «000-00» вместо первых 5 цифр номера социального обеспечения и объединяет ее с четырьмя последними цифрами номера социального обеспечения (например, 000-00-1234).
Чтобы заполнить остальные ячейки в диапазоне, перетащите в ячейку, содержаную формулу, и перетащите его вниз по столбец.
Отображение номеров в качестве номеров телефонов
Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.
На вкладке Главная в областиЧисло во всплывающее меню Числовом формате выберите пункт Пользовательский.
В диалоговом окне Формат ячеек в категориинажмите кнопку Специальный.
В списке Тип выберите Телефон число.
Примечание: Этот специальный числовом формате доступен только в том случае, если в формате Расположение (язык) за установлено положение Сша (английский). В разных региональных значениях для номеров телефонов не за исключением специальных форматов номеров.
Изменение формата номера телефона
Не получается обновить формат ячеек. Присутствуют номера телефонов в следующем формате:
Нужно привести их к виду :
После добавления формата cледующим образом:
Обновляется только первая ячейка, для обновления формата по остальным-нужно выбирать каждую ячейку и нажимать enter.
1 ответ 1
Формат ячеек, который задается, работает с числовыми данными. А в ячейках, похоже, числа, представленные в текстовом формате. Это могло случиться по разным причинам: при загрузке со сторонних программ; если формат столбца перед вводом данных задан как Текстовый; если данные получены формулой, которая возвращает текст.
В левом верхнем углу ячейки зеленый треугольник информирует о том, что число сохранено как текст. Эта индикация может быть отключена в параметрах.
Формулой =ЕТЕКСТ(ссылка) или =ЕЧИСЛО(ссылка)
Проверить формат ячеек. Изменить, если Текстовый. Если данные получены формулой, нужно что-то менять в формуле.
Выделить диапазон с данными, вкладка Данные-Текст по столбцам-ОК. Текстовые числовые значения преобразуются в числа. После этого можно задать формат ячеек для всего диапазона.
Привести данные к нужному формату (формат данных, не ячейки!) можно в другом столбце формулой:
Как в Excel номера телефонов привести к единому формату?
В загруженной в Excel базе столбец записан разными способами:
какой формулой форматировать в Эксель номер телефона, чтобы преобразовать к единому формату в 11 знаков начиная с цифры 7
Можно воспользоваться простой заменой, как в случаи «+7» заменить на «8» (ctrl+h). Еще можно воспользоваться в новом столбце формулой ПРАВСИМВ и указать сколько символов оставить. Потом присоединить к получившемуся значению необходимое значение с помощью &.
Такие возможности описаны в большом количестве в сети. Главное, описать правильно, что хотите сделать со значением ячейки
Чтобы обработать телефонный номер надо ввести дополнительный столбец в который записать формулу для преобразования номеров телефонов в единый формат можно просто добавить цифру 7 к последним 10 цифрам телефона.
для ячейки A2 формула имеет вид:
однако очень часто в поле телефонного номера встречаются комментарии, в этом случае
если требуется преобразовать номер к формату +7. то формула примет вид:
Если слово пишется в графе единицы измерения, в этом случае формула будет выглядеть так:
=ЕСЛИ( ИЛИ( ЦЕЛОЕ( ОСТАТ(A1;100)/10)=1 ; ОСТАТ(A1 ; 10)>=5;ОСТАТ(A1 ; 10)=0) ; «штук» ; ЕСЛИ( ОСТАТ(A1;10)=1 ; «штука» ; «штуки»))
Если требуется написать в тексте число и количество штук через пробел, в этом случае формула примет вид:
=A1&» «&ЕСЛИ( ИЛИ( ЦЕЛОЕ( ОСТАТ(A1;100)/10)=1 ; ОСТАТ(A1;10)>=5 ; ОСТАТ(A1;10)=0) ; «штук» ; ЕСЛИ( ОСТАТ(A1;10)=1 ; «штука»;»штуки»))
Обе формулы рабочие (можно копировать в свою таблицу, с заменой ячейки A1 на ячейку таблицы, в которой записано целое число определяющее количество штук)
Если слово надо писать в отдельную графу, то числа из ячейки A1 формула примет вид:
=ЕСЛИ( ИЛИ( ЦЕЛОЕ( ОСТАТ(A1;100)/10) = 1 ; ОСТАТ( A1 ; 10 )>=5 ; ОСТАТ(A1 ; 10)=0) ; «ящиков» ; ЕСЛИ( ОСТАТ( A1 ; 10)=1 ; «ящик» ; «ящика»))
Для использования в тексте потребуется писать число, а потом слово, формула будет выглядеть так:
=A1&» «&ЕСЛИ( ИЛИ( ЦЕЛОЕ( ОСТАТ(A1;100)/10) = 1 ; ОСТАТ( A1 ; 10 )>=5 ; ОСТАТ(A1 ; 10)=0) ; «ящиков» ; ЕСЛИ( ОСТАТ( A1 ; 10)=1 ; «ящик» ; «ящика»))
Эту формулу можно копировать отсюда в свою таблицу, но необходимо заменить A1 на нужную ячейку.
В формуле обозначения ячеек записываются латинскими буквами. Теперь выделяете ячейку F7, подводите курсор к правому нижнему углу ячейки (там черный квадратик). Тогда курсор из обычного вида перейдёт в вид прямой чёрный крестик (+). Теперь, нажимаете левую клавишу мыши и удерживая её нажатой, протягиваете курсор по всему столбцу F, включая ячейку F135, и после этого отпускаете клавишу мышки. Тем самым Вы скопируете формулу на весь столбец F, точнее на диапазон ячеек F7-F135. В тех строках, в которых разность между значениями в текущей и вышестоящей строке больше 1, появятся звёздочки (*).
Чтобы посчитать сумму значений ячеек обычно используется функция СУММ, а в качестве аргументов пишутся либо значения, либо диапазоны ячеек либо сами ячейки.
Если требуется вычислить сумму значений ячеек разных диапазонов, то эти диапазоны перечисляются как аргументы функции сумм.
вычислит сумму чисел, находящихся в ячейках из диапазона A3:A8 и диапазона C3:H3
Если диапазоны чисел находятся на разных листах одной книги, то перед ссылкой на диапазон надо указать наименование листа с восклицательным знаком
=СУММ( A3:A8 ; C3:H3 ; Лист2!A1:A6 )
если диапазоны суммирования находятся в разных книгах, то придется указать и название книги в квадратных скобках пример:
=СУММ(A3:A8 ; C3:H3 ; [Книга3]Лист2!A1:A6 )
всего в функции СУММ можно указывать от 1 до 255 аргументов.
Имя файла доступно при запросе информации о файле с помощью функции ЯЧЕЙКА(«имяфайла»)
Имя файла эксель выдает в квадратных скобках, поэтому в наименовании файла лучше не использовать квадратные скобки (они преобразуются в круглые, а это может вызвать ошибку).
Если таблица не была сохранена функция выдаст пустую строку.
Для получения имени файла надо сначала убедиться, что таблица была сохранена в файл, а затем взять все символы внутри квадратных скобок, функцией ПСТР.
Вот формула для записи назваия файла в ячейку Excel:
Формулу можно скопировать отсюда в любую ячейку таблицы.