У Дэна есть столбец ячеек, и каждая ячейка содержит три элемента: город, штат и почтовый индекс. (Все три находятся в одной ячейке, так же, как вы видите в адресной строке.) Некоторые из почтовых индексов состоят из пяти цифр, а некоторые – из девяти. Дэну нужно перенести двухзначное состояние и пятизначный почтовый индекс в их собственные ячейки справа от текущих данных. Дэн знает, что может использовать инструмент «Текст в столбцы», но считает, что это требует много работы, поскольку ему придется иметь дело с названиями городов и запятыми, состоящими из нескольких слов. Дэн не может не думать, что может существовать шаблонный подход, который был бы проще.
Чтобы дать какие-либо рекомендации, необходимо сделать несколько предположений относительно данных. Предположим, например, что все данные имеют следующий формат:
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)
Источник: