Khi dữ liệu được nhập vào Excel, dữ liệu có thể ở nhiều định dạng tùy thuộc vào ứng dụng nguồn đã cung cấp dữ liệu đó
Ví dụ: nó có thể chứa tên và địa chỉ của khách hàng hoặc nhân viên, nhưng tất cả điều này kết thúc dưới dạng một chuỗi văn bản liên tục trong một cột của trang tính, thay vì được tách thành các cột riêng lẻ. g. tên, đường phố, thành phố
Bạn có thể chia dữ liệu bằng cách sử dụng ký tự phân cách chung. Ký tự phân cách thường là dấu phẩy, dấu tab, dấu cách hoặc dấu chấm phẩy. Ký tự này phân tách từng đoạn dữ liệu trong chuỗi văn bản
Một lợi thế lớn của việc sử dụng ký tự phân cách là nó không dựa vào độ rộng cố định trong văn bản. Dấu phân cách cho biết chính xác vị trí chia văn bản
Bạn có thể cần chia nhỏ dữ liệu vì bạn có thể muốn sắp xếp dữ liệu bằng cách sử dụng một phần nhất định của địa chỉ hoặc để có thể lọc trên một thành phần cụ thể. Nếu dữ liệu được sử dụng trong bảng tổng hợp, bạn có thể cần phải có tên và địa chỉ dưới dạng các trường khác nhau trong đó
Bài viết này chỉ cho bạn tám cách để chia văn bản thành các phần cấu thành theo yêu cầu bằng cách sử dụng ký tự phân cách để chỉ ra các điểm phân chia
Dữ liệu mẫu
Dữ liệu mẫu ở trên sẽ được sử dụng trong tất cả các ví dụ sau. Tải xuống tệp ví dụ để lấy dữ liệu mẫu cộng với các giải pháp khác nhau để trích xuất dữ liệu dựa trên dấu phân cách
Hàm Excel để tách văn bản
Có một số hàm Excel có thể được sử dụng để tách và thao tác văn bản trong một ô
Chức năng TRÁI
Hàm LEFT trả về số ký tự từ bên trái của văn bản
cú pháp
= LEFT [ Text, [Number] ]- Văn bản – Đây là chuỗi văn bản mà bạn muốn trích xuất từ. Nó cũng có thể là một tham chiếu ô hợp lệ trong sổ làm việc
- Số [Tùy chọn] – Đây là số ký tự mà bạn muốn trích xuất từ chuỗi văn bản. Giá trị phải lớn hơn hoặc bằng 0. Nếu giá trị lớn hơn độ dài của chuỗi văn bản, thì tất cả các ký tự sẽ được trả về. Nếu giá trị bị bỏ qua, thì giá trị được giả định là một
QUYỀN Chức năng
Hàm RIGHT trả về số ký tự từ bên phải của văn bản
cú pháp
= RIGHT [ Text, [Number] ]Các tham số hoạt động theo cách tương tự như đối với hàm LEFT được mô tả ở trên
Hàm TÌM
Hàm FIND trả về vị trí của văn bản đã chỉ định trong một chuỗi văn bản. Điều này có thể được sử dụng để định vị ký tự phân cách. Lưu ý rằng tìm kiếm phân biệt chữ hoa chữ thường
cú pháp
= FIND [SubText, Text, [Start]]- SubText – Đây là một chuỗi văn bản mà bạn muốn tìm kiếm
- Văn bản - Đây là chuỗi văn bản sẽ được tìm kiếm
- Bắt đầu [Tùy chọn] – Vị trí bắt đầu tìm kiếm
LEN Hàm
Hàm LEN sẽ cho độ dài theo số ký tự của chuỗi văn bản
cú pháp
= LEN [ Text ]- Văn bản – Đây là chuỗi văn bản mà bạn muốn xác định số lượng ký tự
Trích xuất dữ liệu bằng các hàm LEFT, RIGHT, FIND và LEN
Sử dụng hàng đầu tiên [B3] của dữ liệu mẫu, các hàm này có thể được kết hợp để chia chuỗi văn bản thành các phần bằng cách sử dụng ký tự phân cách
= FIND [ ",", B3 ]Bạn dùng hàm FIND để lấy vị trí ký tự phân cách đầu tiên. Điều này sẽ trả về giá trị 18
= LEFT [ B3, FIND[ ",", B3 ] - 1 ]Sau đó, bạn có thể sử dụng hàm LEFT để trích xuất thành phần đầu tiên của chuỗi văn bản
Lưu ý rằng TÌM lấy vị trí của dấu phân cách đầu tiên, nhưng bạn cần trừ 1 khỏi nó để không bao gồm ký tự phân cách
Điều này sẽ trả lại Tabbie O'Hallagan
= RIGHT [ B3, LEN [ B3 ] - FIND [ ",", B3 ] ]Nó phức tạp hơn để lấy các thành phần tiếp theo của chuỗi văn bản. Bạn cần xóa thành phần đầu tiên khỏi văn bản bằng cách sử dụng công thức trên
Công thức này lấy độ dài của văn bản gốc, tìm vị trí dấu phân cách đầu tiên, sau đó tính toán số ký tự còn lại trong chuỗi văn bản sau dấu phân cách đó
Sau đó, hàm RIGHT cắt bớt tất cả các ký tự cho đến và bao gồm cả dấu phân cách đầu tiên đó để chuỗi văn bản ngày càng ngắn hơn khi mỗi ký tự phân cách được tìm thấy
Điều này sẽ trả về 056 Dennis Park, Greda, Croatia, 44273
Bây giờ, bạn có thể sử dụng hàm TÌM để định vị dấu phân cách tiếp theo và hàm TRÁI để trích xuất thành phần tiếp theo, sử dụng phương pháp tương tự như trên
Lặp lại cho tất cả các dấu phân cách và thao tác này sẽ chia chuỗi văn bản thành các phần thành phần
Hàm FILTERXML dưới dạng Mảng động
Nếu bạn đang sử dụng Excel cho Microsoft 365 thì bạn có thể sử dụng hàm FILTERXML để tách văn bản với đầu ra dưới dạng một mảng động
Bạn có thể tách một chuỗi văn bản bằng cách biến nó thành một chuỗi XML bằng cách thay đổi các ký tự phân cách thành các thẻ XML. Bằng cách này, bạn có thể sử dụng hàm FILTERXML để trích xuất dữ liệu
Các thẻ XML do người dùng định nghĩa, nhưng trong ví dụ này, s sẽ đại diện cho một nút phụ và t sẽ đại diện cho nút chính
= "" & SUBSTITUTE [ B2, ",", "" ] & ""Sử dụng công thức trên để chèn các thẻ XML vào chuỗi văn bản của bạn
________số 8Điều này sẽ trả về công thức trên trong ví dụ
Lưu ý rằng mỗi nút được xác định được theo sau bởi nút đóng có dấu gạch chéo ngược. Các thẻ XML này xác định phần đầu và phần cuối của từng phần của văn bản và hoạt động hiệu quả theo cách tương tự như các dấu phân cách
=TRANSPOSE[
FILTERXML[
"" &
SUBSTITUTE[
B3,
",",
""
] & "",
"//s"
]
]Công thức trên sẽ chèn các thẻ XML vào chuỗi gốc và sau đó sử dụng các thẻ này để tách các mục thành một mảng
Như đã thấy ở trên, mảng sẽ tràn từng mục vào một ô riêng biệt. Sử dụng chức năng TRANSPOSE làm cho mảng tràn theo chiều ngang thay vì theo chiều dọc
Chức năng FILTERXML để tách văn bản
Nếu phiên bản Excel của bạn không có mảng động thì bạn vẫn có thể sử dụng hàm FILTERXML để trích xuất các mục riêng lẻ
= RIGHT [ Text, [Number] ]Giờ đây, bạn có thể chia chuỗi thành các phần bằng công thức FILTERXML ở trên
Điều này sẽ trả về phần đầu tiên Tabbie O'Hallagan
= RIGHT [ Text, [Number] ]Để quay lại phần tiếp theo, hãy sử dụng công thức trên
Điều này sẽ trả về phần thứ hai của chuỗi văn bản 056 Dennis Park
Bạn có thể sử dụng cùng một mẫu này để trả về bất kỳ phần nào của văn bản mẫu, chỉ cần thay đổi [2] được tìm thấy trong công thức cho phù hợp
Flash Fill để tách văn bản
Flash Fill cho phép bạn đưa vào một ví dụ về cách bạn muốn phân chia dữ liệu của mình
Bạn có thể xem hướng dẫn này về cách sử dụng flash fill để xóa dữ liệu của bạn để biết thêm chi tiết
Sau đó, bạn chọn ô đầu tiên mà bạn muốn chia dữ liệu và nhấp vào Flash Fill. Excel sẽ điền các hàng còn lại từ ví dụ của bạn
Sử dụng dữ liệu mẫu, nhập Tên vào ô C2, sau đó nhập Tabbie O'Hallagan vào ô C3
Flash fill sẽ tự động điền vào các tên dữ liệu còn lại từ dữ liệu mẫu. Nếu không, bạn có thể chọn ô C4 và nhấp vào biểu tượng Flash Fill trong nhóm Công cụ dữ liệu của tab Dữ liệu trên dải băng Excel
Tương tự, bạn có thể thêm Đường vào ô D2, Thành phố vào ô E2, Quốc gia vào ô F2 và Mã Bưu điện vào ô G2
Chọn riêng các ô tiếp theo [D2 đến G2] và nhấp vào biểu tượng Flash Fill. Phần còn lại của các thành phần văn bản sẽ được điền vào các cột này
Chuyển văn bản thành cột Lệnh tách văn bản
Chức năng Excel này có thể được sử dụng để chia văn bản trong một ô thành các phần dựa trên ký tự phân cách
- Chọn toàn bộ phạm vi dữ liệu mẫu [B2. B12]
- Nhấp vào tab Dữ liệu trong dải băng Excel
- Bấm vào biểu tượng Văn bản thành Cột trong nhóm Công cụ Dữ liệu của dải băng Excel và trình hướng dẫn sẽ xuất hiện để giúp bạn thiết lập cách chia văn bản
- Chọn Phân cách trên các nút tùy chọn
- Nhấn nút Tiếp theo
- Chọn Dấu phẩy làm dấu phân cách và bỏ chọn bất kỳ dấu phân cách nào khác
- Nhấn nút Tiếp theo
- Cửa sổ Xem trước dữ liệu sẽ hiển thị cách dữ liệu của bạn sẽ được phân chia. Chọn một vị trí để đặt đầu ra
- Bấm vào nút Kết thúc
Dữ liệu của bạn bây giờ sẽ được hiển thị trong các cột trên trang tính của bạn
Chuyển đổi dữ liệu thành tệp CSV
Điều này sẽ chỉ hoạt động với dấu phẩy làm dấu phân cách, vì tệp CSV [giá trị được phân tách bằng dấu phẩy] phụ thuộc vào dấu phẩy để phân tách các giá trị
Mở Notepad và sao chép và dán dữ liệu mẫu vào đó. Bạn có thể mở Notepad bằng cách nhập Notepad vào hộp tìm kiếm ở bên trái thanh tác vụ Windows hoặc tìm nó trong danh sách ứng dụng
Khi bạn đã sao chép dữ liệu vào Notepad, hãy lưu dữ liệu đó bằng cách sử dụng Tệp ➜ Lưu dưới dạng từ menu. Nhập tên tệp bằng một. hậu tố csv e. g. Chia dữ liệu. csv
Sau đó, bạn có thể mở tệp này trong Excel. Chọn tệp csv trong loại tệp trình duyệt thả xuống và nhấp vào OK. Dữ liệu của bạn sẽ tự động xuất hiện với từng thành phần trong các cột riêng biệt
VBA để tách văn bản
VBA là ngôn ngữ lập trình nằm sau Excel và cho phép bạn viết mã của riêng mình để thao tác dữ liệu hoặc thậm chí tạo các hàm của riêng bạn
Để truy cập Visual Basic Editor [VBE], bạn sử dụng tổ hợp phím Alt + F11
= RIGHT [ Text, [Number] ]Nhấp vào Chèn trong thanh menu và nhấp vào Mô-đun. Một cửa sổ mới sẽ xuất hiện cho mô-đun. Dán vào đoạn mã trên
Đoạn mã này tạo ra một mảng một chiều gọi là MyArray. Sau đó, nó lặp qua dữ liệu mẫu [hàng 2 đến 12] và sử dụng hàm VBA Split để điền vào MyArray
Hàm tách sử dụng dấu phân cách bằng dấu phẩy để mỗi phần của văn bản trở thành một phần tử của mảng
Biến đếm được đặt thành 3 đại diện cho cột C, cột này sẽ là cột đầu tiên hiển thị dữ liệu phân tách
Sau đó, mã lặp qua từng phần tử trong mảng và điền vào từng ô bằng phần tử. Tham chiếu ô dựa trên n cho hàng và Đếm cho cột
Biến Count được tăng dần trong mỗi vòng lặp để dữ liệu lấp đầy hàng, rồi xuống dưới
Power Query để tách văn bản
Power Query trong Excel cho phép thao tác một cột thành các phần bằng cách sử dụng ký tự phân cách
Bài viết liên quan
Điều đầu tiên cần làm là xác định nguồn dữ liệu của bạn, đó là dữ liệu mẫu mà bạn đã nhập vào trang tính Excel của mình
Bấm vào tab Dữ liệu trong dải băng Excel, sau đó bấm vào Lấy dữ liệu trong nhóm Nhận & Chuyển đổi Dữ liệu của dải băng
Bấm vào Từ tệp trong trình đơn thả xuống đầu tiên, sau đó bấm vào Từ sổ làm việc trong trình đơn thả xuống thứ hai
Điều này sẽ hiển thị một trình duyệt tập tin. Xác định vị trí tệp dữ liệu mẫu của bạn [tệp bạn đã mở] và nhấp vào OK
Một cửa sổ bật lên điều hướng sẽ hiển thị hiển thị tất cả các trang tính trong sổ làm việc của bạn. Bấm vào trang tính có dữ liệu mẫu và thao tác này sẽ hiển thị bản xem trước của dữ liệu
Mở rộng cây dữ liệu trong ngăn bên trái để hiển thị bản xem trước của dữ liệu hiện có
Nhấp vào Chuyển đổi dữ liệu và thao tác này sẽ hiển thị Power Query Editor
Đảm bảo rằng cột đơn có dữ liệu trong đó được tô sáng. Nhấp vào biểu tượng Cột chia trong nhóm Chuyển đổi của dải băng. Nhấp vào By Delimiter trong trình đơn thả xuống xuất hiện
Điều này sẽ hiển thị một cửa sổ bật lên cho phép bạn chọn dấu phân cách của mình. Mặc định là dấu phẩy
Nhấn OK và dữ liệu sẽ được chuyển thành các cột riêng biệt
Bấm vào Đóng và Tải trong nhóm Đóng của dải băng và một trang tính mới sẽ được thêm vào sổ làm việc của bạn với một bảng dữ liệu ở định dạng mới
Power Pivot cột được tính để tách văn bản
Bạn có thể sử dụng Power Pivot để tách văn bản bằng cách sử dụng các cột được tính toán
Bấm vào tab Power Pivot trong dải băng Excel rồi bấm vào biểu tượng Thêm vào Mô hình Dữ liệu trong nhóm Bảng
Dữ liệu của bạn sẽ được tự động phát hiện và cửa sổ bật lên sẽ hiển thị vị trí. Nếu đây không phải là vị trí chính xác, thì nó có thể được đặt lại tại đây
Bỏ chọn hộp kiểm Bảng của tôi có tiêu đề trong cửa sổ bật lên, vì chúng tôi cũng muốn chia nhỏ tiêu đề
Nhấp vào OK và một màn hình xem trước sẽ được hiển thị
Nhấp chuột phải vào tiêu đề cho cột dữ liệu của bạn [Cột1] và nhấp vào Chèn cột trong menu bật lên. Thao tác này sẽ chèn một cột được tính toán để có thể nhập công thức
= RIGHT [ Text, [Number] ]Trong thanh công thức, chèn công thức trên
Điều này hoạt động theo cách tương tự như các chức năng được mô tả trong phương pháp 1 của bài viết này
Công thức này sẽ cung cấp thành phần Tên trong chuỗi văn bản
Chèn một cột được tính toán khác bằng phương pháp tương tự như cột được tính toán đầu tiên
= RIGHT [ Text, [Number] ]Chèn công thức trên vào thanh công thức
Đây là một công thức phức tạp và bạn có thể muốn chia nó thành các phần bằng cách sử dụng một số cột được tính toán
Điều này sẽ cung cấp thành phần Street trong chuỗi văn bản
Bạn có thể tiếp tục sửa đổi công thức để tạo các cột được tính toán cho tất cả các thành phần khác của chuỗi văn bản
Vấn đề với bảng tổng hợp là nó cần giá trị số cũng như giá trị văn bản. Vì dữ liệu mẫu chỉ là văn bản nên cần thêm một giá trị số
Nhấp vào ô đầu tiên trong cột Thêm cột và nhập công thức =1 vào thanh công thức
Điều này sẽ thêm giá trị của 1 xuống tận cột đó. Bấm vào biểu tượng Pivot Table trong tab Trang chủ của dải băng
Nhấp vào Bảng tổng hợp trong menu bật lên. Chỉ định vị trí của bảng tổng hợp của bạn trong cửa sổ bật lên đầu tiên và nhấp vào OK. Nếu ngăn Trường bảng Pivot không tự động hiển thị, nhấp chuột phải vào khung bảng tổng hợp và chọn Hiển thị danh sách trường.
Nhấp vào Cột được tính toán trong Danh sách trường và đặt chúng vào cửa sổ Hàng.
bảng tổng hợp của chúng tôi bây giờ sẽ hiển thị các thành phần riêng lẻ của chuỗi văn bản
kết luận
Xử lý dữ liệu được phân tách bằng dấu phẩy hoặc dấu phân cách khác có thể là một vấn đề lớn nếu bạn không biết cách trích xuất từng mục vào ô riêng của mình