Как извлечь почтовый индекс из списка адресов в Excel?
При работе с клиентской информацией в Excel часто возникает список полных адресов, где каждая запись включает и уличные данные, и почтовый индекс (ZIP-код). Если вам нужно выделить и извлечь только почтовые индексы — например, для рассылки, регионального анализа или форматирования данных — их ручное разделение по одному может оказаться утомительным и чреватым ошибками, особенно при работе с большими объёмами данных. К счастью, Excel предлагает практичные способы быстро и эффективно извлечь сразу несколько почтовых индексов без ручного труда. В этой статье приведены пошаговые инструкции по извлечению ZIP-кодов из адресов с помощью формулы 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 выберите Вставка > Модуль, чтобы создать новый модуль. Скопируйте и вставьте следующий код 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 выберите в меню пункт Сервис > Ссылки. См. снимок экрана:
4. В диалоговом окне Ссылки установите флажок напротив пункта Microsoft VBScript Regular Expressions 5,5 и нажмите кнопку ОК.
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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек