Как преобразовать формат телефонного номера в цифры в Excel?

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

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

Преобразование формата телефонного номера в цифры с помощью формулы
Используйте функцию быстрого заполнения для извлечения цифр из отформатированных телефонных номеров
Преобразование формата телефонного номера в цифры с помощью Kutools для Excel
Использование макроса для удаления всех нецифровых символов из телефонных номеров

Преобразование формата телефонного номера в цифры с помощью формулы

Формулы Excel позволяют удалять нежелательные символы и оставлять только цифры в вашем списке телефонных номеров. Этот метод особенно полезен, если вы предпочитаете не использовать надстройки или программирование, и хорошо работает с телефонными номерами, следующими за согласованной формой, такой как (123)456-7890 или 123-456-7890. Однако, если ваши данные содержат дополнительные нестандартные символы или буквы, могут потребоваться более продвинутые техники.

1. Выберите пустую ячейку (например, C2), где вы хотите показать извлеченные цифры. Введите следующую формулу и нажмите Enter:

=--ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2,"(",""),")","")," ",""),"-","")

Примечание: В этой формуле A2 является ссылкой на ячейку, содержащую исходный телефонный номер. Вы можете изменить эту ссылку, если ваши данные находятся в другом столбце или строке.

2. Удерживая выбор ячейки C2, перетащите маркер заполнения (маленький квадратик в нижнем правом углу ячейки) вниз, чтобы применить формулу для остальной части вашего списка (например, от C2 до C8). Это применит формулу к каждому телефонному номеру в диапазоне.

Теперь каждый телефонный номер в вашем диапазоне данных должен быть преобразован в последовательность только из цифр.

Используйте функцию быстрого заполнения для извлечения цифр из отформатированных телефонных номеров

Функция быстрого заполнения Excel предлагает умный, ручной альтернативный способ извлечения цифр из телефонных номеров — особенно эффективный при работе с согласованными форматами списков или когда вы хотите быстрый способ без использования формул. Быстрое заполнение оценивает шаблоны на основе вашего ручного ввода, затем заполняет остальную часть колонки в соответствии с вашим примером.

1. Предположим, что ваши телефонные номера находятся в столбце A, начиная с ячейки A2. В соседнем столбце (B2) вручную введите только цифры из вашего первого телефонного номера, пропуская любые символы или пробелы (например: 1234567890).

2. Перейдите к ячейке B3. Начните вводить цифры из следующего телефонного номера. Как только вы начнете, Excel автоматически определит шаблон и покажет предложенное автозаполнение (подсвечено серым цветом ниже введенного значения).

3. Нажмите Enter, чтобы принять предложение быстрого заполнения, и Excel заполнит вниз по столбцу извлеченные цифры для всех соответствующих строк.

Преобразование формата телефонного номера в цифры с помощью Kutools для Excel

Для пользователей, ищущих более удобное, интерактивное решение, утилита Remove Characters в Kutools для Excel может эффективно удалять указанные символы — включая тире, пробелы, скобки и многое другое — из выбранных ячеек. Этот подход идеален для тех, кто часто обрабатывает структурированные данные и хочет быстрый, управляемый через меню метод очистки телефонных номеров без написания формул или кода.

1. Выберите диапазон, содержащий телефонные номера, которые вы хотите преобразовать в цифры, затем перейдите к Kutools > Текст > Удалить определенные символы.

2. В диалоговом окне Удалить определенные символы выберите опцию Пользовательский и введите тире – в текстовое поле. Вы также можете указать другие символы, которые хотите удалить, такие как пробелы, скобки или точки, добавив их в поле. После настройки нажмите OK для обработки диапазона.

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

Использование макроса для удаления всех нецифровых символов из телефонных номеров

Если ваша задача включает много телефонных номеров в смешанных или непредсказуемых форматах (включая специальные символы, пробелы, лишнюю пунктуацию, текст или даже коды стран), макросы 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 Sub

2. И нажмите кнопку Выполнить (или нажмите F5). В появившемся диалоговом окне подтвердите или выберите свой диапазон данных. Макрос автоматически удалит все нецифровые символы из каждой ячейки вашего выбора — оставляя только чистую числовую последовательность.

Связанные статьи:

• Как отформатировать телефонный номер с добавочным номером в Excel?