Sử dụng chức năng tách VBA
Chức năng VBA Split cho phép bạn tách các phần thành phần ra khỏi chuỗi văn bản tiêu chuẩn trong đó mỗi thành phần sử dụng một ký tự phân tách cụ thể, ví dụ: dấu phẩy hoặc dấu hai chấm. Nó dễ sử dụng hơn là viết mã để tìm kiếm các dấu phân cách trong chuỗi và sau đó trích xuất các giá trị.
Nó có thể được sử dụng nếu bạn đang đọc trong một dòng từ Giá trị được phân tách bằng dấu phẩy (tệp CSV) hoặc bạn có địa chỉ gửi thư nằm trên một dòng, nhưng bạn muốn xem nó dưới dạng nhiều dòng.
Cú pháp là:
| 1 | Biểu thức phân tách, dấu phân cách [tùy chọn], giới hạn [tùy chọn], so sánh [tùy chọn] |
Hàm Split VBA có bốn tham số:
- Biểu hiện - Chuỗi văn bản mà bạn muốn chia thành các phần khác nhau.
- Dấu phân cách (không bắt buộc)- chuỗi hoặc ký tự không in được - Xác định ký tự phân tách sẽ được sử dụng để tách. Nếu không có ký tự phân tách nào được cung cấp thì mặc định của khoảng trắng sẽ được sử dụng.
- Giới hạn (không bắt buộc) - number - Xác định số lần chia sẽ được thực hiện. Nếu để trống, tất cả các phần tách có sẵn sẽ được thực hiện trong chuỗi. Nếu nó được đặt thành 1, thì sẽ không có sự phân tách nào được thực hiện. Về cơ bản, nó cho phép bạn tách ra một số giá trị cụ thể bắt đầu từ đầu chuỗi, ví dụ: trong đó chuỗi rất dài và bạn chỉ cần ba lần tách đầu tiên.
- Đối chiếu (không bắt buộc) - Nếu dấu phân cách của bạn là một ký tự văn bản thì nó được sử dụng để chuyển đổi xem dấu phân cách có phân biệt chữ hoa chữ thường hay không. Các giá trị là vbBinaryCompare (phân biệt chữ hoa chữ thường) và vbTextCompare (không phân biệt chữ hoa chữ thường).
Hàm phân tách luôn trả về một mảng.
Ví dụ đơn giản về hàm phân tách
| 123456789101112 | Sub SplitExample ()'Xác định các biếnDim MyArray () As String, MyString As String, I As Variant'Chuỗi mẫu có dấu cáchMyString = "Một Hai Ba Bốn"'Sử dụng hàm Split để phân chia các phần thành phần của chuỗiMyArray = Tách (MyString)'lặp qua mảng được tạo để hiển thị từng giá trịCho mỗi tôi trong MyArrayMsgBox ITiếp theo tôiKết thúc Sub |
Trong ví dụ này, không có dấu phân cách nào được chỉ định vì tất cả các từ đều có khoảng trắng ở giữa chúng, do đó có thể sử dụng dấu phân cách mặc định (dấu cách).
Mảng không có thứ nguyên và được đặt dưới dạng chuỗi. Biến I, được sử dụng trong vòng lặp For… Next phải được ghi kích thước như một biến thể.
Khi mã này được chạy, nó sẽ hiển thị bốn hộp thông báo, một hộp cho mỗi phần tách, ví dụ: Một hai ba. Bốn.
Lưu ý rằng nếu có khoảng trắng kép giữa các từ trong chuỗi, điều này sẽ được đánh giá là phân tách, mặc dù không có gì trong đó. Đây có thể không phải là kết quả mà bạn muốn xem.
Bạn có thể khắc phục sự cố này bằng cách sử dụng chức năng Replace để thay thế bất kỳ khoảng trắng kép nào bằng một khoảng trắng duy nhất:
| 1 | MyString = Thay thế (MyString, "", "") |
Khoảng trống ở cuối hoặc ở đầu cũng có thể gây ra vấn đề bằng cách tạo ra một khoảng tách trống. Chúng thường rất khó nhìn thấy. Bạn có thể loại bỏ các khoảng trắng không liên quan này bằng cách sử dụng chức năng Trim:
| 1 | MyString = Trim (MyString) |
Sử dụng chức năng phân tách với ký tự phân tách
Chúng ta có thể sử dụng dấu phân cách dấu chấm phẩy (;). Điều này thường được tìm thấy trong chuỗi địa chỉ email để tách các địa chỉ. Bạn có thể có một email được gửi cho bạn và được chia sẻ với một số đồng nghiệp và bạn muốn xem danh sách trong bảng tính của mình về những người mà nó đã được chuyển đến tay bạn. Bạn có thể dễ dàng sao chép địa chỉ email từ hộp email "Tới" hoặc "Sao chép" và vào mã của mình.
| 123456789101112131415 | Sub SplitBySemicolonExample ()'Xác định các biếnDim MyArray () Dưới dạng chuỗi, MyString dưới dạng chuỗi, Tôi là biến thể, N là số nguyên'Chuỗi mẫu có dấu phân cách bằng dấu chấm phẩyMyString = "[email protected]; [email protected]; [email protected]; [email protected]"'Sử dụng hàm Split để phân chia các phần thành phần của chuỗiMyArray = Tách (MyString, ";")'Xóa trang tínhActiveSheet.UsedRange.Clear'lặp qua mảngĐối với N = 0 Đến UBound (MyArray)'Đặt từng địa chỉ email vào cột đầu tiên của trang tínhPhạm vi ("A" & N + 1) .Value = MyArray (N)N tiếp theoKết thúc Sub |
Lưu ý rằng vòng lặp For… Next được sử dụng để lặp qua mảng. Phần tử đầu tiên trong mảng luôn bắt đầu bằng 0 và hàm Upper Bound được sử dụng để lấy số phần tử tối đa.
Sau khi chạy mã này, trang tính của bạn sẽ giống như sau:
Sử dụng Tham số giới hạn trong Hàm phân tách
Tham số giới hạn cho phép một số lần tách cụ thể được thực hiện từ đầu chuỗi. Thật không may, bạn không thể cung cấp vị trí bắt đầu hoặc phạm vi phân chia để thực hiện, vì vậy nó khá cơ bản. Bạn có thể xây dựng mã VBA của riêng mình để tạo một hàm để thực hiện việc này và điều này sẽ được giải thích ở phần sau của bài viết này.
| 123456789101112131415 | Sub SplitWithLimitExample ()'Tạo các biếnDim MyArray () Dưới dạng chuỗi, MyString dưới dạng chuỗi, Tôi là biến thể, N là số nguyên'Chuỗi mẫu có dấu phân cách bằng dấu phẩyMyString = "Một, Hai, Ba, Bốn, Năm, Sáu"'Sử dụng hàm Split để phân chia các phần thành phần của chuỗiMyArray = Tách (MyString, ",", 4)'Xóa trang tínhActiveSheet.UsedRange.Clear'Lặp lại qua mảngĐối với N = 0 Đến UBound (MyArray)'Đặt từng phần tách vào cột đầu tiên của trang tínhPhạm vi ("A" & N + 1) .Value = MyArray (N)N tiếp theoKết thúc Sub |
Sau khi bạn chạy mã này, trang tính của bạn sẽ giống như sau:
Chỉ ba giá trị phân tách đầu tiên được hiển thị riêng biệt. Ba giá trị sau đó được hiển thị dưới dạng một chuỗi dài và không bị phân tách.
Nếu bạn chọn giá trị giới hạn lớn hơn số lượng dấu phân cách trong một chuỗi, điều này sẽ không tạo ra lỗi. Chuỗi sẽ được chia thành tất cả các phần thành phần của nó như thể giá trị giới hạn không được cung cấp.
Sử dụng Tham số So sánh trong Hàm Phân tách
Tham số So sánh xác định xem dấu phân cách có phân biệt chữ hoa chữ thường hay không. Điều này không thể áp dụng nếu các dấu phân cách là dấu phẩy, dấu chấm phẩy hoặc dấu hai chấm.
Lưu ý: Thay vào đó, bạn luôn có thể đặt Văn bản So sánh Tùy chọn <> ở đầu mô-đun của mình để loại bỏ phân biệt chữ hoa chữ thường cho toàn bộ mô-đun.
| 123456789101112131415 | Sub SplitByCompareExample ()'Tạo các biếnDim MyArray () Dưới dạng chuỗi, MyString dưới dạng chuỗi, Tôi là biến thể, N là số nguyên'Chuỗi mẫu có dấu phân cách XMyString = "OneXTwoXThreexFourXFivexSix"'Sử dụng hàm Split để phân chia các phần thành phần của chuỗiMyArray = Split (MyString, "X", vbBinaryCompare)'Xóa trang tínhActiveSheet.UsedRange.Clear'lặp qua mảngĐối với N = 0 Đến UBound (MyArray)'Đặt từng phần tách vào cột đầu tiên của trang tínhPhạm vi ("A" & N + 1) .Value = MyArray (N)N tiếp theoKết thúc Sub |
Trong ví dụ này, chuỗi được phân tách sử dụng ký tự ‘X’ làm dấu phân cách. Tuy nhiên, trong chuỗi này, có một hỗn hợp các ký tự ‘X’ viết hoa và viết thường. Tham số Compare trong hàm Split sử dụng ký tự ‘X’ viết hoa.
Nếu tham số So sánh được đặt thành vbBinaryCompare, thì các ký tự ‘x’ viết thường sẽ bị bỏ qua và trang tính của bạn sẽ giống như sau:
Nếu tham số So sánh được đặt thành vbTextCompare, thì các ký tự ‘x’ viết thường sẽ được sử dụng trong phần tách và trang tính của bạn sẽ trông giống như sau:
Lưu ý rằng giá trị tại ô A6 bị cắt bớt vì nó chứa ký tự ‘x’ viết thường. Bởi vì sự phân tách không phân biệt chữ hoa chữ thường, bất kỳ dấu phân cách nào tạo thành một phần của chuỗi con sẽ gây ra sự phân tách.
Đây là một điểm quan trọng cần ghi nhớ khi sử dụng dấu phân tách văn bản và vbTextCompare. Bạn có thể dễ dàng nhận được kết quả sai.
Sử dụng các ký tự không in được làm ký tự phân cách
Bạn có thể sử dụng các ký tự không in được làm dấu phân cách, chẳng hạn như ký tự xuống dòng (ngắt dòng).
Ở đây chúng tôi sử dụng vbCr để chỉ định một dấu xuống dòng <>
| 123456789101112131415 | Sub SplitByNonPrintableExample ()'Tạo các biếnDim MyArray () Dưới dạng chuỗi, MyString dưới dạng chuỗi, Tôi là biến thể, N là số nguyên'Chuỗi mẫu có dấu phân cách xuống dòngMyString = "Một" & vbCr & "Hai" & vbCr & "Ba" & vbCr & "Bốn" & vbCr & "Năm" & vbCr & "Sáu"'Sử dụng hàm Split để phân chia các phần thành phần của chuỗiMyArray = Tách (MyString, vbCr,, vbTextCompare)'Xóa trang tínhActiveSheet.UsedRange.Clear'Lặp lại qua mảngĐối với N = 0 Đến UBound (MyArray)'Đặt từng phần tách vào cột đầu tiên của trang tínhPhạm vi ("A" & N + 1) .Value = MyArray (N)N tiếp theoKết thúc Sub |
Trong ví dụ này, một chuỗi được tạo bằng cách sử dụng vbCr (ký tự xuống dòng) làm dấu phân cách.
Khi mã này được chạy, trang tính của bạn sẽ giống như sau:
Sử dụng chức năng kết hợp để đảo ngược sự phân tách
Hàm Join sẽ nối lại tất cả các phần tử của một mảng, nhưng sử dụng một dấu phân cách được chỉ định. Nếu không có ký tự phân tách nào được chỉ định thì một khoảng trắng sẽ được sử dụng.
| 123456789101112131415 | Sub JoinExample ()'Tạo các biếnDim MyArray () Dưới dạng chuỗi, MyString dưới dạng chuỗi, Tôi là biến thể, N là số nguyênLàm mờ mục tiêu dưới dạng chuỗi'Chuỗi mẫu có dấu phân cách bằng dấu phẩyMyString = "Một, Hai, Ba, Bốn, Năm, Sáu"'Đặt MyString tại ô A1Phạm vi ("A1"). Giá trị = MyString'Sử dụng hàm Split để phân chia các phần thành phần của chuỗiMyArray = Tách (MyString, ",")'Sử dụng chức năng Tham gia để tạo lại chuỗi ban đầu bằng cách sử dụng dấu phân cách dấu chấm phẩyTarget = Tham gia (MyArray, ”;”)'Đặt chuỗi kết quả tại ô A2Phạm vi ("A2"). Giá trị = Mục tiêuKết thúc Sub |
Mã này chia một chuỗi có dấu phân cách bằng dấu phẩy thành một mảng và nối nó lại với nhau bằng cách sử dụng dấu phân cách dấu chấm phẩy.
Sau khi chạy mã này, trang tính của bạn sẽ giống như sau:
Ô A1 có chuỗi ban đầu có dấu phân cách bằng dấu phẩy và ô A2 có chuỗi mới được nối với dấu phân cách dấu chấm phẩy.
Sử dụng chức năng tách để thực hiện đếm từ
Lưu ý rằng một biến chuỗi trong Excel VBA có thể dài tới 2Gb, bạn có thể sử dụng hàm tách để đếm từ trong một đoạn văn bản. Rõ ràng, Microsoft Word thực hiện điều đó một cách tự động, nhưng điều này có thể hữu ích cho một tệp văn bản đơn giản hoặc văn bản được sao chép từ một ứng dụng khác.
| 1234567891011121314 | Số phụOfWordsExample ()'Tạo các biếnDim MyArray () As String, MyString As String'Chuỗi mẫu có dấu cáchMyString = "Một Hai Ba Bốn Năm Sáu"'Xóa bất kỳ dấu cách kép nàoMyString = Thay thế (MyString, "", "")'Xóa bất kỳ khoảng trắng đầu hoặc cuốiMyString = Trim (MyString)'Sử dụng hàm Split để phân chia các phần thành phần của chuỗiMyArray = Tách (MyString)'Hiển thị số lượng từ bằng cách sử dụng chức năng UBoundMsgBox "Số lượng từ" & UBound (MyArray) + 1Kết thúc Sub |
Một trong những mối nguy hiểm của mã đếm từ này là nó sẽ bị ném bởi dấu cách kép và dấu cách đầu và cuối. Nếu có những từ này, chúng sẽ được tính là từ thừa và số từ sẽ kết thúc là không chính xác.
Mã sử dụng các chức năng Replace và Trim để loại bỏ những khoảng trắng thừa này.
Dòng mã cuối cùng hiển thị số lượng từ được tìm thấy bằng cách sử dụng hàm UBound để lấy số phần tử tối đa của mảng và sau đó tăng nó lên 1. Điều này là do phần tử mảng đầu tiên bắt đầu bằng 0.
Tách một địa chỉ thành các ô trong trang tính
Địa chỉ thư thường là những chuỗi văn bản dài với dấu phân cách bằng dấu phẩy. Bạn cũng có thể muốn tách từng phần của địa chỉ thành một ô riêng biệt.
| 123456789101112131415 | Địa chỉ phụExample ()'Tạo các biếnDim MyArray () Dưới dạng chuỗi, MyString dưới dạng chuỗi, N là số nguyên'Thiết lập chuỗi với Địa chỉ Tập đoàn MicrosoftMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Sử dụng hàm tách để chia chuỗi bằng dấu phân cách bằng dấu phẩyMyArray = Tách (MyString, ",")'Xóa trang tínhActiveSheet.UsedRange.Clear'lặp qua mảngĐối với N = 0 Đến UBound (MyArray)'Đặt từng phần tách vào cột đầu tiên của trang tínhPhạm vi ("A" & N + 1) .Value = MyArray (N)N tiếp theoKết thúc Sub |
Chạy mã này sẽ sử dụng dấu phân cách dấu phẩy để đặt mỗi dòng địa chỉ vào một ô riêng biệt:
Nếu bạn chỉ muốn trả lại mã zip (phần tử mảng cuối cùng), thì bạn có thể sử dụng mã:
| 123456789101112 | Địa chỉ phụZipCodeExample ()'Tạo các biếnDim MyArray () As String, MyString As String, N As Integer, Temp As String'Thiết lập chuỗi với Địa chỉ Tập đoàn MicrosoftMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Sử dụng hàm tách để chia chuỗi bằng dấu phân cách bằng dấu phẩyMyArray = Tách (MyString, ",")'Xóa trang tínhActiveSheet.UsedRange.Clear'Đặt Mã Zip tại ô A1Phạm vi ("A1"). Giá trị = MyArray (UBound (MyArray))Kết thúc Sub |
Điều này sẽ chỉ sử dụng phần tử cuối cùng trong mảng, phần tử này được tìm thấy bằng cách sử dụng hàm UBound.
Mặt khác, bạn có thể muốn xem tất cả các dòng trong một ô để chúng có thể được in lên nhãn địa chỉ:
| 1234567891011121314151617 | Địa chỉ phụExample ()'Tạo các biếnDim MyArray () As String, MyString As String, N As Integer, Temp As String'Thiết lập chuỗi với Địa chỉ Tập đoàn MicrosoftMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Sử dụng hàm tách để chia chuỗi bằng dấu phân cách bằng dấu phẩyMyArray = Tách (MyString, ",")'Xóa trang tínhActiveSheet.UsedRange.Clear'lặp qua mảngĐối với N = 0 Đến UBound (MyArray)'đặt mỗi phần tử mảng cộng với một ký tự nguồn cấp dòng vào một chuỗiTemp = Temp & MyArray (N) & vbLfN tiếp theo'Đặt chuỗi vào trang tínhPhạm vi ("A1") = Nhiệt độKết thúc Sub |
Ví dụ này hoạt động giống như ví dụ trước đó, ngoại trừ việc nó tạo một chuỗi tạm thời của tất cả các phần tử mảng, nhưng chèn một ký tự nguồn cấp dữ liệu dòng sau mỗi phần tử.
Trang tính sẽ trông như thế này sau khi mã đã được chạy:
Chia chuỗi thành các ô trong trang tính
Bạn có thể sao chép mảng Chia thành các ô trang tính <> chỉ bằng một lệnh:
| 12345678910 | Sub CopyToRange ()'Tạo các biếnDim MyArray () As String, MyString As String'Chuỗi mẫu có dấu cáchMyString = "Một, Hai, Ba, Bốn, Năm, Sáu"'Sử dụng hàm Split để phân chia các phần thành phần của chuỗiMyArray = Tách (MyString, ",")'Sao chép mảng vào trang tínhPhạm vi ("A1: A" & UBound (MyArray) + 1) .Value = WorksheetFunction.Transpose (MyArray)Kết thúc Sub |
Khi mã này đã được chạy, trang tính của bạn sẽ giống như sau:
Tạo một hàm mới để cho phép tách từ một điểm đã cho
Tham số Giới hạn trong chức năng Tách chỉ cho phép bạn chỉ định giới hạn trên mà bạn muốn dừng quá trình tách. Nó luôn bắt đầu từ đầu chuỗi.
Sẽ rất hữu ích nếu có một hàm tương tự trong đó bạn có thể chỉ định điểm bắt đầu của phần tách trong chuỗi và số lượng phần tách mà bạn muốn xem từ điểm đó trở đi. Nó cũng sẽ chỉ trích xuất các phần mà bạn đã chỉ định vào mảng, thay vì có một giá trị chuỗi khổng lồ làm phần tử cuối cùng trong mảng.
Bạn có thể dễ dàng tự xây dựng một hàm (được gọi là SplitSlicer) trong VBA để thực hiện việc này:
| 123456789101112131415161718192021222324 | Hàm SplitSlicer (Nhắm mục tiêu dưới dạng chuỗi, Del dưới dạng chuỗi, Bắt đầu dưới dạng số nguyên, N là số nguyên)'Tạo biến mảngDim MyArray () Dưới dạng chuỗi'Chụp sự phân tách bằng cách sử dụng biến bắt đầu bằng cách sử dụng ký tự phân táchMyArray = Tách (Mục tiêu, Del, Bắt đầu)‘Kiểm tra xem thông số bắt đầu có lớn hơn số lần phân tách hay không - điều này có thể gây ra sự cốNếu Bắt đầu> UBound (MyArray) + 1 Sau đó‘Hiển thị lỗi và thoát khỏi chức năngMsgBox "Tham số bắt đầu lớn hơn số lượng phân chia có sẵn"SplitSlicer = MyArrayChức năng thoátKết thúc nếu'Đặt phần tử mảng cuối cùng vào chuỗiTarget = MyArray (UBound (MyArray))'Tách chuỗi bằng cách sử dụng N làm giới hạnMyArray = Tách (Target, Del, N)‘Kiểm tra để đảm bảo rằng giới hạn trên cùng lớn hơn 0 khi mã xóa phần tử cuối cùngNếu UBound (MyArray)> 0 Thì'Sử dụng ReDim để xóa phần tử cuối cùng của mảngReDim Bảo tồn MyArray (UBound (MyArray) - 1)Kết thúc nếu'Trả lại mảng mớiSplitSlicer = MyArrayChức năng kết thúc |
Hàm này được xây dựng với bốn tham số:
- Mục tiêu - string - đây là chuỗi đầu vào mà bạn muốn tách
- Del - chuỗi hoặc ký tự không in được - đây là ký tự phân tách mà bạn sử dụng, ví dụ: dấu phẩy, dấu hai chấm
- Bắt đầu - số - đây là phần chia bắt đầu cho lát của bạn
- n - số - đây là số lượng chia nhỏ mà bạn muốn thực hiện trong lát của mình
Không có tham số nào trong số này là tùy chọn hoặc có giá trị mặc định, nhưng bạn có thể đưa thông số đó vào mã cho hàm nếu bạn muốn mở rộng nó hơn nữa.
Hàm sử dụng hàm Split để tạo mảng sử dụng tham số Bắt đầu làm Giới hạn. Điều này có nghĩa là các phần tử mảng sẽ giữ phần tách cho đến tham số bắt đầu, nhưng phần còn lại của chuỗi sẽ là phần tử cuối cùng và sẽ không được chia.
Phần tử cuối cùng trong mảng được chuyển trở lại một chuỗi bằng cách sử dụng hàm UBound để xác định đây là phần tử nào.
Sau đó, chuỗi lại được chia thành mảng, sử dụng N làm biến giới hạn. Điều này có nghĩa là việc phân tách sẽ được thực hiện cho chuỗi lên đến vị trí N, sau đó phần còn lại của chuỗi sẽ tạo thành phần tử cuối cùng trong mảng.
Câu lệnh ReDim được sử dụng để loại bỏ phần tử cuối cùng vì chúng ta chỉ muốn các phần tử cụ thể còn lại trong mảng. Lưu ý rằng tham số Preserve được sử dụng, nếu không tất cả dữ liệu trong mảng sẽ bị mất.
Mảng mới sau đó được trả về mã mà nó được gọi từ đó.
Lưu ý rằng mã được "kiểm tra lỗi". Người dùng thường sẽ làm những điều kỳ lạ mà bạn không tính đến. Ví dụ: nếu họ thử sử dụng hàm với tham số Bắt đầu hoặc N lớn hơn số lượng phân tách có sẵn trong chuỗi, điều này có thể khiến hàm bị lỗi.
Mã được bao gồm để kiểm tra giá trị Bắt đầu và cũng để đảm bảo rằng có một phần tử có thể bị loại bỏ khi câu lệnh ReDim được sử dụng trên mảng.
Đây là mã để kiểm tra chức năng:
| 123456789101112 | Sub TestSplitSlicer ()'Tạo các biếnDim MyArray () As String, MyString As String'Xác định chuỗi mẫu bằng dấu phân cách bằng dấu phẩyMyString = "Một, Hai, Ba, Bốn, Năm, Sáu, Bảy, Tám, Chín, Mười"'Sử dụng hàm Splitslicer để xác định mảng mớiMyArray = SplitSlicer (MyString, ",", 4, 3)'Xóa trang tính đang hoạt độngActiveSheet.UsedRange.Clear'Sao chép mảng vào trang tínhPhạm vi ("A1: A" & UBound (MyArray) + 1) .Value = WorksheetFunction.Transpose (MyArray)Kết thúc Sub |
Chạy mã này và trang tính của bạn sẽ giống như sau:
