При работе с VBA в Excel вам может потребоваться разделить строку на разные части на основе разделителя.
Например, если у вас есть адрес, вы можете использовать функцию VBA Split, чтобы получить разные части адреса, разделенные запятой (которая в данном случае будет разделителем).
SPLIT - это встроенная строковая функция в Excel VBA, которую можно использовать для разделения текстовой строки на основе разделителя.
Функция Excel VBA SPLIT - синтаксис
Разделить (Выражение, [Разделитель], [Предел], [Сравнить])
- Выражение: Это строка, которую вы хотите разделить на основе разделителя. Например, в примере с адресом весь адрес будет выражением «выражение». В случае, если это строка нулевой длины («»), функция SPLIT вернет пустой массив.
- Разделитель: Это необязательный аргумент. Это разделитель, который используется для разделения аргумента «Выражение». В нашем примере с адресом запятая является разделителем, который используется для разделения адреса на разные части. Если вы не укажете этот аргумент, разделителем по умолчанию будет считаться пробел. Если вы указываете строку нулевой длины («»), функция возвращает всю строку «Expression».
- Лимит: Это необязательный аргумент. Здесь вы указываете общее количество подстрок, которые хотите вернуть. Например, если вы хотите вернуть только первые три подстроки из аргумента «Выражение», это будет 3. Если вы не укажете этот аргумент, по умолчанию будет -1, что возвращает все подстроки.
- Сравнивать: Это необязательный аргумент. Здесь вы указываете тип сравнения, которое вы хотите, чтобы функция SPLIT выполняла при оценке подстрок. Доступны следующие варианты:
- Когда сравнение равно 0: Это двоичное сравнение. Это означает, что если ваш разделитель представляет собой текстовую строку (скажем, ABC), то это будет чувствительно к регистру. «ABC» не будет равно «abc».
- Когда сравнение равно 1: Это текстовое сравнение. Это означает, что если вашим разделителем является текстовая строка (скажем, ABC), то даже если у вас есть «abc» в строке «Expression», она будет считаться разделителем.
Теперь, когда мы рассмотрели основы функции SPLIT, давайте рассмотрим несколько практических примеров.
Пример 1 - Разделение слов в предложении
Предположим, у меня есть текст - «Быстрая коричневая лисица перепрыгивает через ленивую собаку».
Я могу использовать функцию SPLIT, чтобы выделить каждое слово этого предложения как отдельный элемент в массиве.
Приведенный ниже код будет к этому:
Sub SplitWords () Dim TextStrng As String Dim Result () As String TextStrng = «Быстрая коричневая лисица перепрыгивает через ленивую собаку» Результат () = Split (TextStrng) End Sub
Хотя код не делает ничего полезного, он поможет вам понять, что делает функция Split в VBA.
Функция Split разбивает текстовую строку и присваивает каждое слово массиву Result.
Итак, в этом случае:
- Результат (0) сохраняет значение «The».
- Результат (1) сохраняет значение «Быстрый».
- Результат (2) сохраняет значение «Коричневый» и так далее.
В этом примере мы указали только первый аргумент - текст, который нужно разделить. Поскольку разделитель не указан, в качестве разделителя по умолчанию используется пробел.
Важная заметка:
- Функция VBA SPLIT возвращает массив, который начинается с базы 0.
- Когда результат функции SPLIT присваивается массиву, этот массив должен быть объявлен как тип данных String. Если вы объявите его как тип данных Variant, он покажет ошибку несоответствия типа). Обратите внимание, что в приведенном выше примере я объявил Result () как тип данных String.
Пример 2 - Подсчет количества слов в предложении
Вы можете использовать функцию SPLIT, чтобы получить общее количество слов в предложении. Хитрость здесь в том, чтобы подсчитать количество элементов в массиве, которое вы получите при разделении текста.
В приведенном ниже коде будет отображаться окно сообщения с количеством слов:
Sub WordCount () Dim TextStrng As String Dim WordCount As Integer Dim Result () As String TextStrng = "Быстрая коричневая лиса перепрыгивает через ленивую собаку" Результат = Split (TextStrng) WordCount = UBound (Result ()) + 1 MsgBox " Количество слов: "& WordCount End Sub
В этом случае функция UBound сообщает нам верхнюю границу массива (т.е. максимальное количество элементов в массиве). Поскольку база массива равна 0, добавляется 1, чтобы получить общее количество слов.
Вы можете использовать аналогичный код для создания пользовательской функции в VBA, которая будет принимать текст в качестве входных данных и возвращать количество слов.
Приведенный ниже код создаст эту функцию:
Функция WordCount (CellRef As Range) Dim TextStrng As String Dim Result () As String Result = Split (WorksheetFunction.Trim (CellRef.Text), "") WordCount = UBound (Result ()) + 1 End Function
После создания вы можете использовать функцию WordCount, как любую другую обычную функцию.
Эта функция также обрабатывает начальные, конечные и двойные пробелы между словами. Это стало возможным благодаря использованию функции TRIM в коде VBA.
Если вы хотите узнать больше о том, как эта формула работает для подсчета количества слов в предложении, или хотите узнать о способе вычисления количества слов с помощью формулы, отличной от VBA, ознакомьтесь с этим руководством.
Пример 3 - Использование разделителя, отличного от пробела
В предыдущих двух примерах мы использовали только один аргумент в функции SPLIT, а остальные были аргументами по умолчанию.
Когда вы используете какой-либо другой разделитель, вам необходимо указать это в формуле SPLIT.
В приведенном ниже коде функция SPLIT возвращает массив, основанный на запятой в качестве разделителя, а затем показывает сообщение с каждым словом в отдельной строке.
Sub CommaSeparator () Dim TextStrng As String Dim Result () As String Dim DisplayText As String TextStrng = "The, Quick, Brown, Fox, Jump, Over, The, Lazy, Dog" Result = Split (TextStrng, ",") Для i = LBound (Result ()) To UBound (Result ()) DisplayText = DisplayText & Result (i) & vbNewLine Далее i MsgBox DisplayText End Sub
В приведенном выше коде я использовал цикл For Next, чтобы просмотреть каждый элемент массива «Result», назначив его переменной «DisplayText».
Пример 4 - Разделите адрес на три части
С помощью функции SPLIT вы можете указать, сколько чисел разделений вы хотите получить. Например, если я ничего не укажу, каждый экземпляр разделителя будет использоваться для разделения строки.
Но если я укажу 3 как предел, то строка будет разделена только на три части.
Например, если у меня есть следующий адрес:
2703 Winifred Way, Индианаполис, Индиана, 46204
Я могу использовать функцию Split в VBA, чтобы разделить этот адрес на три части.
Он разделяет первые два на основе разделителя запятой, а оставшаяся часть становится третьим элементом массива.
В приведенном ниже коде адрес будет отображаться в трех разных строках в окне сообщения:
Sub CommaSeparator () Dim TextStrng As String Dim Result () As String Dim DisplayText As String TextStrng = "2703 Winifred Way, Indianapolis, Indiana, 46204" Result = Split (TextStrng, ",", 3) For i = LBound (Result ( )) To UBound (Result ()) DisplayText = DisplayText & Result (i) & vbNewLine Далее i MsgBox DisplayText End Sub
Одно из практических применений этого может быть, когда вы хотите разделить однострочный адрес в формате, показанном в окне сообщения. Затем вы можете создать настраиваемую функцию, которая возвращает адрес, разделенный на три части (каждая часть в новой строке).
Следующий код сделает это:
Функция ThreePartAddress (cellRef As Range) Dim TextStrng As String Dim Result () As String Dim DisplayText As String Result = Split (cellRef, ",", 3) For i = LBound (Result ()) To UBound (Result ()) DisplayText = DisplayText & Trim (Результат (i)) & vbNewLine Next i ThreePartAddress = Mid (DisplayText, 1, Len (DisplayText) - 1) Функция завершения
Если у вас есть этот код в модуле, вы можете использовать функцию (ThreePartAddress) в книге так же, как любую другую функцию Excel.
Эта функция принимает один аргумент - ссылку на ячейку с адресом.
Обратите внимание: чтобы полученный адрес отображался в трех разных строках, вам необходимо применить формат переноса текста к ячейкам (он находится на вкладке «Главная» в группе «Выравнивание»). Если формат «Перенос текста» не включен, вы увидите весь адрес как одну строку.
Пример 5 - Получение названия города из адреса
С помощью функции Split в VBA вы можете указать, какую часть результирующего массива вы хотите использовать.
Например, предположим, что я разделяю следующий адрес на основе запятой в качестве разделителя:
2703 Winifred Way, Индианаполис, Индиана, 46204
Результирующий массив будет выглядеть примерно так, как показано ниже:
{"2703 Winifred Way", "Indianapolis", "Indiana", "46204"}
Поскольку это массив, я могу выбрать отображение или возврат определенной части этого массива.
Ниже приведен код пользовательской функции, где вы можете указать число, и она вернет этот элемент из массива. Например, если мне нужно название состояния, я могу указать 3 (поскольку это третий элемент в массиве).
Функция ReturnNthElement (CellRef как диапазон, ElementNumber как целое число) Dim Result () As String Result = Split (CellRef, ",") ReturnNthElement = Result (ElementNumber - 1) End Function
Вышеупомянутая функция принимает два аргумента: ссылку на ячейку с адресом и номер элемента, который вы хотите вернуть. Функция Split разделяет элементы адреса и присваивает их переменной Result.
Затем он возвращает номер элемента, который вы указали в качестве второго аргумента. Обратите внимание, что, поскольку база равна 0, ElementNumber-1 используется для возврата правильной части адреса.
Если вам нужно название города, вы можете использовать 2 в качестве второго аргумента. Если вы используете число, превышающее общее количество элементов, оно вернет #VALUE! ошибка.
Вы можете еще больше упростить код, как показано ниже:
Функция ReturnNthElement (CellRef как диапазон, ElementNumber как целое число) ReturnNthElement = Split (CellRef, ",") (ElementNumber - 1) Конечная функция
В приведенном выше коде вместо использования переменной Result возвращается только указанный номер элемента.
Итак, если у вас есть Split («Доброе утро») (0), он вернет только первый элемент, который является «Хорошим».
Точно так же в приведенном выше коде он возвращает только указанный номер элемента.
