Използване на функцията VBA Split
Функцията VBA Split ви позволява да отделите компонентите от стандартния текстов низ, където всеки компонент използва специфичен разделител, напр. запетая или двоеточие. По -лесно е да се използва, отколкото да се пише код за търсене на разделители в низа и след това извличане на стойностите.
Може да се използва, ако четете в ред от стойност, разделена със запетая (CSV файл) или имате пощенски адрес, който е изцяло на един ред, но искате да го видите като няколко реда.
Синтаксисът е:
| 1 | Разделяне на израз, разделител [по избор], ограничение [по избор], сравнение [по избор] |
Функцията VBA Split има четири параметъра:
- Израз - Низ текст, който искате да разделите на различни части.
- Разделител (по избор)- низ или символ, който не може да се отпечатва - Определя знака за разделител, който ще се използва за разделянето. Ако не е предоставен разделител, тогава се използва подразбиращото се интервал.
- Ограничение (по избор) - номер - Определя колко разделяния ще бъдат направени. Ако е празно, всички налични разделяния ще бъдат направени в низа. Ако е зададено на 1, няма да се правят разделяния. По принцип тя ви позволява да отделите определен брой стойности, започвайки от началото на низа, напр. където низът е много дълъг и имате нужда само от първите три разделяния.
- Сравнете (по избор) - Ако вашият разделител е текстов знак, той се използва за превключване дали разделителят е чувствителен към регистър или не. Стойностите са vbBinaryCompare (чувствителни към регистъра) и vbTextCompare (не чувствителни към регистъра).
Функцията за разделяне винаги връща масив.
Прост пример за функцията за разделяне
| 123456789101112 | Sub SplitExample ()'Определете променливиDim MyArray () As String, MyString As String, I As Variant'Примерен низ с разделители на интервалиMyString = "Едно две три четири"'Използвайте функцията Split, за да разделите съставните части на низаMyArray = Разделяне (MyString)'повторете през масива, създаден, за да покажете всяка стойностЗа всеки I In MyArrayMsgBox IСлед това азEnd Sub |
В този пример не е посочен разделител, тъй като всички думи имат интервал между тях, така че разделителят по подразбиране (интервал) може да се използва.
Масивът няма размери и е зададен като низ. Променливата I, която се използва в цикъла For… Next, трябва да бъде оразмерена като вариант.
Когато този код се изпълнява, той ще показва четири кутии за съобщения, по едно за всеки от разделянията, напр. Едно две три. Четири.
Имайте предвид, че ако между думите в низа има двойно интервал, това ще бъде оценено като разделяне, макар и без нищо в него. Това може да не е резултатът, който искате да видите.
Можете да отстраните този проблем, като използвате функцията Replace, за да замените всички двойни интервали с едно пространство:
| 1 | MyString = Замяна (MyString, "", "") |
Задно или водещо пространство също може да причини проблеми, като създаде празно разделяне. Те често се виждат много трудно. Можете да премахнете тези външни пространства, като използвате функцията Trim:
| 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 |
След като стартирате този код, вашият работен лист ще изглежда така:
Само първите три стойности на разделяне се показват отделно. По -късните три стойности се показват като един дълъг низ и не се разделят.
Ако изберете гранична стойност, която е по -голяма от броя на разделителите в низ, това няма да доведе до грешка. Низът ще бъде разделен на всички негови компоненти, сякаш граничната стойност не е предоставена.
Използване на параметъра за сравнение в функция за разделяне
Параметърът за сравнение определя дали разделителят е чувствителен към регистър или не. Това не е приложимо, ако разделителите са запетаи, запетаи или двоеточия.
Забележка: Вместо това винаги можете да поставите Option Compare Text <> в горната част на вашия модул, за да премахнете чувствителността към регистъра за целия модул.
| 123456789101112131415 | Sub SplitByCompareExample ()„Създаване на променливиDim MyArray () As String, MyString As String, I As Variant, N As Integer'Примерен низ с X разделителиMyString = "OneXTwoXThreexFourXFivexSix"'Използвайте функцията Split, за да разделите съставните части на низаMyArray = Разделяне (MyString, "X",, vbBinaryCompare)„Изчистете работния листActiveSheet.UsedRange.Clear'повторете през масиваЗа N = 0 към UBound (MyArray)„Поставете всяко разделяне в първата колона на работния листДиапазон ("A" & N + 1). Значение = MyArray (N)Следва NEnd Sub |
В този пример низът за разделяне използва знака „X“ като разделител. В този низ обаче има смес от главни и малки букви „X“. Параметърът Сравни във функцията за разделяне използва главен знак „X“.
Ако параметърът Compare е зададен на vbBinaryCompare, тогава малките букви ‘x’ ще бъдат игнорирани и работният ви лист ще изглежда така:
Ако параметърът Compare е зададен на 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 ще се присъедини отново към всички елементи на масив, но с помощта на определен разделител. Ако не е посочен разделител, ще се използва интервал.
| 123456789101112131415 | Sub JoinExample ()„Създаване на променливиDim MyArray () As String, MyString As String, I As Variant, N As IntegerDim Target As String'Примерен низ с разделители на запетаиMyString = "Едно, две, три, четири, пет, шест"„Поставете MyString в клетка A1Диапазон ("A1"). Стойност = MyString'Използвайте функцията Split, за да разделите съставните части на низаMyArray = Разделяне (MyString, ",")„Използвайте функцията за присъединяване, за да създадете отново оригиналния низ, като използвате разделител с запетая и двоеточиеTarget = Присъединяване (MyArray, ”;”)'Поставете низа на резултата в клетка A2Обхват ("A2"). Стойност = ЦелEnd Sub |
Този код разделя низ с разделители на запетаи в масив и го свързва отново с помощта на разделители с запетая.
След като стартирате този код, вашият работен лист ще изглежда така:
Клетка А1 има оригиналния низ с разделители на запетаи, а клетка А2 има новия съединен низ с разделители с запетая.
Използване на функцията за разделяне за преброяване на думи
Като се има предвид, че низовата променлива в Excel VBA може да бъде с дължина до 2 Gb, можете да използвате функцията за разделяне, за да преброите думите в част от текст. Очевидно 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 | Под Адрес Пример ()„Създаване на променливиDim MyArray () като низ, MyString като низ, N като цяло число„Настройте низ с адрес на Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 САЩ"„Използвайте функцията за разделяне, за да разделите низа с разделител на запетаиMyArray = Разделяне (MyString, ",")„Изчистете работния листActiveSheet.UsedRange.Clear'повторете през масиваЗа N = 0 към UBound (MyArray)„Поставете всяко разделяне в първата колона на работния листДиапазон ("A" & N + 1). Значение = MyArray (N)Следва NEnd Sub |
Изпълнението на този код ще използва разделител на запетаи, за да постави всеки ред от адреса в отделна клетка:
Ако искате само да върнете пощенския код (последния елемент на масива), тогава можете да използвате кода:
| 123456789101112 | Под -адрес ZipCodeExample ()„Създаване на променливиDim MyArray () As String, MyString As String, N As Integer, Temp As String„Настройте низ с адрес на Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 САЩ"„Използвайте функцията за разделяне, за да разделите низа с разделител на запетаиMyArray = Разделяне (MyString, ",")„Изчистете работния листActiveSheet.UsedRange.Clear„Поставете пощенския код в клетка A1Диапазон ("A1"). Стойност = MyArray (UBound (MyArray))End Sub |
Това ще използва само последния елемент в масива, който се намира с помощта на функцията UBound.
От друга страна, може да искате да видите всички редове в една клетка, за да могат да бъдат отпечатани върху етикет на адреса:
| 1234567891011121314151617 | Под Адрес Пример ()„Създаване на променливиDim MyArray () As String, MyString As String, N As Integer, Temp As String„Настройте низ с адрес на Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 САЩ"„Използвайте функцията за разделяне, за да разделите низа с разделител на запетаиMyArray = Разделяне (MyString, ",")„Изчистете работния листActiveSheet.UsedRange.Clear'повторете през масиваЗа N = 0 към UBound (MyArray)'поставете всеки елемент от масива плюс знак за подаване на ред в низTemp = Temp & MyArray (N) & vbLfСледва N„Поставете низа върху работния листДиапазон ("A1") = ТемпEnd Sub |
Този пример работи по същия начин като по -ранния, с изключение на това, че създава временен низ от всички елементи на масива, но вмъква знак за емисия на ред след всеки елемент.
Работният лист ще изглежда така след стартирането на кода:
Разделете низа на клетки на работен лист
Можете да копирате масива Split в клетки на работен лист <> само с една команда:
| 12345678910 | Sub CopyToRange ()„Създаване на променливиDim MyArray () As String, MyString As String'Примерен низ с разделители на интервалиMyString = "Едно, две, три, четири, пет, шест"'Използвайте функцията Split, за да разделите съставните части на низаMyArray = Разделяне (MyString, ",")'Копирайте масива в работния листДиапазон ("A1: A" & UBound (MyArray) + 1) .Value = Работен лист Функция. Транспониране (MyArray)End Sub |
Когато този код е пуснат, вашият работен лист ще изглежда така:
Създаване на нова функция, позволяваща разделяне от дадена точка
Параметърът Limit във функцията Разделяне ви позволява само да посочите горна граница, където искате разделянето да спре. Винаги започва от началото на низа.
Би било много полезно да имате подобна функция, където можете да посочите началната точка на разделянето в низа и броя на разделянията, които искате да видите от тази точка нататък. Той също така ще извлече само разделенията, които сте посочили в масива, вместо да има огромна стойност на низ като последен елемент в масива.
Можете лесно да създадете функция (наречена SplitSlicer) във VBA, за да направите това:
| 123456789101112131415161718192021222324 | Функция SplitSlicer (Target As String, Del As String, Start As Integer, N As Integer)'Създаване на променлива на масиваDim MyArray () As String'Уловете разделянето, като използвате променливата start, като използвате знака за разделителMyArray = Разделяне (Target, Del, Start)„Проверете дали началният параметър е по -голям от броя на разделянията - това може да причини проблемиАко 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Крайна функция |
Тази функция е изградена с четири параметъра:
- Цел - низ - това е входният низ, който искате да разделите
- Дел - низ или непечатаем знак - това е разделителният знак, който използвате напр. запетая, двоеточие
- Старт - номер - това е началното разделяне за вашата филия
- н - номер - това е броят на разделянията, които искате да направите в рамките на вашата част
Нито един от тези параметри не е задължителен или има стойности по подразбиране, но можете да го използвате в кода на функцията, ако искате да го разширите допълнително.
Функцията използва функцията 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 |
Пуснете този код и вашият работен лист ще изглежда така:
