Извлечение штата и почтового индекса

У Дэна есть столбец ячеек, и каждая ячейка содержит три элемента: город, штат и почтовый индекс. (Все три находятся в одной ячейке, так же, как вы видите в адресной строке.) Некоторые из почтовых индексов состоят из пяти цифр, а некоторые – из девяти. Дэну нужно перенести двухзначное состояние и пятизначный почтовый индекс в их собственные ячейки справа от текущих данных. Дэн знает, что может использовать инструмент «Текст в столбцы», но считает, что это требует много работы, поскольку ему придется иметь дело с названиями городов и запятыми, состоящими из нескольких слов. Дэн не может не думать, что может существовать шаблонный подход, который был бы проще.

Чтобы дать какие-либо рекомендации, необходимо сделать несколько предположений относительно данных. Предположим, например, что все данные имеют следующий формат:

 My Town, CA 98765-4321 

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

 = MID (A1, FIND (",", A1) +2,  2) = MID (A1, FIND (",", A1) +5,5) 

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

Формулы также предполагают, что в ваших данных нет лишних пробелов; максимум один пробел после запятой и между штатом и почтовым индексом. Это, конечно, достаточно легко обеспечить – просто используйте Найти и Заменить, чтобы заменить два пробела одним пробелом в любом месте вашего рабочего листа.

Конечно, если ваши данные имеют такую ​​структуру, вы можете по-прежнему полагайтесь на инструмент «Текст в столбцы» для выполнения своей работы. Все, что вам нужно сделать, это запустить инструмент и разделить данные по запятой. В результате город останется в одной ячейке, а штат и почтовый индекс будут помещены в следующую ячейку. Затем вы можете снова использовать текст в столбцы, на этот раз во второй ячейке (не в названии города) и разделить содержимое в зависимости от места.

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

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

 = MID  (ОБРЕЗАТЬ (A1), НАЙТИ (СИМВОЛ (1), ПОДСТАВИТЬ (ОБРЕЗАТЬ (A1), "", СИМВОЛ (1), LEN (ОБРЕЗАТЬ (A1))) - LEN (ПОДСТАВИТЬ (ОБРЕЗАТЬ (A1), "", ""  )))) + 1,5) 

Двухсимвольное сокращение состояния можно вернуть, вытащив два символа сразу после предпоследнего пробела:

 = MID (ОБРЕЗАТЬ (A1), НАЙТИ (СИМВОЛ (1), ПОДСТАВИТЬ (ОБРЕЗАТЬ (A1), "", СИМВОЛ (1), LEN (ОБРЕЗАТЬ (A1)) - LEN (ПОДСТАВИТЬ (ОБРЕЗАТЬ (A1), "  "," ")) - 1)) + 1,2) 

Если ваши данные еще менее структурированы – возможно, они включают адреса, которые не все имеют двухсимвольные сокращения состояния (вместо этого NJ из Нью-Джерси) – тогда вам лучше всего будет использовать макрос для разделения данных. Причина этого в том, что VBA имеет гораздо более богатый набор функций обработки текста, чем то, что вы можете сделать с помощью формул Excel. Следующий макрос создает определяемую пользователем функцию, которая может возвращать состояние или почтовый индекс:

 Функция GetStateZIP (rstrAddress As String, iAction As Integer) As String Dim arr As Variant Dim sState As String Dim  sZIP как строка Dim J как целое Dim K как целое Application.Volatile rstrAddress = Trim (rstrAddress) Если Len (rstrAddress) = 0, тогда выйти из функции sState = "?"  sZIP = "?"  Для J = Len (rstrAddress) To 1 Step -1 Если Mid (rstrAddress, J, 1) = "" и sZIP = "?"  Тогда sZIP = Mid (rstrAddress, J + 1, 5) rstrAddress = Trim (Left (rstrAddress, J)) For K = Len (rstrAddress) To 1 Step -1 If Mid (rstrAddress, K, 1) = "" And sState  знак равно  Тогда sState = Mid (rstrAddress, K + 1, 20) rstrAddress = Trim (Left (rstrAddress, K)) End If Next K End If Next J If iAction = 1 Then GetStateZIP = sState End If If iAction = 2 Then GetStateZIP = sZIP  Конец функции IfEnd 

Чтобы использовать эту функцию, просто укажите ссылку на ячейку и либо 1 (если вы хотите состояние), либо 2 (если вы хотите почтовый индекс). Вот пример запроса почтового индекса для любого адреса в ячейке A1:

 = GetStateZIP (A1,2) 

Оцените статью
Frestage.ru
Добавить комментарий