Как извлечь почтовый индекс из списка адресов в 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 Function

3. После вставки кода в окне редактора VBA выберите Tools > References. См. скриншот:

4. В диалоговом окне References отметьте Microsoft VBScript Regular Expressions 5.5 и нажмите OK.

5. Вернитесь на лист и введите эту формулу: =ExtractPostcode(A2), перетащите маркер заполнения вниз на другие ячейки. Все почтовые индексы будут отображены сразу, см. скриншот:

Совет: С помощью этого кода вы можете автоматически извлекать почтовые индексы из любой страны или региона в Excel всего за несколько секунд; просто скорректировав регулярное выражение, чтобы оно соответствовало правилам почтового индекса вашего целевого региона, вы сможете быстро адаптироваться к различным форматам — таким как «SW1A 1AA» для Великобритании, «12345-6789» для США или «100000» для Китая, — значительно повышая эффективность очистки и анализа данных.

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

• Извлечение инициалов из имен в Excel
• Извлечение десятичного значения из строки в Excel