Використання функції розділення VBA
Функція VBA Split дозволяє вам відокремити частини компонента всередині стандартного текстового рядка, де кожен компонент використовує певний символ роздільника, наприклад. кома або двокрапка. Це простіше у використанні, ніж написання коду для пошуку роздільників у рядку, а потім вилучення значень.
Він може бути використаний, якщо ви читаєте у рядку зі значення, розділеного комами (файл CSV), або у вас є поштова адреса, яка знаходиться в одному рядку, але ви хочете бачити її як кілька рядків.
Синтаксис такий:
| 1 | Розділити вираз, роздільник [необов’язково], обмежити [необов’язково], порівняти [необов’язково] |
Функція VBA Split має чотири параметри:
- Вираз - Рядок тексту, який потрібно розділити на різні частини.
- Розмежувач (необов’язково)- рядок або недрукований символ - Визначає символ роздільника, який буде використовуватися для поділу. Якщо символ роздільника не вказано, використовується пробіл за замовчуванням.
- Обмеження (необов’язково) - номер - Визначає, скільки розщеплень буде зроблено. Якщо це поле порожнє, усі доступні розділення будуть зроблені в рядку. Якщо встановлено значення 1, то розколів не буде. В основному, це дозволяє вам відокремити певну кількість значень, починаючи з початку рядка, наприклад. де рядок дуже довгий і вам потрібні лише перші три розщеплення.
- Порівняйте (необов’язково) - Якщо ваш роздільник є текстовим символом, він використовується для перемикання, чи роздільник роздільний або регістровий. Значення vbBinaryCompare (з урахуванням регістру) та vbTextCompare (з урахуванням регістру).
Функція split завжди повертає масив.
Простий приклад функції розщеплення
| 123456789101112 | Sub SplitExample ()'Визначте змінніDim MyArray () As String, MyString As String, I As Variant'Зразок рядка з роздільниками пробілівMyString = "Один два три чотири"'Використовуйте функцію Split, щоб розділити складові частини рядкаMyArray = Розділити (MyString)'перебирати масив, створений для відображення кожного значенняДля кожного я в MyArrayMsgBox IДалі яEnd Sub |
У цьому прикладі роздільник не вказано, оскільки всі слова мають пробіл між ними, тому можна використовувати роздільник за промовчанням (пробіл).
Масив не має розмірів і встановлюється як рядок. Змінна I, яка використовується в циклі For… Next, повинна бути визначена як варіант.
Коли цей код запущено, він покаже чотири вікна повідомлень, по одному для кожного з розділів, наприклад. Один два три. Чотири.
Зауважте, що якщо між словами у рядку є подвійний пробіл, це буде оцінено як розщеплення, хоча у ньому немає нічого. Це може бути не той результат, який ви хотіли б побачити.
Ви можете вирішити цю проблему, використовуючи функцію «Замінити», щоб замінити будь -які подвійні пробіли на один пробіл:
| 1 | MyString = Замінити (MyString, "", "") |
Задній або провідний пробіл також може викликати проблеми, створюючи порожній розділ. Часто їх дуже важко побачити. Ви можете видалити ці сторонні простори за допомогою функції Обрізання:
| 1 | MyString = Обрізати (MyString) |
Використання функції розділення з символом -роздільником
Ми можемо використовувати роздільник крапки з комою (;). Це часто зустрічається в рядках адрес електронної пошти для розділення адрес. Можливо, вам надійде електронний лист, яким поділиться з кількома колегами, і ви захочете побачити список у своєму робочому аркуші, до кого він звернувся. Ви можете легко скопіювати адреси електронної пошти з поштових скриньок "Кому" або "Копіювати" та у свій код.
| 123456789101112131415 | Sub SplitBySemicolonExample ()'Визначте змінніDim MyArray () As String, MyString As String, I As Variant, N As Integer'Зразок рядка з роздільниками з комоюMyString = "[email protected]; [email protected]; [email protected]; [email protected]"'Використовуйте функцію Split, щоб розділити складові частини рядкаMyArray = Розділити (MyString, ";")'Очистіть аркушActiveSheet.UsedRange.Clear'перебирати по масивуДля N = 0 до UBound (MyArray)'Помістіть кожну електронну адресу в перший стовпець аркушаДіапазон ("A" & N + 1). Значення = MyArray (N)Далі NEnd Sub |
Зауважте, що цикл For… Next використовується для перебору масиву. Перший елемент у масиві завжди починається з нуля, а функція Upper Bound використовується для отримання максимальної кількості елементів.
Після запуску цього коду ваш аркуш буде виглядати так:
Використання граничного параметра в функції розділення
Параметр межі дозволяє виконати певну кількість розщеплень від початку рядка. На жаль, ви не можете надати початкову позицію або діапазон розділень, які потрібно виконати, тому це досить просто. Ви можете створити власний код VBA, щоб створити функцію для цього, і це буде пояснено далі в цій статті.
| 123456789101112131415 | Sub SplitWithLimitExample ()'Створіть змінніDim MyArray () As String, MyString As String, I As Variant, N As Integer'Зразок рядка з роздільниками комиMyString = "Один, два, три, чотири, п'ять, шість"'Використовуйте функцію Split, щоб розділити складові частини рядкаMyArray = Розділити (MyString, ",", 4)'Очистіть аркушActiveSheet.UsedRange.Clear'Ітерація по масивуДля N = 0 до UBound (MyArray)'Помістіть кожен розріз у перший стовпець аркушаДіапазон ("A" & N + 1). Значення = MyArray (N)Далі NEnd Sub |
Після запуску цього коду ваш робочий аркуш буде виглядати так:
Тільки перші три значення розділення відображаються окремо. Наступні три значення відображаються як один довгий рядок і не розбиваються.
Якщо ви виберете граничне значення, яке перевищує кількість роздільників у рядку, це не призведе до помилки. Рядок буде розділений на всі складові частини так, ніби граничне значення не було надано.
Використання параметра порівняння у функції розділення
Параметр "Порівняти" визначає, чи є роздільник чутливим до регістру чи ні. Це не застосовується, якщо роздільниками є коми, крапки з комою або двокрапка.
Примітка. Натомість ви завжди можете розмістити Опцію Порівняти текст <> у верхній частині модуля, щоб усунути чутливість до регістру для всього модуля.
| 123456789101112131415 | Sub SplitByCompareExample ()'Створіть змінніDim MyArray () As String, MyString As String, I As Variant, N As Integer'Зразок рядка з роздільниками XMyString = "OneXTwoXThreexFourXFivexSix"'Використовуйте функцію Split, щоб розділити складові частини рядкаMyArray = Розділити (MyString, "X",, vbBinaryCompare)'Очистіть аркушActiveSheet.UsedRange.Clear'перебирати по масивуДля N = 0 до UBound (MyArray)'Помістіть кожен розріз у перший стовпець аркушаДіапазон ("A" & N + 1). Значення = MyArray (N)Далі NEnd Sub |
У цьому прикладі рядок, який потрібно розділити, використовує символ “X” як роздільник. Однак у цьому рядку є суміш символів верхнього та нижнього регістру "X". Параметр "Порівняти" у функції "Розділити" використовує символ "X" у верхньому регістрі.
Якщо для параметра Порівняти встановлено значення vbBinaryCompare, символи "x" у нижньому регістрі ігноруватимуться, і ваш робочий аркуш буде виглядати так:
Якщо для параметра Порівняти встановлено значення vbTextCompare, у розділі будуть використовуватися символи нижнього регістру «x», і ваш робочий аркуш буде виглядати так:
Зауважте, що значення у комірці A6 усічене, оскільки воно містить символ "x" у нижньому регістрі. Оскільки поділ не враховує регістр, будь -який роздільник, що входить до складу підрядка, спричинить поділ.
Це важливий момент, який слід мати на увазі під час використання розділювача тексту та vbTextCompare. Ви можете легко отримати неправильний результат.
Використання недрукованих символів як розділювального символу
Ви можете використовувати як роздільник символи, які не друкуються, наприклад, повернення каретки (розрив рядка).
Тут ми використовуємо vbCr, щоб вказати повернення каретки <>
| 123456789101112131415 | Sub SplitByNonPrintableExample ()'Створіть змінніDim MyArray () As String, MyString As String, I As Variant, N As Integer'Зразок рядка з роздільниками повернення кареткиMyString = "Один" & vbCr & "Два" & vbCr & "Три" & vbCr & "Чотири" & vbCr & "П'ять" & vbCr & "Шість"'Використовуйте функцію Split, щоб розділити складові частини рядкаMyArray = Розділити (MyString, vbCr,, vbTextCompare)'Очистіть аркушActiveSheet.UsedRange.Clear'Ітерація по масивуДля N = 0 до UBound (MyArray)'Помістіть кожен розріз у перший стовпець аркушаДіапазон ("A" & N + 1). Значення = MyArray (N)Далі NEnd Sub |
У цьому прикладі рядок будується за допомогою vbCr (символ повернення каретки) як роздільника.
Коли цей код запущено, ваш аркуш буде виглядати так:
Використання функції Join для зворотного поділу
Функція Join знову приєднує всі елементи масиву, але з використанням зазначеного роздільника. Якщо символ розмежувача не вказано, буде використано пробіл.
| 123456789101112131415 | Sub JoinExample ()'Створіть змінніDim MyArray () As String, MyString As String, I As Variant, N As IntegerПриглушити ціль як рядок'Зразок рядка з роздільниками комиMyString = "Один, два, три, чотири, п'ять, шість"'Помістіть MyString у клітинку А1Діапазон ("A1"). Значення = MyString'Використовуйте функцію Split, щоб розділити складові частини рядкаMyArray = Розділити (MyString, ",")'Використовуйте функцію Join для повторного створення вихідного рядка за допомогою роздільника з комоюЦіль = Приєднатися (MyArray, ”;”)'Помістіть рядок результату в клітинку А2Діапазон ("A2"). Значення = цільEnd Sub |
Цей код розбиває рядок з роздільниками коми на масив і знову об’єднує їх за допомогою роздільників з комою.
Після запуску цього коду ваш аркуш буде виглядати так:
Комірка A1 має оригінальний рядок з роздільниками коми, а клітинка A2-новий об’єднаний рядок з роздільниками з комою.
Використання функції Split для підрахунку слів
Беручи до уваги, що рядкова змінна в Excel VBA може мати довжину до 2 Гб, ви можете використовувати функцію розбиття для підрахунку слів у фрагменті тексту. Очевидно, що Microsoft Word робить це автоматично, але це може бути корисним для простого текстового файлу або тексту, скопійованого з іншої програми.
| 1234567891011121314 | Додатковий номерOffWordsExample ()'Створіть змінніDim MyArray () As String, MyString As String'Зразок рядка з роздільниками пробілівMyString = "Один два три чотири п'ять шість"'Видаліть усі подвійні пробілиMyString = Замінити (MyString, "", "")'Видаліть усі провідні або кінцеві пробілиMyString = Обрізати (MyString)'Використовуйте функцію Split, щоб розділити складові частини рядкаMyArray = Розділити (MyString)'Показати кількість слів за допомогою функції UBoundMsgBox "Кількість слів" & UBound (MyArray) + 1End Sub |
Однією з небезпек цього коду підрахунку слів є те, що він буде підкинутий подвійними пробілами, а також провідними та проміжними пробілами. Якщо вони є, їх буде зараховано як зайві слова, і кількість слів закінчиться як неточна.
Код використовує функції «Замінити» та «Обрізати», щоб видалити зайві пробіли.
Остаточний рядок коду відображає кількість слів, знайдених за допомогою функції UBound, щоб отримати максимальний номер елемента масиву, а потім збільшити його на 1. Це відбувається тому, що перший елемент масиву починається з нуля.
Розподіл адреси на клітинки аркуша
Поштові адреси часто являють собою довгі рядки тексту з роздільниками коми. Можливо, вам захочеться розділити кожну частину адреси на окрему клітинку.
| 123456789101112131415 | Sub AddressExample ()'Створіть змінніDim MyArray () як рядок, MyString як рядок, N як ціле число'Налаштуйте рядок з адресою корпорації MicrosoftMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Використовуйте функцію розділення, щоб розділити рядок за допомогою роздільника комиMyArray = Розділити (MyString, ",")'Очистіть аркушActiveSheet.UsedRange.Clear'перебирати по масивуДля N = 0 до UBound (MyArray)'Помістіть кожен розріз у перший стовпець аркушаДіапазон ("A" & N + 1). Значення = MyArray (N)Далі NEnd Sub |
Запуск цього коду буде використовувати роздільник коми для розміщення кожного рядка адреси в окремій комірці:
Якщо ви хотіли лише повернути поштовий індекс (останній елемент масиву), тоді ви можете скористатися кодом:
| 123456789101112 | Sub AddressZipCodeExample ()'Створіть змінніDim MyArray () As String, MyString As String, N As Integer, Temp As String'Налаштуйте рядок з адресою корпорації MicrosoftMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Використовуйте функцію розділення, щоб розділити рядок за допомогою роздільника комиMyArray = Розділити (MyString, ",")'Очистіть аркушActiveSheet.UsedRange.Clear'Поставте поштовий індекс у клітинку А1Діапазон ("A1"). Значення = MyArray (UBound (MyArray))End Sub |
Це буде використовувати лише останній елемент у масиві, який знайдено за допомогою функції UBound.
З іншого боку, ви можете побачити всі рядки в одній клітинці, щоб їх можна було надрукувати на етикетці адреси:
| 1234567891011121314151617 | Sub AddressExample ()'Створіть змінніDim MyArray () As String, MyString As String, N As Integer, Temp As String'Налаштуйте рядок з адресою корпорації MicrosoftMyString = "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") = ТемпEnd Sub |
Цей приклад працює так само, як і попередній, за винятком того, що він створює тимчасовий рядок усіх елементів масиву, але вставляє символ подачі рядка після кожного елемента.
Робочий лист буде виглядати так після запуску коду:
Розділити рядок на клітинки аркуша
Ви можете скопіювати розділений масив у клітинки аркуша <> за допомогою однієї команди:
| 12345678910 | Sub CopyToRange ()'Створіть змінніDim MyArray () As String, MyString As String'Зразок рядка з роздільниками пробілівMyString = "Один, два, три, чотири, п'ять, шість"'Використовуйте функцію Split, щоб розділити складові частини рядкаMyArray = Розділити (MyString, ",")'Скопіюйте масив на аркушДіапазон ("A1: A" & UBound (MyArray) + 1) .Value = Робочий лист Функція. Транспонування (MyArray)End Sub |
Коли цей код буде запущено, ваш аркуш буде виглядати так:
Створення нової функції для дозволу поділу з заданої точки
Параметр "Обмеження" у функції "Розділити" дозволяє лише вказати верхню межу, де потрібно зупинити розділення. Він завжди починається з початку рядка.
Було б дуже корисно мати подібну функцію, де ви можете вказати початкову точку розколу всередині рядка та кількість розколів, які ви хочете бачити з цього моменту і далі. Він також буде витягувати лише ті розщеплення, які ви вказали в масиві, замість того, щоб мати величезне значення рядка як останній елемент у масиві.
Ви можете легко створити функцію (яка називається SplitSlicer) самостійно у VBA, щоб зробити це:
| 123456789101112131415161718192021222324 | Функція SplitSlicer (Target As String, Del As String, Start As Integer, N As Integer)'Створити змінну масивуDim MyArray () як рядок'Зафіксуйте розкол за допомогою змінної start, використовуючи символ роздільникаMyArray = Розділити (Target, Del, Start)"Перевірте, чи параметр запуску більший за кількість розколів - це може спричинити проблемиЯкщо Пуск> UBound (MyArray) + 1 Тоді"Помилка дисплея та вихід із функціїMsgBox "Параметр запуску більший за кількість доступних розділень"SplitSlicer = MyArrayФункція виходуЗакінчити Якщо'Вставте останній елемент масиву в рядокЦіль = MyArray (UBound (MyArray))'Розділіть рядок, використовуючи N як лімітMyArray = Розділити (Target, Del, N)"Переконайтеся, що верхня межа більше нуля, оскільки код видаляє останній елементЯкщо UBound (MyArray)> 0 Тоді'Використовуйте ReDim, щоб видалити останній елемент масивуReDim Preserve MyArray (UBound (MyArray) - 1)Закінчити Якщо'Повернути новий масивSplitSlicer = MyArrayФункція завершення |
Ця функція побудована з чотирма параметрами:
- Ціль - рядок - це вхідний рядок, який потрібно розділити
- Дель - рядок або недрукований символ - це символ роздільника, який ви використовуєте, наприклад кома, двокрапка
- Почати - номер - це початковий поділ для вашого фрагмента
- N - число - це кількість розколів, які ви хочете зробити у своєму фрагменті
Жоден із цих параметрів не є необов’язковим або має значення за замовчуванням, але ви можете використати це в коді функції, якщо хочете його розширити.
Функція використовує функцію Split для створення масиву, використовуючи параметр Start як межа. Це означає, що елементи масиву будуть утримувати розділення до початкового параметра, але залишок рядка буде останнім елементом і не буде розділений.
Останній елемент у масиві переноситься назад до рядка за допомогою функції UBound, щоб визначити, який це елемент.
Потім рядок знову розбивається на масив, використовуючи N як граничну змінну. Це означає, що для рядка буде зроблено розщеплення до позиції N, після чого решта рядка буде формувати останній елемент у масиві.
Оператор ReDim використовується для видалення останнього елемента, оскільки ми хочемо лише певних елементів, залишених у масиві. Зауважте, що використовується параметр Preserve, інакше всі дані в масиві будуть втрачені.
Потім новий масив повертається до коду, з якого він був викликаний.
Зауважте, що код "захищений від помилок". Користувачі часто роблять дивні речі, про які ви не думали. Наприклад, якщо вони спробують використати функцію з параметром Start або N, більшим за доступну кількість розколів у рядку, це, ймовірно, призведе до збою функції.
Код включений для перевірки початкового значення, а також для того, щоб переконатися, що є елемент, який можна видалити, коли оператор ReDim використовується в масиві.
Ось код для перевірки функції:
| 123456789101112 | Sub TestSplitSlicer ()'Створіть змінніDim MyArray () As String, MyString As String'Визначте зразок рядка з роздільниками комиMyString = "Один, два, три, чотири, п'ять, шість, сім, вісім, дев'ять, десять"'Використовуйте функцію Splitslicer для визначення нового масивуMyArray = SplitSlicer (MyString, ",", 4, 3)'Очистіть активний аркушActiveSheet.UsedRange.Clear'Скопіюйте масив на аркушДіапазон ("A1: A" & UBound (MyArray) + 1) .Value = Робочий лист Функція. Транспонування (MyArray)End Sub |
Запустіть цей код, і ваш аркуш буде виглядати так:
