-1

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

120 Lemon Street Columbus OH 92738 (Basketball Courts)

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

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

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

4 ответа4

2

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

Сначала я создал два списка. Одним из них были все аббревиатуры штата, а другим - все общепринятые уличные суффиксы, которые я нашел здесь:

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

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

Далее это просто поиск того, что относится ..

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

Я разделяю часть (Баскетбольные площадки), используя скобки:

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

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

и это...

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

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

0

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

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

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" частью названия города или обозначением типа улицы; в какое поле он входит? Или Южный Евклид, Огайо - это "южная" часть названия города или указатель, который является частью адреса улицы? У города есть свои проблемы, но есть способ их решить.

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

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

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

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

Если нет точного или однозначного соответствия, перейдите к сравнению названий городов. Переберите список названий городов. Для каждого имени определите количество слов, которое оно содержит, и сравните его с тем количеством слов, которое находится непосредственно перед ZIP.

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

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

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

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

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

-1

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

Формулы ... Адрес и город: = ВЛЕВО (A2, НАЙТИ ("OH", A2) -1) Состояние: = MID (A2, НАЙТИ ("OH", A2), 2) - вы упомянули, что все ОН, поэтому я сделал это просто Zip: = MID (A2, НАЙТИ ("OH", A2)+3,5) Описание: = TRIM (MID (A2, НАЙТИ ("OH", A2)+8,30))

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

-2

Код:

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 .

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

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .