Код:

Question

У меня есть колонка адресов в таком формате:

120 Lemon Street Columbus OH 92738 (Basketball Courts)

И мне нужно разделить его на: почтовый адрес (120 Lemon Street), город (Columbus), штат (OH), почтовый индекс (92738)и описание.((Basketball Courts))

Есть ли способ сделать это? Все они находятся в одном штате, так что это не проблема. Они находятся в разных городах и имеют разные почтовые индексы.

Если вы знаете, как сделать только часть этого, все равно дайте мне знать. Любая помощь приветствуется, спасибо!

Answer 1

User1282637, я подготовил пример, чтобы показать вам ниже. Я сделал это только в качестве отправной точки, чтобы вы могли закончить со своим собственным полным ответом.

Сначала я составил два списка. В одном были все аббревиатуры штатов, а в другом — все принятые суффиксы названий улиц, которые я нашел здесь:

http://pe.usps.com/text/pub28/28apc_002.htm

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

Далее нужно просто посмотреть, что применимо.

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

Я отделяю часть (баскетбольные площадки) скобками:

Я показываю то, что осталось, потому что в вашем случае «Courts» от «Basketball Courts» — это также суффикс улицы:

Далее мне нужно узнать длину строки, которая является суффиксом улицы, поэтому я использую следующее:

и это...

и, наконец, это позволяет мне отображать только улицу:

Я не делал город и штат, но следуя этой идее, вы можете этого добиться. Также, если вы хотите более чистый вариант, то определенно рассмотрите возможность узнать больше о VBA. Я надеюсь, что это, по крайней мере, научит вас некоторым идеям о том, как это сделать.

Answer 2

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

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

120 Lemon Street
120 Lemon Drop Street
120 Lemon Street NW
120 East Lemon Street
120 Lemon Street Apt 3

Количество «слов» в названии улицы может варьироваться от 1 или 2 до 7 или 8, так что это бесполезно для анализа. «Тип» улицы также не особенно полезен. Существует порядка 50-100 слов, используемых только для «типа» улицы (улица, проспект, бульвар, дорога, шлагбаум, переулок, двор, круг, терраса и т. д.). Объедините это с использованием сокращений для типа улицы, как правильных, так и неправильных, и список исчисляется сотнями. Кроме того, это обозначение не всегда является последним словом в поле улицы. Улица — самая сложная для определения часть, поэтому логичным подходом будет определить остальное, а затем оставшееся — это улица.

Название города может состоять из нескольких слов. Washington Court House, OH — из трех слов. Затем рассмотрим такие ситуации, как St Marys, OH. Является ли «St» частью названия города или обозначением типа улицы; в какое поле оно вставляется? Или South Euclid, OH — является ли «South» частью названия города или направлением, которое является частью адреса улицы? У города есть свои проблемы, но есть способ их решить.

Даже использование почтового индекса для определения города имеет проблемы. Не всегда есть совпадение 1:1 между названием города и почтовым индексом.

Самый практичный способ решения проблемы — использовать «словари»: список городов и справочник почтовых индексов. Это самая недвусмысленная часть адреса. Их можно найти в Интернете или в почтовой службе. Для сравнения вам может потребоваться очистить либо ваши данные, либо списки. Им потребуется одинаковый стиль заглавных букв, и любые лишние пробелы в ваших данных помешают точному совпадению.

Если ваши данные или листинг используют сокращения, вам придется с этим разобраться. Либо переведите несокращенное в стандартные сокращения, либо выполните вторичное сопоставление со словарем сокращений (также доступным онлайн или в почтовой службе), если эти различия будут обнаружены.

ZIP-код можно легко проанализировать, и это хорошее место для начала. Выполните поиск почтового индекса по справочнику почтовых индексов. Если результат точно соответствует строке слов, непосредственно предшествующих ZIP-коду, это определяет, какая часть записи является полем города.

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

Если вы найдете совпадение в любом из этих процессов, то все, что останется слева от города, будет почтовым адресом.

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

Независимо от того, насколько строго вы программируете, у вас, скорее всего, все равно будут записи, которые нужно разобрать вручную, и ошибки разбора, которые нужно исправить вручную. Вы не указываете, сколько у вас записей. Возможно, будет проще сделать это вручную.

Если число большое и мне нужно это сделать, я бы отбраковал список. Сопоставьте записи, которые легко, например, однозначные совпадения ZIP. Затем позвольте количеству данных определять, насколько далеко вы зайдете в программировании автоматизированных совпадений.

Для оставшихся записей, предполагая, что вы уже сняли почтовый индекс и описание, вот способ ускорить ручной процесс. Посмотрите на запись и визуально определите количество «слов» в названии города, что является быстрой умственной задачей. Введите это в предписанную ячейку и используйте формулу для разделения улицы от города на основе количества пробелов (разделите на N-м пробеле, где N = общее количество пробелов + 1 - количество слов в названии города).

Answer 3

Попробуйте это. Этот простой подход должен работать очень хорошо, если вы можете смириться с тем, что адрес и город находятся в одной ячейке. У меня есть формула для извлечения города, если это всего лишь одно слово, но она становится намного сложнее, если в качестве города используются несколько слов (например, Нью-Йорк).

Формулы... Адрес и город: =LEFT(A2,FIND("OH",A2)-1) Штат: =MID(A2,FIND("OH",A2),2) - вы упомянули, что все OH, поэтому я упростил Почтовый индекс: =MID(A2,FIND("OH",A2)+3,5) Описание: =TRIM(MID(A2,FIND("OH",A2)+8,30))

https://onedrive.live.com/redir?page=view&resid=D91C36B074F4D0F6!3224&authkey=!AO_MbW7Qxv4yWDo

Answer 4

Код:

Sub SplitAddress()
    Dim Addr As String
    Dim l As Integer
    Dim Desc As String
    Dim Zip As String
    Dim State As String
    Dim City As String
    
    Addr = Selection
    
    l = InStrRev(Addr, "(")
    Desc = Right(Addr, Len(Addr) - l + 1)
    
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    
    l = InStrRev(Addr, " ")
    Zip = Right(Addr, Len(Addr) - l)
    
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    
    l = InStrRev(Addr, " ")
    State = Right(Addr, Len(Addr) - l)
    
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    
    l = InStrRev(Addr, " ")
    City = Right(Addr, Len(Addr) - l)
    
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    
    Selection.Offset(0, 1) = Addr
    Selection.Offset(0, 2) = City
    Selection.Offset(0, 3) = State
    Selection.Range("B11").Offset(0, 4) = Zip
    Selection.Range("B11").Offset(0, 5) = Desc
End Sub

Объяснение: Нажмите Alt+F11и вставьте указанный выше код в появившееся окно. Затем выберите ячейку, содержащую адрес, вернитесь в окно, куда вы вставили код, и нажмите F5.

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