У меня есть колонка адресов в таком формате:
120 Lemon Street Columbus OH 92738 (Basketball Courts)
И мне нужно разделить его на: почтовый адрес (120 Lemon Street)
, город (Columbus)
, штат (OH)
, почтовый индекс (92738)
и описание.((Basketball Courts))
Есть ли способ сделать это? Все они находятся в одном штате, так что это не проблема. Они находятся в разных городах и имеют разные почтовые индексы.
Если вы знаете, как сделать только часть этого, все равно дайте мне знать. Любая помощь приветствуется, спасибо!
решение1
User1282637, я подготовил пример, чтобы показать вам ниже. Я сделал это только в качестве отправной точки, чтобы вы могли закончить со своим собственным полным ответом.
Сначала я составил два списка. В одном были все аббревиатуры штатов, а в другом — все принятые суффиксы названий улиц, которые я нашел здесь:
http://pe.usps.com/text/pub28/28apc_002.htm
Я использовал формулу, которую я привел, просто для того, чтобы преобразовать список так, чтобы он начинался с заглавной буквы, а все остальное было строчными буквами, как у вас.
Далее нужно просто посмотреть, что применимо.
Эту часть можно сделать многими способами, но я решил сделать это для примера. Он просто выведет номер в соответствующей строке, который находит совпадение для используемого суффикса.
Я отделяю часть (баскетбольные площадки) скобками:
Я показываю то, что осталось, потому что в вашем случае «Courts» от «Basketball Courts» — это также суффикс улицы:
Далее мне нужно узнать длину строки, которая является суффиксом улицы, поэтому я использую следующее:
и это...
и, наконец, это позволяет мне отображать только улицу:
Я не делал город и штат, но следуя этой идее, вы можете этого добиться. Также, если вы хотите более чистый вариант, то определенно рассмотрите возможность узнать больше о VBA. Я надеюсь, что это, по крайней мере, научит вас некоторым идеям о том, как это сделать.
решение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 - количество слов в названии города).
решение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
решение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
.
Если это сработает, мы сможем сделать его более подходящим для вашей конкретной ситуации.