Разделение адреса на разные ячейки

У меня есть столбец, адресованный в этом формате:

120 Lemon Street Columbus OH 92738 (Basketball Courts) 

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

  • Найти и сохранить совпадения из 2 столбцов в Excel
  • Может ли Excel показывать формулу и ее результат одновременно?
  • Paginate Rows в столбцы в Excel
  • Отключить автоформат в Excel 2010
  • Расчет Excel на основе ввода пользователем из раскрывающихся полей
  • Как связать ссылку на ячейку с листом
  • Есть ли способ, которым я могу это сделать? Все они находятся в одном и том же состоянии, так что это не проблема. Они находятся в разных городах и имеют разные почтовые индексы.

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

  • Если Menber Active, чем имя залива, еще проверьте другое в excel
  • Как удалить строки не в фильтре
  • Количество строк с условиями, встречающимися в двух столбцах
  • Excel - Формулы, которые отображаются на нескольких листах?
  • Один файл Excel, несколько пользователей редактирования, текущие обновления
  • Транспонирование набора записей
  • 4 Solutions collect form web for “Разделение адреса на разные ячейки”

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

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

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

    Введите описание изображения здесь

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

    Затем он просто ищет то, что применяется.

    Введите описание изображения здесь

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

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

    Введите описание изображения здесь

    Я показываю, что осталось, потому что в вашем случае «Суды» из «Баскетбольных судов» также является суффиксом улицы:

    Введите описание изображения здесь

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

    Введите описание изображения здесь

    и это…

    Введите описание изображения здесь

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

    Введите описание изображения здесь

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

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

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

     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 слов, используемых только для улицы типа (улица, проспект, бульвар, дорога, магистраль, переулок, суд, круг, терраса и т. Д.). Объедините это с использованием сокращений для типа улицы, как правильных, так и неправильных, а список – в сотнях. Кроме того, это обозначение не всегда является последним словом в поле улицы. Улица является самой трудной частью для идентификации, поэтому логический подход состоит в том, чтобы идентифицировать остальное, а затем остальное – улица.

    Город может быть несколько слов. Вашингтонский суд Хаус, штат Огайо, три слова. Затем рассмотрите такие ситуации, как St Marys, OH. Является ли «Св» частью названия города или обозначением типа улицы; В какое поле оно входит? Или Южный Евклид, Огайо – это «южная» часть названия города или направление, которое является частью уличного адреса? У города есть свои проблемы, но есть способ справиться с ними.

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

    Наиболее практичным способом атаки является использование «словарей»: список городов и каталог ZIP-кода. Это наиболее однозначная часть адреса. Их можно найти в Интернете или в Почтовой службе. Чтобы выполнить сравнения, вам может понадобиться очистить данные или данные. Им понадобится тот же стиль капитализации, и любые дополнительные пробелы в ваших данных будут препятствовать точному совпадению.

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

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

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

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

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

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

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

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

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

    Формулы … Адрес и город: = LEFT (A2, FIND («OH», A2) -1) Состояние: = MID (A2, FIND («OH», A2), 2) – вы упомянули, что все ОН, поэтому Я сохранил его просто Zip: = 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

    Введите описание изображения здесь

    Код:

     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 .

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

    Давайте будем гением компьютера.