Том работает со множеством списков адресов. Одна вещь, которая сводит его с ума, — это когда адрес указан в одной камере. Тому нужен номер дома в одной камере, а улица в другой. Итак, если адрес — «1234 Maple Glen Ave.», тогда ему нужно «1234» в одной ячейке и «Maple Glen Ave.» в другой. Он может скопировать номер дома в столбец, расположенный перед названием улицы, но это долгий процесс, чтобы повторно ввести номера и затем удалить их из названия улицы. Том задается вопросом, есть ли более простой способ «отделить» номер дома от названия улицы.

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

даст желаемый результат. К сожалению, это не лучший способ выделить номер дома. Причина проста: если вы используете инструмент, указав разделитель, единственный имеющий смысл — это пробел. Но если вы сделаете это, вы получите «1234 Maple Glen Ave.» распределены по четырем столбцам вместо двух желаемых. Затем вам нужно будет найти способ снова вставить название улицы.

Другой способ потенциально использовать инструмент «Текст в столбцы» — использовать фиксированную ширину столбца (вместо разделителя). Этот подход будет работать только в том случае, если все ваши адреса имеют одинаковое количество цифр в номере дома. В большинстве наборов данных, конечно, этого не произойдет — номера домов могут состоять из любого количества цифр и даже могут включать нечисловые символы (такие как «1234A», «1234-B» или «1234-36»).

Один из подходов, который будет работать, — использовать формулы для разделения исходных адресов. Ваши формулы могут вводить первый пробел в адресе, возвращая либо часть слева от пробела, либо часть справа. Вот способ вытащить все до пробела, учитывая адрес в ячейке A1:

=LEFT(A1,FIND(" ",A1)-1)

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

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

Чтобы получить часть адреса, следующую за первым пробелом, вы можете использовать эту формулу:

=MID(A1,FIND(" ",A1)+1,LEN(A1))

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

=TRIM(SUBSTITUTE(A1,B1,))

С вашими формулами в столбцах B и C (и адресами в столбце A) вы можете скопировать формулы вниз для любого количества необходимых строк. Затем вы можете выбрать этот диапазон B: C и использовать Специальную вставку, чтобы вставить значения обратно в эти ячейки. После этого вы можете безопасно удалить исходные адреса в столбце A.

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

Sub SplitAddress()

Dim c As Range     Dim j As Integer     Dim n As String     Dim addr As String

Selection.Insert Shift:=xlToRight     Selection.Offset(0, 1).Select

For Each c In Selection         j = InStr(1, c, " ")

n = Left(c, j)

c.Offset(0, -1) = n         addr = Trim(Right(c, Len(c) - j))

c = addr     Next End Sub

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

Function GrabHouseNumber(Raw As String) As Text     Dim x As Variant     Dim House As Variant

x = Split(Raw, " ")     'use space char to split elements into array     House = x(0)   'first element of array     If Left(House, 1) Like "#" Then         'First char is numeric digit         GrabHouseNumber = House  'set return value as house number     Else         GrabHouseNumber = ""  'First char is text, so not a house number     End If End Function

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

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

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

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

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

Этот совет (13350) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.