Как извлечь почтовый индекс из списка адресов в Excel?
При управлении информацией о клиентах в Excel часто встречается список полных адресов, где каждая запись содержит как детали улицы, так и почтовый индекс. Если вам нужно выделить и извлечь только почтовые индексы из этих адресов — возможно, для рассылок, регионального анализа или форматирования данных — ручное разделение их по одному может быть утомительным и подверженным ошибкам, особенно при работе с большими наборами данных. К счастью, Excel предлагает практичные способы быстро и эффективно извлечь несколько почтовых индексов сразу, без ручного труда. В этой статье представлены пошаговые инструкции по извлечению почтовых индексов из адресов с использованием формулы Excel и решения на основе макроса VBA.
Извлечение почтового индекса с помощью формулы в Excel
Извлечение почтового индекса с помощью пользовательской функции в Excel
Извлечение почтового индекса с помощью формулы в Excel
Во многих распространенных списках адресов почтовый индекс расположен в конце строки адреса и не превышает 8 символов. Этот метод наиболее подходит, когда все адреса в вашем списке следуют последовательному формату, и почтовый индекс появляется как последний элемент каждого адреса, отделенный пробелами.
Чтобы быстро извлечь почтовый индекс с помощью формулы, выполните следующие шаги:
1. Выберите пустую ячейку, где должен отображаться почтовый индекс (например, B1, если ваш адрес находится в A1). Введите следующую формулу:
=MID(A1,FIND("zzz",SUBSTITUTE(A1," ","zzz",SUMPRODUCT(1*((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=" "))-1))+1,LEN(A1))2. Нажмите клавишу Enter. Почтовый индекс из адреса в A1 будет отображен в выбранной ячейке.
3. Чтобы применить эту формулу к другим адресам, выберите ячейку с формулой, перетащите маркер заполнения вниз по столбцу, чтобы охватить все строки адресов, и Excel автоматически извлечет почтовый индекс для каждого адреса.
Совет: Эта формула предполагает, что почтовый индекс является последним элементом после последнего пробела в строке адреса. Если ваши адреса структурированы иначе, например, почтовые индексы находятся в середине или разделяются запятыми, эта формула может не дать правильный результат. Для расширения этого метода на международные почтовые индексы или пользовательские форматы может потребоваться другой или более сложный подход к формуле, либо попробуйте решение на основе VBA ниже.
Извлечение почтового индекса с помощью пользовательской функции в Excel
Если ваши адресные данные более сложные, непоследовательные или содержат почтовые индексы в различных форматах и позициях внутри строки, как показано на следующем скриншоте, макрос VBA может предоставить более гибкие варианты извлечения. Этот автоматизированный метод быстро обрабатывает весь столбец адресов, чтобы выделить и вернуть только почтовые индексы, значительно экономя время и минимизируя ручной труд.
1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.
2. В окне VBA нажмите Insert > Module, чтобы создать новый модуль. Скопируйте и вставьте следующий код VBA в окно модуля:
Public Function ExtractPostcode(text As String) As String
Dim reg As New RegExp
Dim m As MatchCollection
reg.Pattern = "\b([A-Z]{1,2}\d{1,2}[A-Z]?\s*\d[A-Z]{2}|\d{5}(?:-\d{4})?|\d{6})\b"
reg.IgnoreCase = True
reg.Global = False
If reg.Test(text) Then
Set m = reg.Execute(text)
ExtractPostcode = m(0).Value
Else
ExtractPostcode = ""
End If
End Function3. После вставки кода в окне редактора VBA выберите Tools > References. См. скриншот:
4. В диалоговом окне References отметьте Microsoft VBScript Regular Expressions 5.5 и нажмите OK.
5. Вернитесь на лист и введите эту формулу: =ExtractPostcode(A2), перетащите маркер заполнения вниз на другие ячейки. Все почтовые индексы будут отображены сразу, см. скриншот:
Совет: С помощью этого кода вы можете автоматически извлекать почтовые индексы из любой страны или региона в Excel всего за несколько секунд; просто скорректировав регулярное выражение, чтобы оно соответствовало правилам почтового индекса вашего целевого региона, вы сможете быстро адаптироваться к различным форматам — таким как «SW1A 1AA» для Великобритании, «12345-6789» для США или «100000» для Китая, — значительно повышая эффективность очистки и анализа данных.
Связанные статьи:
Лучшие инструменты для повышения продуктивности в 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек