У Аллана есть список из нескольких сотен имен и адресов. Уличные адреса варьируются от 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, НАЙТИ («», 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 для каждой ячейки в выделении sStreet = cell.Value J = InStr (sStreet, «») Если J> 0, то iNum = Val (Left (sStreet, J)) Если iNum> 0, то cell.Offset (0, 1) .Value = iNum sStreet = Trim (Mid (sStreet, J, Len (sStreet))) End If End If cell.Offset (0 , 2) .Value = sStreet NextEnd Sub
Чтобы использовать этот макрос, просто выберите диапазон ячеек, содержащих ваши адреса, и затем запустите его. Первая числовая часть адреса появится в ячейке справа от каждого адреса, а баланс адреса будет помещен в ячейку справа от него. (Поэтому вы должны убедиться, что справа от выбранных вами адресов есть два пустых столбца.)
Источник: