Как отделить код города от номера телефона в Excel?
Если у вас есть список телефонов клиентов на листе Excel и вы хотите анализировать или группировать их по географическим регионам, отделение кодов городов от полных номеров становится необходимым. Это особенно полезно при фильтрации, сортировке или агрегировании данных по кодам городов для маркетинга, логистики или составления региональной отчётности. Excel предлагает несколько эффективных способов извлечения кодов городов — выбор оптимального метода зависит от структуры ваших данных и личных предпочтений. Ниже представлены три практических подхода к выделению кодов городов из номеров телефонов в Excel, каждый со своими преимуществами и идеальными сценариями применения.
Отделение кода города от Номер телефона с помощью формулы в Excel
Отделение кода города от Номер телефона с помощью функции «Текст по столбцам»
Код VBA — автоматическое извлечение кодов городов из Номер телефона
Отделение кода города от Номер телефона с помощью формулы в Excel
Этот метод идеально подходит, если ваши номера телефона всегда следуют одному и тому же формату — например, стандартным североамериканским номерам, где код города всегда занимает первые три цифры. Формула проста в применении, работает быстро и отлично справляется с небольшими и средними списками.
Обычно код города представлен первыми тремя символами в строке номера Номер телефона — независимо от наличия дефисов или скобок. В простых случаях, когда каждый номер начинается с кода города, можно использовать следующую формулу:
1. Выберите ячейку, в которую нужно поместить извлечённый код города (например, C1, если номер телефона находится в B1). Введите следующую формулу:
=MID(B1,1,3)2. Нажмите клавишу Enter, чтобы подтвердить ввод, — и код города появится. Чтобы быстро применить это ко всему списку, перетащите маркер заполнения из правого нижнего угла ячейки вниз по столбцу для всех соответствующих номеров телефона.
Примечание: Если ваши номера «Номер телефона» содержат форматирование — пробелы, дефисы или скобки (например, «(123) 456-7890» или «123-456-7890»), формулу может потребоваться адаптировать под эти форматы. Вот пример формулы для номеров «Номер телефона» со скобками:
=MID(B1,2,3)Вы можете настроить начальную позицию и длину в соответствии с форматом ваших данных. Обязательно проверьте несколько образцов, чтобы убедиться, что формула извлекает только нужный код города. Если в одном наборе данных встречаются разные форматы, это может потребовать дополнительной логики или предварительной очистки.
Если формула не извлекает ожидаемый код города, проверьте исходный формат ваших номеров телефона и скорректируйте формулу соответствующим образом. Например, вы можете использовать такие функции, как LEFT(), RIGHT() или SEARCH(), чтобы обрабатывать более сложные шаблоны данных.
Отделение кода города от Номер телефона с помощью функции «Текст по столбцам» в Excel
Этот метод удобен, если все номера телефона имеют одинаковую длину или если код города и остальная часть номера всегда расположены на одних и тех же позициях. Инструмент «Текст по столбцам» позволяет разделять данные либо по фиксированной ширине, либо с использованием заданного разделителя. Он подходит для наборов данных среднего размера, но не рекомендуется при сильно различающихся форматах телефонных номеров или когда требуется масштабная очистка данных.
1. Выделите столбец, содержащий список ваших номеров телефона. Перейдите на вкладку Данные на ленте и нажмите Текст по столбцам. См. снимок экрана:
2. В мастере преобразования текста в столбцы выберите опцию Фиксированная ширина и нажмите кнопку Далее. Этот вариант подходит, когда код города всегда имеет одинаковую длину и расположен в начале номера. См. снимок экрана:
3. На следующем экране задайте ширину поля, щёлкнув для установки линии разрыва сразу после кода города (для трёхзначного кода — щёлкните после третьего символа). При необходимости переместите или скорректируйте линию разрыва в соответствии с форматом ваших данных. См. снимок экрана:
Если ваши номера имеют разное форматирование или длину, используйте режим «С разделителями» и укажите конкретный разделитель — например, пробел, дефис или скобки.
4. Нажмите кнопку Готово. Столбец будет разделён, и коды городов окажутся в отдельном столбце — это упростит их анализ и группировку по кодам.
Совет: Всегда проверяйте предварительный просмотр в мастере перед завершением. Если данные отображаются некорректно, вернитесь назад и скорректируйте линию разрыва или параметры разделителя. Обязательно сохраните резервную копию листа перед использованием функции «Текст по столбцам» — она перезаписывает данные в соседних столбцах!
Код VBA — автоматическое извлечение кодов городов из Номер телефона
Для больших наборов данных или списков с нестандартными форматами номеров Номер телефона написание простого макроса VBA предоставляет масштабируемый способ эффективного извлечения кодов городов. Такой подход особенно ценен, когда коды городов могут присутствовать со скобками, дефисами, пробелами или другими нестандартными символами либо без них. VBA позволяет реализовать индивидуальную логику извлечения и лучше справляется с исключениями по сравнению с формульными методами.
1. Нажмите вкладку Разработчик на ленте Excel, затем выберите команду Visual Basic, чтобы открыть редактор VBA. В новом окне Microsoft Visual Basic для приложений нажмите Вставка > Модуль. Скопируйте и вставьте следующий код в модуль:
Sub ExtractAreaCodes()
Dim cell As Range
Dim phoneStr As String
Dim areaCode As String
Dim regEx As Object
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = "\(?(\d{3})\)?[-\s.]?"
regEx.Global = False
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set phoneRange = Application.InputBox("Select phone number range", xTitleId, "", Type:=8)
For Each cell In phoneRange
phoneStr = cell.Value
If regEx.Test(phoneStr) Then
areaCode = regEx.Execute(phoneStr)(0).SubMatches(0)
cell.Offset(0, 1).Value = areaCode
Else
cell.Offset(0, 1).Value = ""
End If
Next cell
Set regEx = Nothing
End Sub2. Чтобы запустить макрос, нажмите кнопку 
в редакторе VBA. Появится диалоговое окно с запросом на выбор диапазона, содержащего ваши номера «Номер телефона». Макрос автоматически извлечёт коды городов и поместит их в столбец сразу справа от ваших номеров — например, если номера «Номер телефона» находятся в столбце B, коды городов появятся в столбце C.
Совет: Этот код корректно обрабатывает номера телефонов — как содержащие, так и не содержащие скобки, дефисы, пробелы или точки сразу после кода города. Если формат номеров ещё сложнее, вы можете дополнительно настроить регулярное выражение в коде. Всегда проверяйте результат и вручную исправляйте случаи, когда извлечение не удалось, например номера без кодов городов или с нестандартными шаблонами.
См. также:
Лучшие инструменты повышения продуктивности в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек