Как преобразовать телефонный номер в числовой формат в Excel?
При работе с листами Excel, содержащими списки Номер телефона, вы часто сталкиваетесь с различными стилями форматирования, такими как скобки, пробелы или тире. Эти недесятичные символы могут мешать работе Анализ данных, импорту в системы или автоматическому набору, для которых требуется, чтобы Номер телефона представляли собой чистую последовательность цифр. Если вам нужно стандартизировать данные, извлекая только цифры из любого форматированного Номер телефона, в этой статье приведено несколько практических решений для эффективного выполнения этой задачи в Excel.
Вот пример ситуации, с которой вы можете столкнуться: в самом левом столбце указаны номера телефонов в разных форматах, а в правом — желаемый результат: номер телефона, из которого удалены все недесятичные символы, оставляя непрерывную строку цифр.
Преобразование формата Номер телефона в цифры с помощью формулы
Использование «Быстрого заполнения» для извлечения цифр из форматированных Номер телефона
Преобразование формата Номер телефона в цифры с помощью Kutools для Excel
Использование макроса для удаления всех недесятичных символов из Номер телефона
Преобразование формата Номер телефона в цифры с помощью формулы
Формулы Excel помогут удалить нежелательные символы и оставить в вашем списке «Номер телефона» только цифры. Этот метод особенно удобен, если вы не хотите использовать надстройки или программирование, и отлично справляется с номерами телефонов, имеющими единообразный формат, например (123)456-7890 или 123-456-7890. Однако если ваши данные содержат дополнительные нестандартные символы или буквы, могут понадобиться более продвинутые методы.
1. Выберите пустую ячейку (например, C2), в которую хотите поместить извлечённые цифры. Введите следующую формулу и нажмите Enter:
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","")," ",""),"-","")
Примечание: В этой формуле A2 — ссылка на ячейку с исходным номером телефона. Вы можете изменить эту ссылку, если ваши данные находятся в другом столбце или строке.
2. Не снимая выделения с ячейки C2, перетащите маркер заполнения (маленький квадрат в правом нижнем углу ячейки) вниз до конца списка (например, до ячейки C8), чтобы применить формулу ко всем номерам телефона в этом диапазоне.
Теперь каждый номер телефона в вашем диапазоне данных должен быть преобразован в последовательность только из цифр.
Использование «Быстрого заполнения» для извлечения цифр из форматированных Номер телефона
Функция «Быстрое заполнение» в Excel предлагает интеллектуальный ручной способ извлечения цифр из номера телефона — особенно эффективный при работе со списками в единообразном формате или когда нужен быстрый результат без использования формул. «Быстрое заполнение» распознаёт шаблоны на основе вашего примера и автоматически заполняет оставшиеся ячейки столбца в том же стиле.
1. Предположим, что ваши номера телефонов находятся в столбце A. В ячейке B2 вручную введите только цифры из первого номера телефона, опуская любые символы или пробелы (например: 1234567890).
2. Перейдите в ячейку B3 и начните вводить цифры следующего номера телефона. Как только вы начнёте ввод, Excel автоматически распознает шаблон и предложит автозаполнение (оно будет выделено серым цветом под введённым значением).
3. Нажмите Enter, чтобы принять предложение «Быстрого заполнения», и Excel автоматически заполнит столбец извлечёнными цифрами для всех соответствующих строк.
Совет: Если функция «Быстрое заполнение» не срабатывает автоматически, вы можете активировать её вручную: выделите заполненную ячейку и нажмите сочетание клавиш Ctrl+E или выберите Данные > Быстрое заполнение на ленте.
Мера предосторожности: Всегда проверяйте точность полученных результатов, особенно если форматы номеров телефона различаются. «Быстрое заполнение» может работать некорректно со списками, содержащими непредсказуемые пробелы, смешанное форматирование или встроенный текст.
Рекомендация по итогам: «Быстрое заполнение» — отличный способ быстро обработать разовые или небольшие наборы данных, но оно не обновляется автоматически при изменении исходных данных. Для постоянно обновляемых списков лучше использовать формулы или методы VBA.
Преобразование формата Номер телефона в цифры с помощью Kutools для Excel
Для пользователей, которым нужно более удобное интерактивное решение, утилита Удалить определенные символы в Kutools для Excel позволяет эффективно удалять указанные символы — включая тире, пробелы, скобки и другие — из выделенных ячеек. Этот подход идеально подходит тем, кто часто работает со структурированными данными и хочет быстро очистить номер телефона через меню, не прибегая к написанию формул или кода.
1. Выделите диапазон, содержащий номера телефонов, которые необходимо преобразовать в цифры, затем перейдите в меню Kutools > Текст > Удалить определенные символы.
2. В диалоговом окне Удалить определенные символы выберите опцию Пользовательская и введите дефис – в текстовое поле. Вы также можете указать другие символы для удаления — например, пробелы, скобки или точки — просто добавив их в это поле. После настройки нажмите ОК, чтобы обработать диапазон.
Выделенные номера телефонов будут немедленно обновлены: указанные символы удалятся, оставив чистую последовательность цифр, как показано ниже.
Если вы хотите воспользоваться бесплатной пробной версией (30 дней) этой утилиты, нажмите, чтобы скачать её, а затем выполните операцию в соответствии с приведёнными выше шагами.
Использование макроса для удаления всех недесятичных символов из Номер телефона
Если ваша задача предполагает обработку множества телефонных номеров в разнообразных или непредсказуемых форматах — со специальными символами, пробелами, дополнительными знаками препинания, текстом или даже кодами стран — макросы VBA в Excel обеспечивают гибкий и полностью автоматический способ очистки всех таких записей сразу. Этот подход особенно эффективен при работе с большими объёмами данных или при регулярном выполнении повторяющихся задач очистки.
Применимый сценарий: Используйте VBA, если форматы ваших номеров телефонов неоднородны, строк для обработки много или ни формулы, ни встроенные средства не справляются с задачей полностью. Однако учтите: чтобы использовать макросы, книгу нужно сохранить в формате с поддержкой макросов (*.xlsm) и предоставить разрешение на их выполнение.
Шаги:
1. Нажмите Alt+F11, чтобы открыть редактор Visual Basic for Applications. В новом окне выберите Вставка > Модуль, чтобы создать пустой модуль, затем скопируйте и вставьте следующий код VBA в окно кода:
Sub StripNonDigitsFromPhoneNumbers()
Dim xRg As Range
Dim xCell As Range
Dim xDigits As String
Dim i As Integer
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set xRg = Application.Selection
Set xRg = Application.InputBox("Select the range with phone numbers", xTitleId, xRg.Address, Type:=8)
For Each xCell In xRg
xDigits = ""
For i = 1 To Len(xCell.Value)
If Mid(xCell.Value, i, 1) Like "#" Then
xDigits = xDigits & Mid(xCell.Value, i, 1)
End If
Next i
xCell.Value = xDigits
Next xCell
End Sub2. Затем нажмите кнопку 
Выполнить(или нажмите)F5). В появившемся диалоговом окне подтвердите или выберите ваш диапазон данных. Макрос автоматически удалит все недесятичные символы из каждой ячейки в выделенном диапазоне, оставив только чистую последовательность цифр.
Пояснение параметров: Этот макрос обрабатывает каждый символ в каждой ячейке, оставляя в результате только цифры. Перед запуском убедитесь, что выделена вся нужная область — все требуемые столбцы и строки.
Советы и напоминания об ошибках: Всегда сохраняйте книгу перед первым запуском скриптов VBA — изменения нельзя отменить одним щелчком. Если появится предупреждение о безопасности макросов, проверьте источник кода и при необходимости включите макросы. При совместной работе будьте осторожны, чтобы случайно не перезаписать важные данные.
Рекомендация по итогам: После выполнения макроса быстро проверьте очищенные данные на точность. Если в номерах есть коды стран или добавочные номера, учтите: они будут объединены в одну строку цифр. При необходимости обрабатывайте такие элементы отдельно.
Связанные статьи:
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек