У Аллана есть список из нескольких сотен имен и адресов. Уличные адреса варьируются от Main Street, 123 Main Street, US RT 2 или 187 South Elm St. Он хотел бы выделить улицу из адресов.

Таким образом, адрес 123 Main Street в одной ячейке будет содержать «123», а в другой — «Main Street». Если номер дома отсутствует, в столбце с номером дома ничего не отображается. Инструмент «Текст в столбцы» не работает, и он задается вопросом, как он может это сделать.

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

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

=IF(ISERROR(VALUE(LEFT(A1,1))),"",LEFT(A1,FIND(" ",A1)-1))

Предполагая, что вы поместили формулу в ячейку B1, вы можете затем использовать другую формулу для получения нечисловой части адреса:

=TRIM(RIGHT(A1,LEN(A1)-LEN(B1)))

Обратите внимание, что у этого подхода есть ограничение. Некоторые адреса, особенно в крупных мегаполисах, используют такой формат, как 152-33 Bell Blvd. Формулы, приведенные выше, будут работать для этих адресов, но если использовать альтернативу, 152 33 Bell Blvd., формула будет обрабатываться неправильно. Если вы не хотите покупать профессионально разработанную программу для синтаксического анализа адресов, приведенных выше формул и быстрого просмотра результатов будет достаточно.

В этом случае работает другая формула. Предполагая, что ваш адрес находится в ячейке A2, введите следующую формулу в ячейку B2:

=IF(ISNUMBER(VALUE(LEFT(A2,1))),VALUE(LEFT(A2,FIND(" ",A2)-1)),"")

Эта формула гласит: «Если первый символ не является числом, оставьте ячейку пустой. В противном случае дайте мне все символы слева до первого пробела, но не включая его». Затем вы можете использовать результат этой формулы для извлечения нечисловой части адреса:

=IF(B2="",A2,MID(A2,FIND(" ",A2)+1,99))

Другой подход — использовать формулу массива. Здесь снова, предполагая, что ваш адрес находится в ячейке A2, вы можете использовать следующее:

=IF(ISNUMBER(1*MID(A2,ROW($1:$1),1)) = TRUE, LEFT(A2,FIND(" ",A2,1)),"")

Поскольку это формула массива, вам нужно ввести ее, используя Ctrl + Shift + Enter. В результате формула возвращает начальную числовую часть адреса. Затем вы можете определить нечисловую часть, используя следующую формулу массива:

=IF(ISNUMBER(1*MID(A2,ROW($1:$1),1)) = TRUE, RIGHT(A2,LEN(A2)-FIND(" ",A2,1)),A2)

Наконец, следующий макрос можно использовать для отделения адреса от названия улицы.

Sub GetStreetNum()

Dim sStreet As String     Dim J As Integer     Dim iNum As Integer

For Each cell In Selection         sStreet = cell.Value         J = InStr(sStreet, " ")

If J > 0 Then             iNum = Val(Left(sStreet, J))

If iNum > 0 Then                 cell.Offset(0, 1).Value = iNum                 sStreet = Trim(Mid(sStreet, J, Len(sStreet)))

End If         End If         cell.Offset(0, 2).Value = sStreet     Next End Sub

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

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

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

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

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

Этот совет (8029) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Extracting_Street_Numbers_from_an_Address [Извлечение номеров улиц из адреса].