Під час роботи з VBA в Excel може виникнути необхідність розділити рядок на різні частини на основі роздільника.
Наприклад, якщо у вас є адреса, ви можете використовувати функцію VBA Split, щоб отримати різні частини адреси, розділені комою (що в цьому випадку буде роздільником).
SPLIT - це вбудована рядкова функція в Excel VBA, яку можна використовувати для розділення текстового рядка на основі роздільника.
Функція Excel VBA SPLIT - Синтаксис
Розділити (вираз, [роздільник], [межа], [порівняти])
- Вираз: Це рядок, який потрібно розділити на основі роздільника. Наприклад, у прикладі адреси вся вираз буде виразом. Якщо це рядок нульової довжини (“”), функція SPLIT повертає порожній масив.
- Розмежувач: Це необов’язковий аргумент. Це роздільник, який використовується для поділу аргументу "Вираз". У нашому прикладі адреси кома - це роздільник, який використовується для поділу адреси на різні частини. Якщо ви не вказуєте цей аргумент, пробіл вважається роздільником за замовчуванням. Якщо ви надаєте рядок нульової довжини (“”), функція повертає весь рядок “Expression”.
- Обмеження: Це необов’язковий аргумент. Тут ви вказуєте загальну кількість підрядків, які потрібно повернути. Наприклад, якщо ви хочете повернути лише перші три підрядки з аргументу "Вираз", це буде 3. Якщо ви не вкажете цей аргумент, за замовчуванням буде -1, яке повертає всі підрядки.
- Порівняйте: Це необов’язковий аргумент. Тут ви вказуєте тип порівняння, який ви хочете, щоб функція SPLIT виконувала при оцінці підрядків. Доступні такі варіанти:
- Коли Порівняти дорівнює 0: Це бінарне порівняння. Це означає, що якщо ваш роздільник-це текстовий рядок (скажімо, ABC), то це буде з урахуванням регістру. "ABC" не буде рівним "abc".
- Коли Порівняти - 1: Це порівняння тексту. Це означає, що якщо ваш роздільник - це текстовий рядок (скажімо, ABC), то навіть якщо у рядку "Вираз" у вас є "abc", він буде розглядатися як роздільник.
Тепер, коли ми розглянули основи функції SPLIT, розглянемо кілька практичних прикладів.
Приклад 1 - Розділіть слова в реченні
Припустимо, у мене є текст - «Швидкий бурий лис стрибає через ледачого собаку».
Я можу використовувати функцію SPLIT, щоб кожне слово цього речення перетворити на окремий елемент у масиві.
Нижче наведений код стосується цього:
Sub SplitWords () Dim TextStrng As String Dim Result () As String TextStrng = "Швидкий бурий лис перестрибує через ледачого собаку" Результат () = Розділити (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 = "Швидка бура лисиця перестрибує через ледачого собаку" Результат = Розділити (TextStrng) WordCount = UBound (Result ()) + 1 MsgBox "The Кількість слів - це & & кінцевий підрядник WordCount
У цьому випадку функція 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, ",") For i = LBound (Result ()) To UBound (Result ()) DisplayText = DisplayText & Result (i) & vbNewLine Next i MsgBox DisplayText End Sub
У наведеному вище коді я використовував цикл For Next для перегляду кожного елемента масиву "Result", призначаючи його змінній "DisplayText".
Приклад 4 - Розділіть адресу на три частини
За допомогою функції SPLIT ви можете вказати, яку кількість розділів ви хочете отримати. Наприклад, якщо я нічого не вказую, кожен екземпляр роздільника буде використовуватися для поділу рядка.
Але якщо я вкажу 3 як межу, то рядок буде розділений лише на три частини.
Наприклад, якщо у мене є така адреса:
2703 Winifred Way, Indianapolis, Indiana, 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 (Результат ( )) До UBound (Результат ()) DisplayText = DisplayText & Результат (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, Indianapolis, Indiana, 46204
Отриманий масив виглядатиме так, як показано нижче:
{"2703 Winifred Way", "Indianapolis", "Indiana", "46204"}
Оскільки це масив, я можу вибрати відображення або повернення певної частини цього масиву.
Нижче наведено код для користувацької функції, де ви можете вказати номер, і він поверне цей елемент з масиву. Наприклад, якщо мені потрібна назва стану, я можу вказати 3 (оскільки це третій елемент у масиві).
Функція ReturnNthElement (CellRef As Range, ElementNumber As Integer) Dim Result () As String Result = Split (CellRef, ",") ReturnNthElement = Result (ElementNumber - 1) End Функція
Наведена вище функція приймає два аргументи: посилання на клітинку з адресою та номер елемента, який потрібно повернути. Функція Split розбиває елементи адреси і призначає її змінній Result.
Потім він повертає номер елемента, який ви вказали як другий аргумент. Зауважте, що оскільки база-0, ElementNumber-1 використовується для повернення правильної частини адреси.
Якщо вам потрібна назва міста, ви можете використовувати 2 як другий аргумент. Якщо ви використовуєте число, яке перевищує загальну кількість елементів, воно поверне #VALUE! помилка.
Ви можете ще більше спростити код, як показано нижче:
Функція ReturnNthElement (CellRef As Range, ElementNumber As Integer) ReturnNthElement = Розділити (CellRef, ",") (ElementNumber - 1) Кінцева функція
У наведеному вище коді замість використання змінної Result вона повертає лише вказаний номер елемента.
Отже, якщо у вас є Split (“Доброго ранку”) (0), він поверне лише перший елемент, який є “Good”.
Аналогічно, у наведеному вище коді він повертає лише вказаний номер елемента.
