Funkcja podziału Excel VBA - wyjaśniona z przykładami

Podczas pracy z VBA w programie Excel może być konieczne podzielenie ciągu 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 oddzielone przecinkiem (który byłby w tym przypadku ogranicznikiem).

SPLIT to wbudowana funkcja ciągu w Excel VBA, której można użyć do podzielenia ciągu tekstowego na podstawie ogranicznika.

Funkcja dzielenia Excel VBA - składnia

Podziel ( Wyrażenie, [Ogranicznik], [Limit], [Porównaj] )

• Wyrażenie: To jest ciąg, który chcesz podzielić na podstawie ogranicznika. Na przykład w przypadku przykładowego 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ę.
• Ogranicznik: To jest argument opcjonalny. Jest to ogranicznik używany do dzielenia argumentu „Wyrażenie”. W przypadku naszego przykładu adresu przecinek jest separatorem używanym do dzielenia adresu na różne części. Jeśli nie określisz tego argumentu, znak spacji jest uważany za domyślny ogranicznik. W przypadku podania ciągu o zerowej długości („”), funkcja zwraca cały ciąg „Wyrażenie”.
• Limit: To jest argument opcjonalny. Tutaj określasz całkowitą liczbę podciągów, które chcesz zwrócić. Na przykład, jeśli chcesz zwrócić tylko pierwsze trzy podciągi z argumentu „Wyrażenie”, będzie to 3. Jeśli nie określisz tego argumentu, wartością domyślną jest -1, która zwraca wszystkie podciągi.
• Porównywać: To jest argument opcjonalny. Tutaj określasz typ porównania, które funkcja SPLIT ma wykonać podczas oceny podciągów. Dostępne są następujące opcje:
  • Gdy Porównaj wynosi 0: To jest porównanie binarne. Oznacza to, że jeśli twój ogranicznik jest ciągiem tekstowym (powiedzmy ABC), to rozróżniana jest wielkość liter. „ABC” nie byłoby równe „abc”.
  • Gdy Porównaj wynosi 1: To jest porównanie tekstowe. Oznacza to, że jeśli twój ogranicznik jest ciągiem tekstowym (powiedzmy ABC), to nawet jeśli masz „abc” w ciągu „Wyrażenie”, zostanie on uznany 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 mógłby to zrobić:

Sub SplitWords() Dim TextStrng As String Dim Result() As String TextStrng = "Szybki brązowy lis przeskakuje nad leniwym psem" 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 ciąg tekstowy i przypisuje każde słowo do tablicy wyników.

Więc w tym przypadku:

• Wynik (0) przechowuje wartość „The”
• Wynik(1) przechowuje wartość „Szybko”
• Wynik(2) przechowuje wartość „Brown” i tak dalej.

W tym przykładzie określiliśmy tylko pierwszy argument, którym jest tekst do podziału. Ponieważ nie określono ogranicznika, jako ogranicznik domyślny przyjmuje znak spacji.

Ważna uwaga:

1. Funkcja VBA SPLIT zwraca tablicę, która zaczyna się od podstawy 0.
2. 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 błąd niezgodności typu). W powyższym przykładzie zauważ, że zadeklarowałem 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 tym, aby policzyć liczbę elementów w tablicy, którą otrzymasz po podzieleniu tekstu.

Poniższy kod wyświetli okno komunikatu z liczbą słów:

Sub WordCount() Dim TextStrng As String Dim WordCount As Integer Dim Result() As String TextStrng = "Szybki brązowy lis przeskakuje nad leniwym psem" Wynik = Split(TextStrng) WordCount = UBound(Result()) + 1 MsgBox "The Liczba wyrazów to " & Koniec liczby słów napisów

W tym przypadku funkcja UBound informuje nas o górnej granicy tablicy (tj. o maksymalnej liczbie elementów tablicy). Ponieważ podstawą tablicy jest 0, dodawane jest 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 dane wejściowe i zwróci liczbę słów.

Poniższy kod utworzy tę funkcję:

Funkcja WordCount(CellRef As Range) Dim TextStrng As String Dim Wynik() As String Wynik = Split(WorksheetFunction.Trim(CellRef.Text), " ") WordCount = UBound(Result()) + 1 funkcja końcowa

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 wykorzystaniu 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 sposobie formułowania innym niż VBA, aby uzyskać liczbę słów, zapoznaj się z tym samouczkiem.

Przykład 3 - użycie ogranicznika innego niż spacja

W poprzednich dwóch przykładach użyliśmy tylko jednego argumentu w funkcji SPLIT, a reszta była 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 osobnym wierszu.

Sub CommaSeparator() Dim TextStrng As String Dim Result() As String Dim DisplayText As String TextStrng = "The,Quick,Brown,Fox, Jump,over,The,Lazy,Pies" Wynik = Split(TextStrng, ",") Dla i = LBound(Result()) To UBound(Result()) DisplayText = DisplayText i Wynik(i) & vbNewLine Next i MsgBox DisplayText End Sub

W powyższym kodzie użyłem pętli For Next, aby przejść przez każdy element tablicy „Result” i przypisać go do zmiennej „DisplayText”.

Przykład 4 - Podziel adres na trzy części

Za pomocą funkcji SPLIT możesz określić, ile podziałów chcesz uzyskać. Na przykład, jeśli nic nie określę, każde wystąpienie ogranicznika zostanie użyte do podzielenia ciągu.

Ale jeśli jako limit określę 3, ciąg 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 przecinka, a pozostała część staje się trzecim elementem tablicy.

Poniższy kod pokaże adres w trzech różnych wierszach w oknie komunikatu:

Sub CommaSeparator() Dim TextStrng As String Dim Result() As String Dim DisplayText As String TextStrng = "2703 Winifred Way, Indianapolis, Indiana, 46204" Wynik = Split(TextStrng, ",", 3) For i = LBound(Result( )) To UBound(Result()) DisplayText = DisplayText & Result(i) & vbNewLine Next i MsgBox DisplayText End Sub

Jednym z praktycznych zastosowań tego może być podzielenie adresu jednowierszowego na format pokazany w oknie komunikatu. Następnie możesz utworzyć funkcję niestandardową, która zwraca adres podzielony na trzy części (każda część w nowej linii).

Poniższy kod zrobi to:

Function ThreePartAddress(cellRef As Range) Dim TextStrng As String Dim Result() As String Dim DisplayText As String Wynik = Split(cellRef, ",", 3) For i = LBound(Result()) To UBound(Result()) DisplayText = DisplayText & Trim(Result(i)) & vbNewLine Next i ThreePartAddress = Mid(DisplayText, 1, Len(DisplayText) - 1) Koniec funkcji

Gdy już masz ten kod w module, możesz użyć funkcji (TrzyPartAddress) w skoroszycie, tak jak każdej innej funkcji programu Excel.

Ta funkcja przyjmuje jeden argument - odwołanie do komórki zawierającej adres.

Zwróć uwagę, że aby wynikowy adres pojawił się w trzech różnych wierszach, musisz zastosować format tekstu zawijania do komórek (znajduje się 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 - Uzyskaj nazwę miasta z adresu

Dzięki funkcji Split w VBA możesz określić, której części wynikowej tablicy chcesz użyć.

Załóżmy na przykład, że dzielę następujący adres na podstawie przecinka jako separatora:

2703 Winifred Way, Indianapolis, Indiana, 46204

Wynikowa tablica wyglądałaby tak, jak pokazano poniżej:

{"2703 Droga Winifred", "Indianapolis", "Indiana", "46204"}

Ponieważ jest to tablica, mogę wybrać wyświetlanie lub zwracanie określonej części tej tablicy.

Poniżej znajduje się kod funkcji niestandardowej, w której możesz określić liczbę, która zwróci ten element z tablicy. Na przykład, jeśli chcę nazwę stanu, mogę określić 3 (ponieważ jest to trzeci element w tablicy).

Funkcja ReturnNthElement(CellRef As Range, ElementNumber As Integer) Dim Wynik() As String Wynik = Split(CellRef, ",") ReturnNthElement = Result(ElementNumber - 1) Koniec funkcji

Powyższa funkcja przyjmuje dwa argumenty, odwołanie do komórki zawierające adres i numer elementu, który chcesz zwrócić. Funkcja Split dzieli elementy adresu i przypisuje je do zmiennej Result.

Następnie zwraca numer elementu określony jako drugi argument. Zauważ, że ponieważ podstawą jest 0, ElementNumber-1 jest używany do zwrócenia prawidłowej części adresu.

Ta niestandardowa formuła najlepiej nadaje się, gdy masz spójny format w całym adresie – tzn. miasto jest zawsze wymieniane po dwóch przecinkach. Jeśli dane nie są spójne, nie uzyskasz pożądanego rezultatu.

Jeśli chcesz nazwę miasta, możesz użyć 2 jako drugiego argumentu. Jeśli użyjesz liczby, która jest większa niż całkowita liczba elementów, zwróci #ARG! błąd.

Możesz jeszcze bardziej uprościć kod, jak pokazano poniżej:

Funkcja ReturnNthElement(CellRef jako zakres, ElementNumber jako liczba całkowita) ReturnNthElement = Split(CellRef, ",")(ElementNumber - 1) Funkcja zakończenia

W powyższym kodzie zamiast używać zmiennej Result zwraca tylko określony numer elementu.

Więc jeśli masz Split("Dzień dobry")(0), zwróci tylko pierwszy element, którym jest "Dobry".

Podobnie w powyższym kodzie zwraca tylko określony numer elementu.