Использование функции разделения VBA
Функция VBA Split позволяет отделить компоненты из стандартной текстовой строки, где каждый компонент использует определенный символ-разделитель, например. запятая или двоеточие. Это проще использовать, чем писать код, для поиска разделителей в строке и последующего извлечения значений.
Его можно использовать, если вы читаете строку из значения, разделенного запятыми (файл CSV), или если у вас есть почтовый адрес, который находится в одной строке, но вы хотите видеть его как несколько строк.
Синтаксис:
| 1 | Разделить выражение, разделитель [необязательно], предел [необязательно], сравнить [необязательно] |
Функция VBA Split имеет четыре параметра:
- Выражение - Строка текста, которую вы хотите разделить на разные части.
- Разделитель (по желанию)- строка или непечатаемый символ - определяет символ-разделитель, который будет использоваться для разделения. Если символ-разделитель не указан, используется пробел по умолчанию.
- Предел (по желанию) - число - Определяет, сколько разбиений будет сделано. Если пусто, все доступные разделения будут сделаны внутри строки. Если он установлен на 1, то разбиение не будет. По сути, это позволяет вам выделить определенное количество значений, начиная с начала строки, например. где строка очень длинная, и вам нужны только первые три разделения.
- Сравнивать (по желанию) - Если ваш разделитель является текстовым символом, то он используется для переключения, учитывает ли разделитель регистр или нет. Значения: vbBinaryCompare (с учетом регистра) и vbTextCompare (без учета регистра).
Функция разделения всегда возвращает массив.
Простой пример функции разделения
| 123456789101112 | Sub SplitExample ()'Определить переменныеDim MyArray () как строка, MyString как строка, I как вариант'Пример строки с разделителями пробеламиMyString = "Раз, два, три, четыре"'Используйте функцию Split, чтобы разделить составные части строкиMyArray = Разделить (MyString)'перебирать массив, созданный для отображения каждого значенияДля каждого я в MyArrayMsgBox IДалее яКонец подписки |
В этом примере разделитель не указан, потому что между всеми словами есть пробел, поэтому можно использовать разделитель по умолчанию (пробел).
Массив не имеет размеров и задан как строка. Переменная I, которая используется в цикле For… Next, должна иметь размер как вариант.
Когда этот код запущен, он покажет четыре окна сообщений, по одному для каждого из разделов, например. Один два три. Четыре.
Обратите внимание, что если между словами в строке есть двойной пробел, это будет расценено как разбиение, хотя в нем ничего нет. Возможно, это не тот результат, который вы хотите видеть.
Вы можете решить эту проблему, используя функцию Replace, чтобы заменить любые двойные пробелы одним пробелом:
| 1 | MyString = Заменить (MyString, "", "") |
Конечный или начальный пробел также может вызывать проблемы, создавая пустое разделение. Их часто очень трудно увидеть. Вы можете удалить эти лишние пробелы с помощью функции Trim:
| 1 | MyString = Обрезать (MyString) |
Использование функции разделения с символом-разделителем
Мы можем использовать разделитель точки с запятой (;). Это часто встречается в строках адресов электронной почты для разделения адресов. Вам может быть отправлено электронное письмо, которым вы поделитесь с несколькими коллегами, и вы хотите увидеть на своем рабочем листе список тех, кому оно было отправлено. Вы можете легко скопировать адреса электронной почты из полей «Кому» или «Копировать» в свой код.
| 123456789101112131415 | Sub SplitBySemicolonExample ()'Определить переменныеDim MyArray () как строка, MyString как строка, I как вариант, N как целое число'Пример строки с разделителями с запятойMyString = "[email protected]; [email protected]; [email protected]; [email protected]"'Используйте функцию Split, чтобы разделить составные части строкиMyArray = Разделить (MyString, ";")'Очистить рабочий листActiveSheet.UsedRange.Clear'перебирать массивДля N = 0 до UBound (MyArray)'Поместите каждый адрес электронной почты в первый столбец рабочего листаДиапазон ("A" и N + 1). Значение = MyArray (N)Следующий NКонец подписки |
Обратите внимание, что цикл For… Next используется для перебора массива. Первый элемент в массиве всегда начинается с нуля, и функция Upper Bound используется для получения максимального количества элементов.
После запуска этого кода ваш рабочий лист будет выглядеть так:
Использование предельного параметра в функции разделения
Параметр limit позволяет выполнить определенное количество разделений с начала строки. К сожалению, вы не можете указать начальную позицию или диапазон разбиений, поэтому это довольно просто. Вы можете создать свой собственный код VBA, чтобы создать функцию для этого, и это будет объяснено позже в этой статье.
| 123456789101112131415 | Подраздел SplitWithLimitExample ()'Создать переменныеDim MyArray () как строка, MyString как строка, I как вариант, N как целое число'Пример строки с разделителями-запятымиMyString = "Один, два, три, четыре, пять, шесть"'Используйте функцию Split, чтобы разделить составные части строкиMyArray = Разделить (MyString, ",", 4)'Очистить рабочий листActiveSheet.UsedRange.Clear'Итерировать по массивуДля N = 0 до UBound (MyArray)'Поместите каждую разбивку в первый столбец рабочего листаДиапазон ("A" и N + 1). Значение = MyArray (N)Следующий NКонец подписки |
После того, как вы запустите этот код, ваш рабочий лист будет выглядеть так:
Отдельно показаны только первые три значения разделения. Последние три значения отображаются как одна длинная строка и не разделяются.
Если вы выберете предельное значение, превышающее количество разделителей в строке, это не приведет к ошибке. Строка будет разделена на все составные части, как если бы предельное значение не было указано.
Использование параметра сравнения в функции разделения
Параметр Compare определяет, чувствителен ли разделитель к регистру или нет. Это не применимо, если разделителями являются запятые, точки с запятой или двоеточие.
Примечание. Вместо этого вы всегда можете разместить Option Compare Text <> в верхней части модуля, чтобы исключить чувствительность к регистру для всего модуля.
| 123456789101112131415 | Sub SplitByCompareExample ()'Создать переменныеDim MyArray () как строка, MyString как строка, I как вариант, N как целое число'Пример строки с разделителями XMyString = "OneXTwoXThreexFourXFivexSix"'Используйте функцию Split, чтобы разделить составные части строкиMyArray = Split (MyString, «X»,, vbBinaryCompare)'Очистить рабочий листActiveSheet.UsedRange.Clear'перебирать массивДля N = 0 до UBound (MyArray)'Поместите каждую разбивку в первый столбец рабочего листаДиапазон ("A" и N + 1). Значение = MyArray (N)Следующий NКонец подписки |
В этом примере разделяемая строка использует символ «X» в качестве разделителя. Однако в этой строке есть смесь символов «X» в верхнем и нижнем регистре. В параметре Сравнить в функции разделения используется символ «X» в верхнем регистре.
Если для параметра Compare установлено значение vbBinaryCompare, то строчные символы «x» будут проигнорированы, и ваш рабочий лист будет выглядеть следующим образом:
Если для параметра Compare установлено значение vbTextCompare, то в разделении будут использоваться строчные символы «x», и ваш рабочий лист будет выглядеть следующим образом:
Обратите внимание, что значение в ячейке A6 усечено, поскольку оно содержит символ «x» в нижнем регистре. Поскольку разделение не чувствительно к регистру, любой разделитель, который является частью подстроки, вызовет разделение.
Это важный момент, о котором следует помнить при использовании текстового разделителя и vbTextCompare. Вы легко можете получить неверный результат.
Использование непечатаемых символов в качестве символа-разделителя
В качестве разделителя можно использовать непечатаемые символы, например возврат каретки (разрыв строки).
Здесь мы используем vbCr для указания возврата каретки <>
| 123456789101112131415 | Sub SplitByNonPrintableExample ()'Создать переменныеDim MyArray () как строка, MyString как строка, I как вариант, N как целое число'Пример строки с разделителями возврата кареткиMyString = "One" & vbCr & "Two" & vbCr & "Three" & vbCr & "Four" & vbCr & "Five" & vbCr & "Six"'Используйте функцию Split, чтобы разделить составные части строкиMyArray = Разделить (MyString, vbCr`` vbTextCompare)'Очистить рабочий листActiveSheet.UsedRange.Clear'Итерировать по массивуДля N = 0 до UBound (MyArray)'Поместите каждую разбивку в первый столбец рабочего листаДиапазон ("A" и N + 1). Значение = MyArray (N)Следующий NКонец подписки |
В этом примере строка создается с использованием vbCr (символ возврата каретки) в качестве разделителя.
Когда этот код будет запущен, ваш рабочий лист будет выглядеть так:
Использование функции соединения для отмены разделения
Функция Join повторно объединит все элементы массива, но с использованием указанного разделителя. Если символ-разделитель не указан, будет использоваться пробел.
| 123456789101112131415 | Sub JoinExample ()'Создать переменныеDim MyArray () как строка, MyString как строка, I как вариант, N как целое числоТусклая цель как строка'Пример строки с разделителями-запятымиMyString = "Один, два, три, четыре, пять, шесть"'Поместите MyString в ячейку A1Диапазон ("A1"). Значение = MyString'Используйте функцию Split, чтобы разделить составные части строкиMyArray = Разделить (MyString, ",")'Используйте функцию соединения, чтобы воссоздать исходную строку, используя разделитель с запятой.Цель = Присоединиться (MyArray, ”;”)'Поместите строку результата в ячейку A2Диапазон ("A2"). Значение = ЦельКонец подписки |
Этот код разбивает строку с разделителями-запятыми в массив и снова объединяет их, используя разделители с запятой.
После запуска этого кода ваш рабочий лист будет выглядеть так:
В ячейке A1 есть исходная строка с разделителями-запятыми, а в ячейке A2 - новая объединенная строка с разделителями-точками с запятой.
Использование функции разделения для подсчета слов
Принимая во внимание, что строковая переменная в Excel VBA может иметь длину до 2 ГБ, вы можете использовать функцию разделения для подсчета слов в фрагменте текста. Очевидно, Microsoft Word делает это автоматически, но это может быть полезно для простого текстового файла или текста, скопированного из другого приложения.
| 1234567891011121314 | Sub NumberOfWordsExample ()'Создать переменныеDim MyArray () как строка, MyString как строка'Пример строки с разделителями пробеламиMyString = "Один, два, три, четыре, пять, шесть"'Удалите любые двойные пробелыMyString = Заменить (MyString, "", "")'Удалите все начальные и конечные пробелыMyString = Обрезать (MyString)'Используйте функцию Split, чтобы разделить составные части строкиMyArray = Разделить (MyString)'Показать количество слов с помощью функции UBoundMsgBox "Количество слов" и UBound (MyArray) + 1Конец подписки |
Одна из опасностей этого кода подсчета слов заключается в том, что в нем будут появляться двойные пробелы, а также начальные и конечные пробелы. Если они присутствуют, они будут засчитаны как лишние слова, и подсчет слов будет неточным.
Код использует функции Replace и Trim для удаления этих лишних пробелов.
В последней строке кода отображается количество слов, найденных с помощью функции UBound для получения максимального количества элементов массива с последующим увеличением его на 1. Это связано с тем, что первый элемент массива начинается с нуля.
Разделение адреса на ячейки рабочего листа
Почтовые адреса часто представляют собой длинные текстовые строки с разделителями-запятыми. Вы можете разделить каждую часть адреса на отдельную ячейку.
| 123456789101112131415 | Sub AddressExample ()'Создать переменныеDim MyArray () как строка, MyString как строка, N как целое число'Настроить строку с адресом Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Используйте функцию разделения, чтобы разделить строку с помощью разделителя запятойMyArray = Разделить (MyString, ",")'Очистить рабочий листActiveSheet.UsedRange.Clear'перебирать массивДля N = 0 до UBound (MyArray)'Поместите каждую разбивку в первый столбец рабочего листаДиапазон ("A" и N + 1). Значение = MyArray (N)Следующий NКонец подписки |
При запуске этого кода будет использоваться разделитель запятой, чтобы поместить каждую строку адреса в отдельную ячейку:
Если вы хотите вернуть только почтовый индекс (последний элемент массива), вы можете использовать код:
| 123456789101112 | Дополнительный адрес'Создать переменныеDim MyArray () как строка, MyString как строка, N как целое число, Temp как строка'Настроить строку с адресом Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Используйте функцию разделения, чтобы разделить строку с помощью разделителя запятойMyArray = Разделить (MyString, ",")'Очистить рабочий листActiveSheet.UsedRange.Clear'Введите почтовый индекс в ячейку A1Диапазон ("A1"). Значение = MyArray (UBound (MyArray))Конец подписки |
Это будет использовать только последний элемент в массиве, который можно найти с помощью функции UBound.
С другой стороны, вы можете захотеть увидеть все строки в одной ячейке, чтобы их можно было напечатать на адресной этикетке:
| 1234567891011121314151617 | Sub AddressExample ()'Создать переменныеDim MyArray () как строка, MyString как строка, N как целое число, Temp как строка'Настроить строку с адресом Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Используйте функцию разделения, чтобы разделить строку с помощью разделителя запятойMyArray = Разделить (MyString, ",")'Очистить рабочий листActiveSheet.UsedRange.Clear'перебирать массивДля N = 0 до UBound (MyArray)'поместите каждый элемент массива плюс символ перевода строки в строкуTemp = Temp & MyArray (N) & vbLfСледующий N'Поместите строку на листДиапазон ("A1") = ТемператураКонец подписки |
Этот пример работает так же, как и предыдущий, за исключением того, что он создает временную строку из всех элементов массива, но вставляет символ перевода строки после каждого элемента.
После запуска кода рабочий лист будет выглядеть так:
Разделить строку на ячейки листа
Вы можете скопировать массив Split в ячейки листа <> с помощью всего одной команды:
| 12345678910 | Sub CopyToRange ()'Создать переменныеDim MyArray () как строка, MyString как строка'Пример строки с разделителями пробеламиMyString = "Один, два, три, четыре, пять, шесть"'Используйте функцию Split, чтобы разделить составные части строкиMyArray = Разделить (MyString, ",")'Скопируйте массив в рабочий листДиапазон ("A1: A" & UBound (MyArray) + 1) .Value = WorksheetFunction.Transpose (MyArray)Конец подписки |
Когда этот код будет запущен, ваш рабочий лист будет выглядеть так:
Создание новой функции, позволяющей разделение с заданной точки
Параметр Limit в функции Split позволяет вам указать только верхний предел, на котором вы хотите, чтобы разделение остановилось. Он всегда начинается с начала строки.
Было бы очень полезно иметь аналогичную функцию, в которой вы можете указать начальную точку разделения внутри строки и количество разделений, которые вы хотите видеть с этой точки и далее. Он также будет извлекать только те разбиения, которые вы указали в массиве, вместо того, чтобы иметь огромное строковое значение в качестве последнего элемента в массиве.
Вы можете легко создать функцию (называемую SplitSlicer) в VBA, чтобы сделать это:
| 123456789101112131415161718192021222324 | Функция SplitSlicer (Target As String, Del As String, Start as Integer, N As Integer)'Создать переменную массиваDim MyArray () как строка'Захватить разделение, используя начальную переменную, используя символ-разделительMyArray = Разделить (цель, удаление, начало)‘Проверьте, не превышает ли начальный параметр количества разделений - это может вызвать проблемыЕсли Start> UBound (MyArray) + 1, то‘Отображение ошибки и выход из функцииMsgBox "Начальный параметр больше, чем количество доступных разделений"SplitSlicer = MyArrayФункция выходаКонец, если'Поместите последний элемент массива в строкуЦель = MyArray (UBound (MyArray))'Разделить строку, используя N как пределMyArray = Разделить (Цель, Del, N)‘Убедитесь, что верхний предел больше нуля, поскольку код удаляет последний элементЕсли UBound (MyArray)> 0, то'Используйте ReDim, чтобы удалить последний элемент массиваReDim Preserve MyArray (UBound (MyArray) - 1)Конец, если'Вернуть новый массивSplitSlicer = MyArrayКонечная функция |
Эта функция построена с четырьмя параметрами:
- Цель - строка - это входная строка, которую вы хотите разделить
- Del - строковый или непечатаемый символ - это символ-разделитель, который вы используете, например запятая, двоеточие
- Начинать - число - это начальный раздел для вашего среза
- N - число - это количество разделов, которое вы хотите сделать в своем срезе
Ни один из этих параметров не является необязательным или имеет значения по умолчанию, но вы можете включить это в код функции, если хотите расширить его.
Функция использует функцию Split для создания массива, используя параметр Start в качестве предела. Это означает, что элементы массива будут содержать разбиения до начального параметра, но оставшаяся часть строки будет последним элементом и не будет разбита.
Последний элемент в массиве передается обратно в строку с помощью функции UBound, чтобы определить, какой это элемент.
Затем строка снова разделяется на массив, используя N в качестве предельной переменной. Это означает, что разбиение будет выполнено для строки до позиции N, после чего остальная часть строки сформирует последний элемент в массиве.
Оператор ReDim используется для удаления последнего элемента, поскольку мы хотим, чтобы в массиве оставались только определенные элементы. Обратите внимание, что используется параметр Preserve, иначе все данные в массиве будут потеряны.
Затем новый массив возвращается коду, из которого он был вызван.
Обратите внимание, что код «защищен от ошибок». Пользователи часто будут делать странные вещи, о которых вы не задумывались. Например, если они попытаются использовать функцию с параметром Start или N, превышающим доступное количество разбиений в строке, это может привести к сбою функции.
Код включен для проверки значения Start, а также для того, чтобы убедиться, что существует элемент, который можно удалить при использовании оператора ReDim в массиве.
Вот код для проверки функции:
| 123456789101112 | Sub TestSplitSlicer ()'Создать переменныеDim MyArray () как строка, MyString как строка'Определить образец строки с разделителями-запятымиMyString = "Один, Два, Три, Четыре, Пять, Шесть, Семь, Восемь, Девять, Десять"'Используйте функцию Splitslicer для определения нового массиваMyArray = SplitSlicer (MyString, ",", 4, 3)'Очистить активный листActiveSheet.UsedRange.Clear'Скопируйте массив в рабочий листДиапазон ("A1: A" & UBound (MyArray) + 1) .Value = WorksheetFunction.Transpose (MyArray)Конец подписки |
Запустите этот код, и ваш рабочий лист будет выглядеть так:
