podczas pracy z VBA w Excelu może być konieczne podzielenie ciągu znaków na różne części na podstawie ogranicznika.
na przykład, jeśli masz adres, możesz użyć funkcji VBA Split, aby uzyskać różne części adresu, które są oddzielone przecinkiem(który byłby w tym przypadku ogranicznikiem).
SPLIT to wbudowana funkcja łańcuchowa w programie Excel VBA, której można użyć do podzielenia ciągu tekstowego na podstawie ogranicznika.,
Ten samouczek obejmuje:
składnia funkcji dzielenia Excela VBA
Split ( Expression, , , )
- wyrażenie: jest to ciąg znaków, który chcesz podzielić na podstawie ogranicznika. Na przykład, w przypadku przykładu adresu, cały adres byłby „wyrażeniem”. W przypadku, gdy jest to łańcuch o zerowej długości ( „” ) funkcja SPLIT zwróci pustą tablicę.
- Delimiter: jest to opcjonalny argument. Jest to ogranicznik używany do dzielenia argumentu 'Expression'., W naszym przykładzie adresu przecinek jest ogranicznikiem, który jest używany do dzielenia adresu na różne części. Jeśli nie podasz tego argumentu, znak spacji jest uważany za domyślny ogranicznik. W przypadku, gdy podasz łańcuch o zerowej długości ( „” ), cały łańcuch 'Expression' jest zwracany przez funkcję.
- Limit: jest to opcjonalny argument. Tutaj określasz całkowitą liczbę podciągów, które chcesz zwrócić. Na przykład, jeśli chcesz zwrócić tylko trzy pierwsze podłańcuchy z argumentu 'Expression', będzie to 3., Jeśli nie podasz tego argumentu, domyślną wartością jest -1, która zwraca wszystkie podłańcuchy.
- Compare: jest to opcjonalny argument. Tutaj można określić typ porównania, który ma być wykonywany przez funkcję SPLIT podczas oceniania podłańcuchów. Dostępne są następujące opcje:
- gdy Compare wynosi 0: jest to porównanie binarne. Oznacza to, że jeśli ogranicznik jest ciągiem tekstowym (powiedzmy ABC), to rozróżnia się wielkość liter. „ABC” nie byłoby równe „abc”.
- gdy Compare wynosi 1: jest to porównanie tekstu., Oznacza to, że jeśli twój ogranicznik jest ciągiem tekstowym (powiedzmy ABC), to nawet jeśli masz 'abc' w ciągu' Expression', będzie on uważany za ogranicznik.
teraz, gdy omówiliśmy podstawy funkcji SPLIT, zobaczmy kilka praktycznych przykładów.
przykład 1 – Podziel słowa w zdaniu
Załóżmy, że mam tekst – „szybki brązowy lis przeskakuje nad leniwym psem”.
mogę użyć funkcji SPLIT, aby uzyskać każde słowo tego zdania jako oddzielny element w tablicy.,
poniższy kod byłby taki:
Sub SplitWords()Dim TextStrng As StringDim Result() As StringTextStrng = "The Quick Brown Fox Jumps Over The Lazy Dog"Result() = Split(TextStrng)End Sub
chociaż kod nie robi nic użytecznego, pomoże Ci zrozumieć, co robi funkcja Split w VBA.
funkcja Split dzieli łańcuch tekstowy i przypisuje każde słowo do tablicy wyników.
Tak więc w tym przypadku:
- Result(0) przechowuje wartość „The”
- Result(1) przechowuje wartość „Quick”
- Result(2) przechowuje wartość „Brown” i tak dalej.
w tym przykładzie podaliśmy tylko pierwszy argument – który jest tekstem do podziału., Ponieważ nie podano ogranicznika, jako domyślny ogranicznik przyjmuje znak spacji.
Ważna uwaga:
- funkcja VBA SPLIT zwraca tablicę zaczynającą się od podstawy 0.
- gdy wynik funkcji SPLIT jest przypisany do tablicy, tablica ta musi być zadeklarowana jako typ danych String. Jeśli zadeklarujesz go jako typ danych Variant, wyświetli się błąd niedopasowania typu). W powyższym przykładzie zauważ, że zadeklarowałem metodę Result () jako typ danych String.,
przykład 2 – policz liczbę słów w zdaniu
Możesz użyć funkcji SPLIT, aby uzyskać całkowitą liczbę słów w zdaniu. Sztuczka polega na policzeniu liczby elementów w tablicy, które otrzymujesz po podzieleniu tekstu.
poniższy kod wyświetli okno z liczbą słów:
Sub WordCount()Dim TextStrng As StringDim WordCount As IntegerDim Result() As StringTextStrng = "The Quick Brown Fox Jumps Over The Lazy Dog"Result = Split(TextStrng)WordCount = UBound(Result()) + 1MsgBox "The Word Count is " & WordCountEnd Sub
w tym przypadku funkcja UBound informuje nas o górnej granicy tablicy (tzn. maksymalnej liczbie elementów tablicy)., Ponieważ baza tablicy wynosi 0, dodaje się 1, Aby uzyskać całkowitą liczbę słów.
możesz użyć podobnego kodu, aby utworzyć niestandardową funkcję w VBA, która przyjmie tekst jako wejście i zwróci liczbę słów.
poniższy kod utworzy tę funkcję:
Po utworzeniu możesz używać funkcji WordCount tak jak każdej innej zwykłej funkcji.
Ta funkcja obsługuje również początkowe, końcowe i podwójne spacje między słowami. Stało się to możliwe dzięki zastosowaniu funkcji TRIM w kodzie VBA.,
Jeśli chcesz dowiedzieć się więcej o tym, jak działa ta formuła, aby policzyć liczbę słów w zdaniu lub chcesz dowiedzieć się o formule innej niż VBA, aby uzyskać liczbę słów, sprawdź ten samouczek.
przykład 3-używając ogranicznika innego niż znak spacji
w poprzednich dwóch przykładach użyliśmy tylko jednego argumentu w funkcji SPLIT, a pozostałe były argumentami domyślnymi.
gdy używasz innego ogranicznika, musisz to określić w formule SPLIT.,
w poniższym kodzie funkcja SPLIT zwraca tablicę opartą na przecinku jako ograniczniku, a następnie wyświetla komunikat z każdym słowem w osobnej linii.
w powyższym kodzie użyłem pętli For Next, aby przejść przez każdy element tablicy 'Result' przypisując go do zmiennej 'DisplayText'.
przykład 4 – Podziel adres na trzy części
za pomocą funkcji SPLIT możesz określić, ile liczb splitów chcesz uzyskać. Na przykład, jeśli niczego nie podam, każda instancja ogranicznika zostanie użyta do podzielenia łańcucha.,
ale jeśli podam 3 jako limit, wtedy łańcuch zostanie podzielony tylko na trzy części.
na przykład, jeśli mam następujący adres:
2703 Winifred Way, Indianapolis, Indiana, 46204
mogę użyć funkcji Split w VBA, aby podzielić ten adres na trzy części.
dzieli pierwsze dwa na podstawie separatora przecinków, a pozostała część staje się trzecim elementem tablicy.,
poniższy kod pokazuje adres w trzech różnych liniach w polu wiadomości:
jednym z praktycznych zastosowań tego może być podzielenie adresu pojedynczej linii na format pokazany w polu wiadomości. Następnie można utworzyć funkcję niestandardową, która zwraca adres podzielony na trzy części (z każdej części w nowej linii).
poniższy kod zrobi to:
gdy masz ten kod w module, możesz użyć funkcji (ThreePartAddress) w skoroszycie, tak jak każda inna funkcja programu Excel.,
Ta funkcja przyjmuje jeden argument – odniesienie do komórki, która ma adres.
zauważ, że aby adres wynikowy pojawił się w trzech różnych wierszach, musisz zastosować format tekstowy zawijania do komórek (znajduje się on na karcie Strona główna w grupie wyrównanie). Jeśli format „Zawijaj tekst” nie jest włączony, zobaczysz cały adres jako jedną linię.
przykład 5 – Pobierz nazwę miasta z adresu
dzięki funkcji Split w VBA możesz określić, jakiej części tablicy wynikowej chcesz użyć.,
na przykład, załóżmy, że dzielę następujący adres na podstawie przecinka jako ogranicznika:
2703 Winifred Way, Indianapolis, Indiana, 46204
wynikowa tablica będzie wyglądać tak, jak pokazano poniżej:
{"2703 Winifred Way", "Indianapolis", "Indiana", "46204"}
ponieważ jest to tablica, mogę wybrać wyświetlenie lub zwrócenie określonej części tej tablicy.
Poniżej znajduje się kod funkcji niestandardowej, w której można podać liczbę i zwróci ona ten element z tablicy. Na przykład, jeśli chcę nazwę stanu, mogę podać 3 (ponieważ jest to trzeci element w tablicy).,
powyższa funkcja pobiera dwa argumenty, odniesienie do komórki, które ma adres i numer elementu, który chcesz zwrócić. Funkcja Split dzieli elementy adresu i przypisuje je do zmiennej wynikowej.
następnie zwraca numer elementu, który podałeś jako drugi argument. Zauważ, że ponieważ bazą jest 0, ElementNumber – 1 jest używany do zwrócenia poprawnej części adresu.
Jeśli chcesz podać nazwę miasta, możesz użyć 2 jako drugiego argumentu. W przypadku użycia liczby, która jest wyższa niż całkowita liczba elementów, zwróci wartość#! błąd.
możesz dodatkowo uprościć Kod, jak pokazano poniżej:
w powyższym kodzie, zamiast używać zmiennej wynikowej, zwraca ona tylko określony numer elementu.
więc jeśli masz Split („Dzień dobry”) (0), zwróci tylko pierwszy element, który jest „dobry”.,
podobnie w powyższym kodzie zwraca tylko podany numer elementu.
Możesz również polubić poniższe samouczki programu Excel:
- funkcja Excel VBA InStr – wyjaśniona przykładami.
- jak sortować dane w Excelu za pomocą VBA (przewodnik krok po kroku).
- 7 niesamowitych rzeczy, które Excel Text to Columns może zrobić dla Ciebie.
- Jak uzyskać liczbę słów w Excelu.
- funkcja VBA TRIM.