Как в экселе сделать номер телефона
Если у вас есть список телефонных номеров клиентов на листе, и вы хотите разделить их по областям, вам необходимо сначала отделить коды городов от их телефонных номеров. Теперь я расскажу вам два быстрых способа отделить коды городов от номеров телефонов в Excel.
Отделить код города от номера телефона с помощью формулы в Excel
Разделите код города от номера телефона текстом в столбец
- Повторное использование всего: добавьте наиболее часто используемое или сложные формулы, диаграммы и все остальное в избранное и быстро повторно используйте их в будущем.
- Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния: несколько книг и листов в одну; Объединить несколько ячеек/строк/столбцов без потери данных; Объедините повторяющиеся строки и суммируйте.
- Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Из одной книги в несколько файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
- Вставить пропуск скрытых/отфильтрованных строк; Подсчет и сумма по цвету фона; Массовая отправка персонализированных писем нескольким получателям.
- Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделе, дню, частоте и т. Д. Фильтр жирным шрифтом, формулами, комментарием …
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
![]()
Код города отделите от номера телефона с помощью формула в Excel
Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Сэкономьте 50% своего времени и уменьшите тысячи щелчков мышью каждый день!
Как правило, код города — это первые три цифры телефонного номера, поэтому вы можете использовать следующую формулу:
Выберите ячейку и введите эту формулу = MID (B1,1,3) (B1 — это ячейка, от которой вы хотите отделить код города) и нажмите Enter , затем перетащите маркер заполнения, чтобы заполнить диапазон, в котором вы хотите применить эту формулу. Смотрите, коды городов извлекаются из списка телефонных номеров:
![]()
Разделите код города от номера телефона с помощью текста в столбец в Excel
В Excel вы также можете использовать функцию преобразования текста в столбец, чтобы завершить разделение.
1. Выберите список номеров телефонов, нажмите Данные > Текст в столбцы . См. снимок экрана:
2. В мастере преобразования текста в столбцы установите флажок Фиксированная ширина и нажмите Далее . См. снимок экрана:
3. Затем установите ширину поля, установив линии разрыва со стрелкой. См. снимок экрана:
4. Нажмите Готово . Затем вы увидите, что коды городов отделены от полных номеров телефонов
Предположим, что пользователи заполняют таблицу содержащую список телефонных номеров для звонков клиентам. Пусть важно быстро определить те из них, у которых есть московский номер.
Телефонные номера обычно отображают в формате (495) 111-1111. Для этого нужно установить формат ячеек: Дополнительный/ Номер телефона (нажав CTRL+1 ). В этом случае ввод в ячейку телефонного номера можно производить, опустив скобки кода региона и дефис, т.е. 4951111111.
Создадим правило Условного форматирования для выделения номеров принадлежащих абонентам г.Москва (телефоны с номерами начинающихся с кодов 495 и 499):
- выберите требуемый формат, например, красный цвет шрифта.
Вместо использования функции ИЛИ() , можно создать два правила соответствующих разным кодам с формулами =ЛЕВСИМВ(A1;3)="499" и =ЛЕВСИМВ(A1;3)="495" .
В результате получим.
У данного примера имеется существенный недостаток: при вводе ошибочного номера, например, (495) 111, т.е. ввели неполный номер, Условное форматирование не покажет ошибки. для того, чтобы номер телефона соответствовал определенному шаблону используйте идеи из статьи Ввод данных по шаблону .
СОВЕТ : Чтобы найти все ячейки на листе, к которым применены правила Условного форматирования необходимо:
Надстройка PhoneNumbers предназначена для обработки номеров телефонов в таблице Excel, в выводом найденный (распознанных) номеров в отдельный столбец
Основное предназначение: Извлечение номеров телефонов из Excel файла.
Пример исходных данных и результата можно посмотреть в прикреплённом файле Excel (на первом листе — что есть изначально, на втором — то, что получается после обработки номеров телефонов)
То же самое показано на скриншоте (щелкните по нему для увеличения)
Особенности форматирования номеров телефонов:
- все номера телефонов приводятся к единому виду (89123456789)
- разделителем номеров считаются следующие символы: запятая, точка с запятой, точка, плюс, а также некоторые текстовые последовательности
- корректным номером телефона считается числовое значение, состоящее из 10 или 11 цифр
- к номерам из 10 цифр дописывается префикс (по умолчанию, используется префикс 8)
- количество обрабатываемых номеров не ограничено
- разделителем номеров телефонов в результате является точка с запятой + пробел
- повторяющиеся номера телефонов в одной ячейке заменяются одним (удаляются дубликаты)
- в отдельный столбец выводится первый найденный номер мобильного телефона (где номер начинается с 9)
В пробной версии надстройки PhoneNumbers, присутствуют ограничения:
- код надстройки закрыт от просмотра и редактирования
- некоторые цифры номера заменяются на символ *
В полной версии надстройки PhoneNumbers, этих ограничений нет:
- код надстройки открыт, вы можете изменять его как угодно
- нет привязки к компьютеру, и каких-либо ограничений по использованию
- стоимость надстройки PhoneNumbers составляет 420 рублей
Подключение надстройки к Excel выполняется как для всех обычных надстроек Excel
(через Файл — Параметры — Надстройки — Надстройки Excel — Перейти — Обзор. )
В этом случае одинарная кавычка показываться не будет, а будет показываться только плюс (ну или минус).
Рассмотрим еще один случай, который часто встречается, когда необходимо пронумеровать счета или счет-фактуры. В этих случаях часто приходится вводить числа с несколькими нулями впереди, например, 0004. В этом случае Excel их удаляет и автоматически преобразует число в 4.
Как в Excel номера телефонов привести к единому формату?
В загруженной в Excel базе столбец записан разными способами:
какой формулой форматировать в Эксель номер телефона, чтобы преобразовать к единому формату в 11 знаков начиная с цифры 7
Такие возможности описаны в большом количестве в сети. Главное, описать правильно, что хотите сделать со значением ячейки
Чтобы обработать телефонный номер надо ввести дополнительный столбец в который записать формулу для преобразования номеров телефонов в единый формат можно просто добавить цифру 7 к последним 10 цифрам телефона.
для ячейки A2 формула имеет вид:
однако очень часто в поле телефонного номера встречаются комментарии, в этом случае
Если слово пишется в графе единицы измерения, в этом случае формула будет выглядеть так:
Если требуется написать в тексте число и количество штук через пробел, в этом случае формула примет вид:
Обе формулы рабочие (можно копировать в свою таблицу, с заменой ячейки A1 на ячейку таблицы, в которой записано целое число определяющее количество штук)
Если слово надо писать в отдельную графу, то числа из ячейки A1 формула примет вид:
Для использования в тексте потребуется писать число, а потом слово, формула будет выглядеть так:
Эту формулу можно копировать отсюда в свою таблицу, но необходимо заменить 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:
Формулу можно скопировать отсюда в любую ячейку таблицы.
Как привести телефонные номера к единому формату?
Как это сделать?
Для того чтобы создать свой формат данных, переведите курсор в ячейку, к которой вы хотите применить пользовательский формат, и вызовите диалоговое окно Формат ячеек.
Как это работает?
На самом деле все очень просто. Excel использует несколько спецсимволов в масках форматов:
Как в экселе написать номер телефона 7
Добавьте код страны / города в список телефонных номеров с формулой
Добавить код страны / города в список номеров телефонов с помощью Kutools for Excel
После установки Kutools for Excel, сделайте следующее: （Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите все номера телефонов в списке, который вам нужен, и нажмите Kutools > Текст > Добавить текст. Смотрите скриншот:
2. в Добавить текст диалоговом окне введите код региона / страны, который вы хотите добавить, в Текст коробка и отметьте Перед первым персонажем вариант, вы можете предварительно просмотреть результаты на правой панели. Смотрите скриншот:
3. Нажмите Ok or Применить, и вы можете видеть, что каждый номер телефона в списке был добавлен с тем же кодом зоны.
Наконечник: Освободи Себя Добавить текст Утилита добавит код города или страны непосредственно в ячейки с телефонными номерами. В результате вам лучше вставить и сохранить исходные данные в другом месте, прежде чем применять эту утилиту.
Примечание: Работы С Нами Добавить текст of Kutools for Excel, вы также можете добавить одну и ту же текстовую строку в несколько позиций каждой ячейки, как показано ниже:
Номера телефонов в формате Excel
Форматирование телефонных номеров в Excel (Содержание)
Формат телефонных номеров в Excel
Возможно форматирование телефонных номеров в Excel. Это небольшая, но очень инновационная функция в Excel. В каждой стране свой код страны и формат написания телефонных номеров. В некоторых номерах номера телефонов разделены пробелами, в некоторых разрешены скобки для кода страны, в некоторых разница в длине номера, а в некоторых ставятся пробелы после кода страны, а остаток номера телефона следует последовательности.
Как отформатировать телефонные номера в Excel?
Форматирование телефонных номеров в Excel очень просто и удобно. Давайте разберемся с разными методами на нескольких примерах.
Давайте рассмотрим несколько случайных телефонных номеров для их форматирования. У нас есть номера телефонов длиной 10 символов, как показано ниже.
Как только мы нажмем кнопку ОК, мы увидим, что выбранные ячейки будут отформатированы в соответствии с типом, который мы исправили в поле Формат ячейки, как показано на снимке экрана ниже.
После того, как мы отформатировали несколько тестовых телефонных номеров, мы можем отформатировать любую ячейку, содержащую телефонные номера, в том же формате.
Существует еще один способ форматирования телефонных номеров в Excel. Давайте рассмотрим те же наборы образца телефонного номера, которые мы видели в примере-1. Теперь для форматирования телефонных номеров сначала выберите данные, затем сделайте прямо на них. И выберите параметры форматирования ячеек, как показано на скриншоте ниже.
Как мы видим ниже, формат, который мы установили в ячейках формата для телефонных номеров, теперь отражается на выбранных данных.
После того, как наш формат установлен, мы можем использовать его где угодно, когда видим, что телефонные номера должны быть отформатированы.
Чтобы проверить, виден ли наш недавно созданный формат телефонных номеров в ячейках формата или нет, сделайте это в любом месте на листе и выберите параметры Формат ячеек в нижней части меню. После этого появится то же самое окно ячеек формата. Перейдите на вкладку Number и выберите Custom option. Прокрутите вниз, чтобы найти наш недавно созданный формат.
Как мы можем видеть на скриншоте ниже, мы обвели форматы телефонных номеров, которые мы создали в примере 1 и примере 2.
Таким образом, мы можем создавать различные типы форматов телефонных номеров и использовать их в соответствии с нашими требованиями.
Плюсы формата телефонных номеров в Excel
Для данных большого телефонного номера становится очень легко понять код страны и фактический номер телефона, если данные правильно структурированы. Мы можем классифицировать и сортировать телефонные номера разных стран по их коду.
Читайте также: