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

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

My Town, CA 98765-4321

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

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

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

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

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

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

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

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

=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ", CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,5)

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

=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1), LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-1))+1,2)

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

Причина этого в том, что VBA имеет гораздо более богатый набор функций обработки текста, чем то, что вы можете сделать с помощью формул Excel. Следующий макрос создает определяемую пользователем функцию, которая может возвращать состояние или почтовый индекс:

Function GetStateZIP(rstrAddress As String, iAction As Integer) As String     Dim arr As Variant     Dim sState As String     Dim sZIP As String

Application.Volatile     rstrAddress = Trim(rstrAddress)

If Len(rstrAddress) = 0 Then Exit Function     arr = Split(rstrAddress, " ")

With arr         If UBound(arr) < 2 Then             sState = "?"

sZIP = "?"

Else             sState = arr(UBound(arr) - 1)

sZIP = arr(UBound(arr))

End If     End With     If iAction = 1 Then         GetStateZIP = sState     End If     If iAction = 2 Then         GetStateZIP = sZIP     End If End Function

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

=GetStateZIP(A1,2)

_Примечание: _

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

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (9599) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Extracting_a_State_and_a_ZIP_Code [Извлечение состояния и почтового индекса].