Hướng dẫn này sẽ chỉ cho bạn cách chuyển đổi chuỗi văn bản trong một ô thành nhiều cột bằng cách sử dụng phương pháp Range TextToColumns trong VBA
Văn bản thành cột
Các Phạm vi.TextToColumns trong VBA là một công cụ mạnh mẽ để dọn dẹp dữ liệu đã được nhập từ các tệp văn bản hoặc tệp csv chẳng hạn.
Hãy xem xét bảng tính sau.
Dữ liệu được đưa vào Excel tất cả trong một cột và được phân tách bằng dấu ngoặc kép.
Bạn có thể sử dụng phương pháp Range TextToColumns để tách dữ liệu này thành các cột.
Cú pháp TextToColumns
biểu hiện.TextToColumns (Điểm đến, Loại dữ liệu, TextQualifier, Dấu liên tiếp, Chuyển hướng, Dấu chấm phẩy, Dấu phẩy, Không gian, Khác, OtherChar, FieldInfo, Phân số thập phân, Hàng nghìn dấu phân tách, TrailingMinusNumbers)
Biểu hiện
Đây là Phạm vi ô bạn muốn tách - ví dụ: Phạm vi (“A1: A23”).
Tất cả các đối số trong phương thức TextToColumns là tùy chọn (chúng có dấu ngoặc vuông xung quanh).
Điểm đến
Nơi bạn muốn kết quả được đặt - thường bạn ghi đè dữ liệu và chia nó ở cùng một vị trí.
Loại dữ liệu
Loại phân tích cú pháp văn bản bạn đang sử dụng - nó có thể là xlDelimited (mặc định nếu bị bỏ qua) hoặc xlFixedWidth.
TextQualifier
Nếu bạn có dấu ngoặc kép (đơn hoặc kép) xung quanh mỗi trường trong văn bản mà bạn đang tách, bạn cần cho biết chúng là đơn hay kép.
ConsequtiveDelimiter
Điều này đúng hoặc sai và yêu cầu VBA xem xét 2 trong số các dấu phân cách giống nhau như thể nó là 1 dấu phân cách.
Chuyển hướng
Đây là một trong hai Thật của Sai, mặc định là Sai - điều này cho VBA biết rằng dữ liệu được phân định bằng Tab.
Dấu chấm phẩy
Đây là một trong haiThật của Sai, mặc định là Sai - điều này cho VBA biết rằng dữ liệu được phân cách bằng dấu chấm phẩy.
Không gian
Đây là một trong hai Thật của Sai, mặc định là Sai - điều này cho VBA biết rằng dữ liệu được phân cách bằng dấu cách.
Khác
Đây là một trong hai Thật của Sai, mặc định là Sai. Nếu bạn đặt giá trị này thành Đúng, thì đối số tiếp theo, OtherChar cần phải được chỉ rõ.
OtherChar
Đây là ký tự mà văn bản được phân tách (ví dụ: hoặc |).
FieldInfo
Đây là một mảng chứa thông tin về loại dữ liệu đang được phân tách. Giá trị đầu tiên trong mảng cho biết số cột trong dữ liệu và giá trị thứ hai cho biết hằng số mà bạn sẽ sử dụng để mô tả kiểu dữ liệu bạn yêu cầu.
Ví dụ về 5 cột có kiểu dữ liệu là văn bản, số và ngày tháng có thể là:
Mảng (Mảng (1, xlTextFormat), Mảng (2, xlTextFormat), Mảng (3, xlGeneralFormat), Mảng (4, xlGeneralFormat), Mảng (5, xlMDYFormat))
Một cách khác để giải quyết vấn đề này là:
Mảng (Mảng (1, 2), Mảng (2, 2), Mảng (3, 1), Mảng (4, 1), Mảng (5, 3))
Các số trong cột thứ hai là giá trị của các hằng số trong đó hằng số xlTextFormat có giá trị là 2, xlGeneralFormat (mặc định) có giá trị là 1 và xlMDYFormat có giá trị là 3.
Phân số thập phân
Bạn có thể chỉ định dấu phân tách thập phân mà VBA phải sử dụng nếu có số trong dữ liệu. Nếu bị bỏ qua, nó sẽ sử dụng cài đặt hệ thống, thường là một khoảng thời gian.
Hàng nghìn dấu phân tách
Bạn có thể chỉ định dấu phân tách hàng nghìn mà VBA phải sử dụng nếu có số trong dữ liệu. Nếu bị bỏ qua, nó sẽ sử dụng cài đặt hệ thống, thường là dấu phẩy.
TrailingMinusNumbers
Đối số này chủ yếu dành cho tính tương thích đối với dữ liệu được tạo từ các hệ thống cũ hơn trong đó dấu trừ thường đứng sau số chứ không phải trước đó. Bạn nên đặt giá trị này thành True nếu các số âm có dấu trừ đằng sau chúng. Mặc định này sai.
Chuyển đổi văn bản thành cột
Quy trình sau đây sẽ chuyển đổi dữ liệu Excel ở trên thành các cột.
| 12345678910111213141516 | Sub TextToCol1 ()Phạm vi ("A1: A25"). TextToColumns _Đích: = Phạm vi ("A1: A25"),DataType: = xlDelimited, _TextQualifier: = xlDoubleQuote, _Dấu liên tiếp: = Đúng, _Tab: = Sai, _Dấu chấm phẩy: = Sai, _Dấu phẩy: = Sai,Dấu cách: = Đúng, _Khác: = Sai, _FieldInfo: = Array (Array (1, 1), Array (2, 1), Array (3, 1), Array (4, 1), Array (5, 1)), _DecimalSeparator: = "." , _Dấu phân tách hàng nghìn: = ",", _TrailingMinusNumbers: = TrueKết thúc Sub |
Trong quy trình trên, chúng tôi đã điền vào tất cả các tham số. Tuy nhiên, nhiều tham số được đặt thành false hoặc thành cài đặt mặc định và không cần thiết. Dưới đây là một phiên bản rõ ràng hơn của quy trình trên. Bạn cần sử dụng tên tham số để cho biết chúng tôi đang sử dụng tham số nào.
| 1234567 | Sub TextToCol2 ()Phạm vi ("A1: A25"). TextToColumns _DataType: = xlDelimited, _TextQualifier: = xlDoubleQuote, _Dấu liên tiếp: = Đúng, _Dấu cách: = Đúng,Kết thúc Sub |
Chỉ có 4 tham số thực sự được yêu cầu - dữ liệu được phân tách bằng dấu ngoặc kép, bạn muốn các dấu ngoặc kép liên tiếp được coi là một và dữ liệu được phân tách bằng dấu cách!
Đối với một dòng mã nhanh hơn nữa, chúng ta có thể bỏ qua tên tham số, nhưng sau đó chúng ta sẽ cần phải đặt dấu phẩy để lưu vị trí của tham số. Bạn chỉ cần đặt thông tin đến tận tham số cuối cùng mà bạn đang sử dụng - trong trường hợp này là Dấu cách phân tách dữ liệu là tham số thứ 8.
| 123 | Sub TextToCol3 ()Phạm vi ("A1: A25"). TextToColumns, xlDelimited, xlDoubleQuote, True,,,, TrueKết thúc Sub |
Khi bạn chạy bất kỳ quy trình nào ở trên, dữ liệu sẽ được phân tách theo hình bên dưới.
