Как конвертировать телефонные номера в Excel
Обновлено: 20.11.2024
При вводе телефонных номеров всегда предпочтительнее форматировать числа небольшими фрагментами, используя точки, дефисы, пробелы или круглые скобки. Большинство телефонных номеров могут содержать от 9 до 12 цифр.
Глядя на весь столбец из 12-значных чисел, глаза читателя могут немного затуманиться, поэтому всегда предпочтительнее представлять эти числа в более удобном для чтения формате, особенно когда числа включают международные и национальные коды.
В дополнение к проблеме удобочитаемости существует проблема автоматического форматирования. Допустим, у вас есть телефонный номер вроде «01873548923».
После того, как вы введете это число в ячейку листа Excel и нажмете клавишу возврата, вы обнаружите, что число автоматически отформатировано как «1873548923».
Это связано с тем, что Excel воспринимает это как обычный номер, а не как номер телефона. Поэтому он принимает начальный нуль за незначащий и автоматически удаляет его.
В этом руководстве по Excel мы покажем вам, как отформатировать диапазон ячеек, содержащих номера телефонов, в Excel, чтобы вам не приходилось вручную форматировать каждую ячейку с помощью дефисов, пробелов или скобок.
После этого мы покажем, как вы можете создавать пользовательские форматы номеров, чтобы ваши телефонные номера соответствовали соглашениям о форматировании вашей страны или любой другой страны.
Оглавление
Форматирование телефонных номеров во встроенный формат Excel
Предположим, у вас есть набор данных, как показано ниже, где столбец B содержит телефонные номера, которые необходимо отформатировать.
Ниже приведены шаги по форматированию этих телефонных номеров в Excel:
- Выберите ячейку или диапазон ячеек, которые необходимо отформатировать.
- На вкладке Главная выберите раскрывающийся список форматирования в группе "Число". Затем выберите "Другие числовые форматы". Кроме того, вы можете просто щелкнуть правой кнопкой мыши выбранный элемент и выбрать «Форматировать ячейки» в появившемся всплывающем меню.
- Откроется диалоговое окно Формат ячеек. Выберите вкладку "Число" и в списке Категорий выберите вариант "Особые".
- Теперь вы увидите список параметров форматирования «Тип» в правой части диалогового окна.
- Выберите из списка вариант «Номер телефона» и нажмите «ОК».
Теперь вы найдете все свои телефонные номера, отформатированные скобками, пробелами и дефисами, в нужных местах, без необходимости вводить их вручную.
Обратите внимание, что такое форматирование телефонных номеров изменяет только их внешний вид на листе Excel. Это не меняет характер или значение фактического базового числа.
Итак, даже если у вас есть такие символы, как дефисы, пробелы и круглые скобки, ваши ячейки по-прежнему являются числовыми ячейками, они не преобразованы в текст.
Это также видно из выравнивания всех чисел вправо, а не влево. Мы знаем, что числа всегда выравниваются по правому краю, а текст всегда выравнивается по левому краю.
Форматирование телефонных номеров в произвольном формате
Теперь описанный выше метод дает вашим номерам общий формат телефонных номеров.
Однако, если этот встроенный формат телефонных номеров вам не подходит, вы также можете создать свой собственный формат!
Допустим, вместо формата (XXX) XXX-XXXX вы хотите отображать числа, просто разделенные точками, как в XXX.XXX.XXXX. Вот что вам нужно сделать:
- Выберите ячейку или диапазон ячеек, которые необходимо отформатировать.
- На вкладке Главная выберите раскрывающийся список форматирования в группе "Число". Затем выберите «Другие числовые форматы». Кроме того, вы можете просто щелкнуть правой кнопкой мыши выбранный элемент и выбрать «Форматировать ячейки» в появившемся всплывающем меню.
- Откроется диалоговое окно Формат ячеек. Выберите вкладку "Число" и в списке Категорий выберите вариант "Пользовательская".
- Теперь вы увидите список параметров форматирования Тип в правой части диалогового окна. Выберите встроенный формат, который больше всего похож на тот, который вы хотите создать. Например, 0,00. Выбранный формат появится в поле ввода Тип.
- Измените выбранный числовой формат в поле "Тип", чтобы создать именно тот формат, который вам нужен. В нашем случае нам нужны точки после каждых трех цифр, поэтому измените его на: 000\.000\.0000
- Нажмите "ОК".
При этом будет создан новый пользовательский формат номера, и после нажатия кнопки "ОК" вы найдете все свои телефонные номера, отформатированные в соответствии с вашими требованиями.
Вы можете спросить, почему мы добавили «\» перед каждой точкой в нашем формате.
Причина этого в том, что Excel воспринимает точки как десятичные дроби, а не просто символы. Итак, если вы поставите точку ('.') без обратной косой черты ('\'), Excel начнет добавлять десятичную дробь после всех цифр телефонного номера, а это не то, что нам нужно.
Обратная косая черта ('\') называется экранирующим символом. Он используется для экранирования следующего символа.
Это означает, что он используется для указания того, что следующий символ не должен рассматриваться как десятичный. Скорее, его следует рассматривать как специальный символ или символ.
Обратите внимание, что настраиваемые числовые форматы, которые вы создаете в одной книге, недоступны в других книгах. Поэтому вам придется снова создать формат, если вы хотите использовать его в новой книге.
Формат международного и местного кода для номеров США
В разных странах приняты разные правила написания телефонных номеров.
Существуют международные стандарты форматов телефонных номеров, например, выпущенные Международным союзом электросвязи.
Также существуют разные национальные планы телефонной нумерации, и они различаются в зависимости от страны.
Согласно стандарту ITU E.123, номер телефона в США должен быть указан в формате +1 XXX XXX XXXX для международного контекста и (0XXX) XXX XXXX для местного контекста.
Существует также псевдоформат, который объединяет оба вида: +XX (XXX) XXX XXXX
Давайте посмотрим, как ваш пользовательский числовой формат будет выглядеть для каждой из этих форм:
- Общая форма: +00 (000) 0000000
- Для национальных контекстов: (\0000) 0000000
- Для международного контекста: +10000000000
+1 означает международный код доступа для США.
Как упоминалось ранее, мы добавили косую черту ('\') перед нулем для формы с национальным контекстом, потому что мы хотим, чтобы Excel воспринимал первый ноль как специальный символ, а не как заполнитель.
Формат международного и местного кода для номеров Великобритании
Для Соединенного Королевства числовое форматирование выглядит следующим образом:
- Для мегаполисов: (0XXX) XXX XXXX
- Для других регионов: (0XXXX) XXXXXX или (0XXX XX) XXXX.
- Для международного контекста: +44 XXXX XXXXXX
+44 означает международный код доступа для США.
Ваш собственный формат для каждой из вышеуказанных форм будет следующим:
- Для мегаполисов: (\0000) 0000000
- Для других регионов: (\00000) 000000 или (\000000) 0000
- Для международного контекста: +440000000000
Давайте рассмотрим форматы телефонных номеров еще для нескольких стран:
Международный и местный кодовый формат для Индии
Номер стационарного телефона в Индии будет иметь формат XXX-XXXXXXX, тогда как номер мобильного телефона будет иметь формат XXXXX-XXXXX. В международном контексте мобильные номера могут иметь формат +91. -ХХХХХ ХХХХХ.
+91 означает международный код доступа для Индии. Таким образом, ваши пользовательские форматы будут такими:
- Для стационарных номеров: 000–0000000
- Для мобильных номеров (местный контекст): 00000–00000
- Для мобильных номеров (международный контекст): +91-0000000000
Форматы телефонных номеров в других странах
Глядя на приведенные выше форматы, довольно легко увидеть, что независимо от того, номер телефона какой страны вы хотите отслеживать, все, что вам нужно сделать, это:
- Представляйте каждую цифру номера телефона с помощью "0".
- Расположите другие символы так, как они есть в исходном формате.
- Если формат требует использования каких-либо специальных символов, таких как точка ('.') или ноль ('0'), представьте его с предшествующим escape-символом ('\').
Это все, теперь вы можете представлять свои телефонные номера практически в любом формате, который вам нужен, используя Пользовательские форматы номеров.
Заключение
В этом руководстве мы показали, как преобразовать телефонные номера в формат, встроенный в Excel.
Мы также показали вам, как вы можете создавать свои собственные пользовательские форматы телефонных номеров, чтобы вы могли автоматически настроить все свои телефонные номера в соответствии с любым соглашением о форматировании для любой страны.
Иногда при импорте номеров телефонов из разных источников номера телефонов имеют разные форматы. В других случаях, чтобы иметь презентабельные и единообразные данные, номера телефонов могут нуждаться в очистке и форматировании.
Сначала мы обсудим, как удалить нежелательные символы из номера телефона.
Как очистить телефонные номера
Номера телефонов могут иметь разные форматы и содержать множество различных нечисловых символов. Функцию ПОДСТАВИТЬ можно использовать для удаления нечисловых символов следующим образом:
Функция ПОДСТАВИТЬ заменяет указанный символ другим символом. В приведенном выше примере символы, указанные в столбце номера телефона, заменены пробелами.
Примечание. Знак «+0» в конце формулы преобразует очищенные телефонные номера в числовой формат. Вместо этого вы можете выполнить любую другую математическую операцию или использовать функцию ЗНАЧЕНИЕ.
Мы вложили несколько функций ПОДСТАВКИ в одну формулу, чтобы сразу удалить все нежелательные символы.
Вместо этого вы можете разделить каждую функцию на отдельные ячейки следующим образом:
Форматирование телефонных номеров с помощью функции ТЕКСТ
После того, как телефонные номера были очищены, нам нужно будет их отформатировать. Одним из способов форматирования строк телефонных номеров является использование функции ТЕКСТ.
Функция ТЕКСТ форматирует ячейку на основе указанного формата. В приведенном выше примере указанный формат показывает, что первые 3 цифры (код города) должны быть в круглых скобках, а остальные 7 цифр должны быть разделены дефисом после третьей цифры.
Функция ТЕКСТ преобразует телефонные номера в текст. Это может быть или не быть тем, что вы хотите. Ниже мы обсудим способ сохранения телефонных номеров в виде чисел при обновлении форматирования.
Форматирование номеров телефонов с помощью пользовательского форматирования номеров
Еще один способ форматирования телефонных номеров — использование пользовательского форматирования номеров в Excel. Использование пользовательского форматирования дает нам возможность указать формат, который мы хотим для выбранного диапазона.
Примечание. Пользовательское форматирование чисел изменяет только способ отображения чисел. Это не изменяет фактическое содержимое ячейки.
Сначала мы выбираем ячейки для форматирования, а затем нажимаем Ctrl + 1. Это вызывает диалоговое окно форматирования ячеек, которое мы видели выше.
Выберите «Пользовательский», после чего вы увидите доступные примеры параметров или можете ввести собственное форматирование.
Этот метод также является отличным вариантом, чтобы найти нужное форматирование перед использованием метода TEXT Function.
Телефонные номера бывают разных форм, обычно в сочетании с другими символами, такими как дефисы, точки и круглые скобки. Работая с Excel, мы можем чистить телефонные номера с помощью функции ПОДСТАВИТЬ. Это пошаговое руководство поможет пользователям Excel любого уровня очистить и отформатировать телефонные номера.
Рисунок 1. Конечный результат: очистка и переформатирование телефонных номеров
Окончательная формула: =ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5,"-",""),".",""),"(",""),")","") ," ","")+0
Синтаксис функции ПОДСТАВИТЬ
Функция ПОДСТАВИТЬ заменяет символ или текст в текстовой строке
= ПОДСТАВИТЬ (текст, старый_текст, новый_текст, [номер_экземпляра])
- текст – текстовая строка, содержащая символ или текст, который мы хотим заменить или изменить
- old_text — любой текст, который мы хотим заменить на new_text
- new_text — текст, который мы хотим заменить old_text
- instance_num – Необязательно ; Указывает, какое вхождение old_text мы хотим заменить; Если этот параметр опущен, каждое вхождение old_text в тексте заменяется на new_text
Настройка наших данных
В нашей таблице есть три столбца: номер телефона (столбец A), чистая версия (столбец B), переформатированный (столбец C). Обратите внимание, что три перечисленных телефонных номера имеют одинаковые номера, но с разными символами: тире «-», точка «.», круглые скобки «()» и пробелы.
В столбце B мы хотим получить чистую версию телефонных номеров, только с числовыми цифрами, без других символов. В столбце C мы хотим отформатировать числа в соответствии с нашими предпочтениями.
Рисунок 2. Пример данных для очистки и переформатирования телефонных номеров
Использование функции ПОДСТАВИТЬ
Прежде чем мы перейдем к окончательной формуле очистки телефонных номеров, давайте сначала узнаем, как использовать функцию ПОДСТАВИТЬ.
Ниже приведен синтаксис функции ПОДСТАВИТЬ:
= ПОДСТАВИТЬ (текст, старый_текст, новый_текст, [номер_экземпляра])
Например, мы хотим заменить тире «-» в ячейке B4 пустой строкой. Вводим формулу в ячейку C4: =( ПОДСТАВИТЬ ( B4 , , – “, “” ))
Рисунок 3. Ввод формулы с помощью ПОДСТАВКИ
Результат в ячейке C4 – 2 221 234 567, где дефисы убраны и остались только числовые цифры.
Чистые телефонные номера
Мы хотим получить только числовые цифры наших телефонных номеров и исключить другие символы. Ключевым моментом здесь является определение символов, которые мы хотим исключить и заменить пустой строкой. Глядя на номера телефонов, мы хотим заменить тире «-», точку «.», открывающую и закрывающую скобки «(« и «)» и пробел с пустой строкой «».
Мы будем использовать одну функцию ПОДСТАВИТЬ для каждого символа, который мы хотим заменить пустой строкой. Чтобы очистить наши телефонные номера, мы выполняем следующие действия:
Шаг 1 . Выберите ячейку C5
Шаг 2 . Введите формулу: =ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5, "-",""),".",""),"(",""),")","")," ","")+0
Шаг 3. Нажмите клавишу ВВОД
Шаг 4. Скопируйте формулу из ячейки C5 в ячейку C6, нажав значок «+» в правом нижнем углу ячейки C5 и перетащив ее вниз
Рисунок 4. Использование ЗАМЕНЫ для очистки телефонных номеров
Наша формула представляет собой комбинацию пяти функций ПОДСТАВИТЬ. Первая функция ПОДСТАВИТЬ становится текстом для второй функции ПОДСТАВИТЬ и так далее. Дополнительный «+ 0» в конце формулы преобразует телефонные номера в числовой формат. Это особенно полезно, когда мы хотим переформатировать наши телефонные номера.
В ячейках C5 и C6 мы успешно очистили телефонные номера, оставив только числовые цифры 2221234567.
Переформатировать телефонные номера
Сначала скопируем формулы ячеек C4:C6 в ячейки D4:D6.
Рисунок 5. Копирование формул из столбца C в столбец D
Чтобы отформатировать телефонные номера в соответствии с нашими предпочтениями, выполните следующие действия:
Шаг 1 . Выберите ячейки D4:D6
Шаг 2 . Нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек».
Шаг 3. В диалоговом окне "Формат ячеек" нажмите "Пользовательский"
Шаг 5 . Нажмите "ОК".
Рисунок 6. Ввод пользовательского формата
Формат можно настроить по своему усмотрению. Мы можем добавить другие символы или изменить цвет цифр.
Рис. 7. Вывод: переформатирование телефонных номеров
В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.
Это сообщение не ответило на ваш вопрос? Получите решение, связавшись с экспертом.
Здравствуйте, у меня есть большой список адресов электронной почты, имен, фамилий и номеров телефонов, которые мне нужно сопоставить с другим списком адресов электронной почты.
Здравствуйте, у меня есть список телефонных номеров, и мне нужно добавить цифру 1 к телефонным номерам. Он настроен как 1112223333, и мне нужно, чтобы он выглядел как 1-(111)-222-3333
У меня есть список телефонных номеров в одном файле, и я хочу удалить их из основного списка в другом файле. можно пожалуйста?
Махеш Маквана
Махеш Маквана
Писатель
Махеш Маквана — технический писатель-фрилансер, специализирующийся на написании практических руководств. Он пишет технические учебники уже более десяти лет. Он написал для некоторых известных технических сайтов, включая MakeUseOf, MakeTechEasier и Online Tech Tips. Подробнее.
Хотите преобразовать цифры в своих электронных таблицах в правильные телефонные номера США с кодами городов? Вместо того чтобы вручную вставлять дефисы и круглые скобки, используйте параметр форматирования номера телефона в Microsoft Excel.
В Excel вы можете форматировать данные различными способами. Одно из таких форматов предназначено для телефонных номеров в США, превращая ваши номера, такие как «5555551234», в удобочитаемый «(555) 555-1234». При желании вы можете применить это форматирование к нескольким телефонным номерам одновременно.
Как применить форматирование номера телефона в Microsoft Excel
Для начала откройте электронную таблицу в Microsoft Excel.
В электронной таблице выберите ячейку или ячейки, в которых у вас есть номера телефонов.
Пока ваши номера телефонов выделены, на ленте Excel вверху нажмите вкладку "Главная".
На вкладке "Главная" в разделе "Число" нажмите "Числовой формат" (значок стрелки) в правом нижнем углу.
Вы увидите окно «Формат ячеек». Здесь в списке «Категория» слева выберите «Особые».
В разделе "Тип" справа нажмите "Номер телефона". Затем нажмите «ОК» внизу.
Примечание. Если вы не видите «Номер телефона» в разделе «Тип», щелкните раскрывающееся меню «Язык» и выберите «Английский (США)» или другую поддерживаемую страну.
В электронной таблице все ваши телефонные номера теперь отформатированы в правильном стиле.
Если вам нужен другой формат для этих чисел, в окне «Формат ячеек» выберите «Пользовательский», а затем укажите собственный формат.
Вот как вы превращаете обычные цифры в телефонные номера в Microsoft Excel. Очень полезно!
Нужно добавить ноль перед числом в электронной таблице Excel? Если это так, есть простой способ сделать это.
- › Сколько оперативной памяти требуется вашему ПК?
- › 5 вещей, которые вы, вероятно, не знали о GIF-файлах
- › Что такое GrapheneOS и как она делает Android более приватным?
- › 7 основных функций Microsoft Excel для составления бюджета
- › Matter — это долгожданный стандарт умного дома
- › Почему СМС должен умереть
Читайте также: