Việc tách nội dung từ một ô thành nhiều cột trong Microsoft Excel theo cách thủ công sẽ tốn rất nhiều thời gian và dễ dẫn đến sai sót. May mắn thay, phần mềm này cung cấp nhiều cách thức khác nhau – từ các công cụ tích hợp sẵn, quy trình tự động hóa cho đến các hàm dễ sử dụng – để thực hiện tác vụ sắp xếp dữ liệu này một cách hiệu quả.
Trong bài viết này, chúng ta sẽ cùng khám phá các phương pháp tách dữ liệu trong Excel thành nhiều cột, giúp bạn làm chủ công việc và tăng năng suất đáng kể.
1. Sử Dụng Công Cụ Text to Columns để Tách Dữ Liệu
Một trong những cách phổ biến để tách dữ liệu thành nhiều cột trong Microsoft Excel là sử dụng công cụ tích hợp sẵn “Text to Columns” (Chuyển Văn bản thành Cột). Phương pháp này rất hữu ích nếu bạn thích làm việc trong một hộp thoại có hướng dẫn từng bước.
Ví dụ, hãy xem xét trường hợp bạn muốn tách họ và tên đầy đủ trong Cột A thành cột Họ và Tên riêng biệt, đặt ở Cột B và C.
Bảng Excel hiển thị cột A chứa tên đầy đủ, cột B và C được đặt tên "Họ" và "Tên" tương ứng, sẵn sàng để tách.
Để thực hiện điều này, trước tiên, hãy chọn các ô trong Cột A, sau đó trên tab Data (Dữ liệu) của dải băng (ribbon), nhấp vào “Text to Columns”.
Dữ liệu trong bảng Excel được chọn, và công cụ "Text to Columns" trên tab "Data" được tô sáng.
Trong Convert Text to Columns Wizard (Trình hướng dẫn Chuyển Văn bản thành Cột), chọn “Delimited” (Phân cách bằng dấu), sau đó nhấp vào “Next” (Tiếp theo).
Lựa chọn "Delimited" trong hộp thoại Convert Text to Columns Wizard của Excel.
Dấu phân cách là một ký tự, biểu tượng hoặc khoảng trắng được sử dụng để phân tách các mục trong một chuỗi. Trong ví dụ này, tên trong Cột A được phân tách bằng dấu phẩy và một khoảng trắng, vì vậy hãy chọn cả hai tùy chọn này. Bạn có thể xem cách điều này sẽ ảnh hưởng đến dữ liệu của mình trong phần xem trước ở cuối hộp thoại. Nếu hài lòng, nhấp vào “Next”.
Tùy chọn "Comma" (dấu phẩy) và "Space" (khoảng trắng) được chọn làm dấu phân cách trong Convert Text to Columns Wizard trên Excel.
Tiếp theo, xóa mọi thông tin trong trường Destination (Đích), chọn ô mà bạn muốn dữ liệu đã tách được đặt – trong trường hợp này là ô B2 – và nhấp vào “Finish” (Hoàn tất).
Ô B2 được chọn làm vị trí đích (Destination) trong hộp thoại Convert Text to Columns Wizard của Excel.
Nếu bạn muốn thay thế dữ liệu gốc bằng phiên bản đã tách, hãy chọn ô trên cùng bên trái của dữ liệu gốc trong trường Destination. Trong ví dụ này, đó sẽ là ô A2.
Bây giờ, họ và tên đầy đủ đã được tách thành cột Họ và Tên riêng biệt. Nếu cần, bạn có thể xóa dữ liệu gốc trong Cột A, vì tên đã tách không liên kết theo bất kỳ cách nào với tên đầy đủ ban đầu.
Bảng Excel sau khi tách thành công họ và tên của chín người từ cột A sang cột B (Họ) và cột C (Tên).
Tuy nhiên, trong ví dụ này, một người có hai tên riêng và một người khác có hai họ. Trong trường hợp này, việc chọn dấu phân cách khoảng trắng sẽ tách các tên kép này thành các cột riêng biệt, mặc dù bạn muốn giữ chúng lại với nhau.
Danh sách tên trong bảng Excel, với các tên có nhiều hơn một từ ở họ hoặc tên được tô sáng, minh họa trường hợp cần xử lý đặc biệt.
Để khắc phục điều này, chỉ chọn dấu phân cách dấu phẩy – không chọn khoảng trắng – trong Convert Text to Columns Wizard.
Dấu phẩy được chọn làm dấu phân cách duy nhất trong hộp thoại Convert Text to Columns Wizard của Excel để xử lý tên có nhiều từ.
Khi quá trình tách hoàn tất, bạn sẽ nhận thấy rằng các tên riêng bị dính một khoảng trắng ở phía trước, vì bạn đã không yêu cầu Excel coi khoảng trắng là dấu phân cách.
Cột C trong Excel hiển thị các tên riêng bị dính khoảng trắng ở đầu mỗi ô sau khi tách chỉ bằng dấu phẩy.
Để loại bỏ những khoảng trắng này, hãy mở rộng bảng thêm một cột sang phải để tạo một cột khác cho các tên riêng đã được sửa. Sau đó, trong ô D2, gõ công thức:
=TRIM([@[First name]])Trong đó hàm TRIM loại bỏ tất cả các khoảng trắng khỏi một chuỗi văn bản (ngoại trừ khoảng trắng giữa các từ), và [@][First name]] là tham chiếu có cấu trúc đến cột trong bảng có tên “First names” (Tên riêng).
Công thức hàm TRIM được sử dụng trong Excel để loại bỏ khoảng trắng thừa ở đầu các tên riêng trong cột D.
Khi bạn nhấn Enter, các tên riêng sẽ xuất hiện trong Cột D, nhưng lần này không có khoảng trắng đầu. Đồng thời, vì dữ liệu nằm trong một bảng Excel đã định dạng, công thức sẽ tự động được sao chép vào các ô còn lại của cột.
Danh sách tên trong cột D của Excel đã được làm sạch khoảng trắng đầu bằng hàm TRIM, dựa trên dữ liệu từ cột C.
Tiếp theo, chọn các tên riêng mới được tạo và nhấn Ctrl+C để sao chép chúng. Bây giờ, nhấn Ctrl+Alt+V để mở hộp thoại Paste Special (Dán đặc biệt), sau đó nhấn V để chọn “Values” (Giá trị), rồi nhấn Enter.
Tùy chọn "Values" (Giá trị) được chọn trong hộp thoại Paste Special của Microsoft Excel để dán dữ liệu dưới dạng giá trị.
Cuối cùng, xóa cột chứa khoảng trắng trước tên riêng (C) và cột gốc chứa tên đầy đủ (A) để hoàn thành bảng của bạn.
Bảng Excel đã hoàn chỉnh với họ ở cột A và tên ở cột B, sau khi xử lý và loại bỏ các cột tạm thời.
2. Tách Dữ Liệu Nhanh Chóng với Flash Fill (Điền Nhanh)
Microsoft Excel có nhiều công cụ để tự động hóa các quy trình tẻ nhạt, và Flash Fill là một trong những công cụ hữu ích nhất. Nếu việc sử dụng Text to Columns Wizard để tách dữ liệu mất quá nhiều thời gian, công cụ trực quan này sẽ giúp tăng tốc mọi thứ. Về cơ bản, đó là một cách thông minh hơn để sao chép và dán dữ liệu vào các ô mới.
Đầu tiên, chọn ô mà bạn muốn chứa phần tử đầu tiên đã tách (trong trường hợp này là ô B2), gõ giá trị mong muốn (trong trường hợp này là Smith) theo cách thủ công và nhấn Enter.
Bảng Excel với chữ "Smith" được gõ thủ công vào ô đầu tiên của cột "Họ" để khởi tạo mẫu cho Flash Fill.
Tiếp theo, trong tab Data trên dải băng, nhấp vào “Flash Fill”. Nếu bạn thích sử dụng các phím tắt của Microsoft Excel, hãy nhấn Ctrl+E.
Nút "Flash Fill" trên tab "Data" của dải băng Excel được chọn để tự động điền dữ liệu.
Khi bạn chạy Flash Fill, Excel sẽ tìm kiếm các mẫu trong dữ liệu và cố gắng áp dụng chúng cho phần dữ liệu còn lại trong phạm vi. Trong ví dụ này, nó đã điền thành công phần còn lại của họ từ dữ liệu gốc xuống cột B.
Cột B hiển thị các họ còn lại đã được điền tự động bằng công cụ Flash Fill của Excel.
Sau đó, lặp lại quy trình tương tự cho các tên riêng trong Cột C – gõ giá trị đầu tiên để thiết lập mẫu, nhấn Enter, sau đó nhấn Ctrl+E.
Cột C hiển thị các tên riêng còn lại đã được điền tự động bằng công cụ Flash Fill của Excel.
Flash Fill được sử dụng tốt nhất trên các tập dữ liệu nhỏ, nơi bạn có thể nhanh chóng kiểm tra xem công cụ đã xác định đúng các mẫu trong dữ liệu của mình hay chưa. Tuy nhiên, nếu bạn đang làm việc với nhiều hàng dữ liệu, hãy sử dụng một công cụ đáng tin cậy hơn – như các hàm tách văn bản của Excel – không yêu cầu bạn phải xác minh kết quả chặt chẽ.
3. Sử Dụng Các Hàm Có Sẵn Trong Excel để Tách Dữ Liệu
Một cách khác để tách dữ liệu thành nhiều cột là sử dụng một số hàm của Microsoft Excel. Nếu bạn chọn cách này, hãy nhớ rằng các giá trị đã tách sẽ được liên kết với các giá trị gốc, nghĩa là bạn cần sao chép (Ctrl+C) và dán chúng dưới dạng giá trị (Ctrl+Alt+V > V) nếu bạn muốn xóa dữ liệu gốc.
3.1. Hàm TEXTSPLIT: Tách Tất Cả Nội Dung Một Ô
Hàm TEXTSPLIT của Excel chia tất cả nội dung trong một ô thành các cột khác nhau theo dấu phân cách bạn chỉ định và trả về kết quả dưới dạng một mảng tràn (spilled array).
Mặc dù các hàm mảng động tạo ra các mảng tràn giúp tiết kiệm thời gian bằng cách trả về nhiều hơn một kết quả từ một công thức, nhưng hãy lưu ý rằng chúng không tương thích với các bảng Excel đã định dạng. Điều này có nghĩa là bạn chỉ có thể sử dụng TEXTSPLIT trong các phạm vi thông thường.
Trong ví dụ này, giả sử bạn muốn tách các thủ đô và tiểu bang trong Cột A thành Cột B và C bằng một công thức duy nhất.
Danh sách thủ đô và tiểu bang trong cột A của Microsoft Excel, với cột B và C đã sẵn sàng để tách dữ liệu thành các tập hợp riêng biệt.
Để thực hiện điều này bằng TEXTSPLIT, trong ô B2, gõ công thức:
=TEXTSPLIT(A2,", ")Trong đó A2 là ô chứa văn bản bạn muốn tách, và sự kết hợp dấu phẩy + khoảng trắng trong dấu ngoặc kép cho Excel biết rằng các ký tự này cùng nhau tạo thành dấu phân cách.
Hàm TEXTSPLIT đang được sử dụng trong Microsoft Excel để tách tên tiểu bang và thủ đô của chúng thành các cột riêng biệt.
Khi bạn nhấn Enter và chọn ô bạn vừa gõ công thức, bạn sẽ thấy một đường màu xanh lam bao quanh kết quả. Điều này có nghĩa là mặc dù bạn chỉ gõ công thức vào ô B2, nhưng kết quả đã tràn sang ô C2.
Mảng tràn (spilled array) trong Microsoft Excel, được biểu thị bằng đường viền màu xanh dương bao quanh các ô bị ảnh hưởng bởi công thức.
Vì dữ liệu nằm trong một phạm vi không được định dạng dưới dạng bảng Excel, bạn cần điền phần còn lại của cột theo cách thủ công. Để làm điều này, hãy nhấp và kéo tay nắm điền (fill handle) ở góc dưới bên phải của ô B2 xuống cuối phạm vi dữ liệu.
Tay nắm điền (fill handle) trong Excel được sử dụng để sao chép một công thức từ ô B2 xuống các ô từ B3 đến B10.
3.2. Hàm TEXTBEFORE và TEXTAFTER: Tách Có Chọn Lọc
Trong khi TEXTSPLIT chia tất cả nội dung của một ô, các hàm TEXTBEFORE và TEXTAFTER cho phép bạn chọn liệu bạn muốn trích xuất thông tin trước hay sau một dấu phân cách cụ thể. Ngoài ra, vì chúng không phải là hàm mảng động, bạn có thể sử dụng chúng trong dữ liệu đã định dạng dưới dạng bảng.
Sử dụng cùng ví dụ như trên, bắt đầu với các thủ đô tiểu bang, trong ô B2, gõ công thức:
=TEXTBEFORE([@[Capital and State]],", ")Trong đó [@][Capital and State]] là tham chiếu có cấu trúc đến cột chứa các ô bạn muốn tách, và sự kết hợp dấu phẩy + khoảng trắng bên trong dấu ngoặc kép cho Excel biết rằng dấu phẩy theo sau là khoảng trắng nên được coi là dấu phân cách. Lần này, chỉ văn bản trước dấu phân cách được trả về trong kết quả khi bạn nhấn Enter.
Hàm TEXTBEFORE trong Excel được dùng để trích xuất các thủ đô tiểu bang vào cột B.
Để trích xuất các tên tiểu bang trong ô C2, gõ công thức:
=TEXTAFTER([@[Capital and State]],", ")và nhấn Enter.
Hàm TEXTAFTER trong Excel được dùng để trích xuất các tên tiểu bang vào cột C.
Bên cạnh khả năng tương thích với bảng Excel, một lợi ích của việc sử dụng TEXTBEFORE và TEXTAFTER thay vì TEXTSPLIT là kết quả không cần phải nằm trong các cột liền kề.
3.3. Hàm LEFT, MID, và RIGHT: Tách Theo Vị Trí Ký Tự
Các hàm LEFT, MID và RIGHT của Excel cho phép bạn tách nội dung của một ô tại các vị trí nhất định trong chuỗi ký tự. Điều này đặc biệt tiện dụng nếu bạn có một tập dữ liệu gồm các giá trị có cấu trúc nhất quán, như số sê-ri hoặc mã code.
Trong ví dụ này, ba ký tự đầu tiên đại diện cho một quốc gia, ba ký tự cuối cùng đại diện cho một ID, và X hoặc Y ở giữa đại diện cho một thứ hạng. Mục tiêu của bạn là tách ba yếu tố này thành các cột B, C và D tương ứng.
Danh sách mã code trong Excel, với các cột tiếp theo được chuẩn bị để tách các mã này thành ba yếu tố khác nhau.
Đầu tiên, để trích xuất các quốc gia, trong ô B2, gõ công thức:
=LEFT([@Code],3)Trong đó LEFT([@Code] cho Excel biết đọc các ô trong cột Code từ bên trái, và 3 cho Excel biết trích xuất ba ký tự đầu tiên.
Tên quốc gia được trích xuất từ một mã code trong Excel bằng cách sử dụng hàm LEFT.
Bây giờ, trong ô D2, hãy làm theo nguyên tắc tương tự, nhưng sử dụng hàm RIGHT để trích xuất ba ký tự cuối cùng:
=RIGHT([@Code],3)
ID được trích xuất từ một mã code trong Excel bằng cách sử dụng hàm RIGHT.
Cuối cùng, trong ô C2, bạn muốn trích xuất ký tự ở giữa, là ký tự thứ tư trong chuỗi. Để làm điều này, gõ công thức:
=MID([@Code],4,1)Trong đó 4 cho Excel biết bắt đầu trích xuất từ ký tự thứ tư, và 1 cho Excel biết bạn chỉ muốn trích xuất một ký tự duy nhất.
Xếp hạng được trích xuất từ một mã code trong Excel bằng cách sử dụng hàm MID.
Bây giờ, bạn đã tách thành công dữ liệu trong Cột A thành ba cột riêng biệt.
4. Sử Dụng Power Query Editor để Tách Dữ Liệu Nâng Cao
Nhiều người tin rằng Power Query Editor của Excel quá phức tạp đối với họ – tuy nhiên, nó được thiết kế đặc biệt để thân thiện với người dùng và là một cách tuyệt vời để tách dữ liệu thành nhiều cột.
Trong ví dụ này, giả sử bạn có bảng Excel này chứa các đội NFL khác nhau, và bạn muốn tách chúng thành các khu vực và tên đội tương ứng.
Danh sách các đội NFL, khu vực và liên đoàn trong Excel.
Đầu tiên, chọn bất kỳ ô nào trong dữ liệu, và trong tab Data trên dải băng, nhấp vào “From Table/Range” (Từ Bảng/Phạm vi) trong nhóm Get & Transform Data (Truy vấn và Chuyển đổi Dữ liệu).
Một ô trong tập dữ liệu Excel được chọn, và tùy chọn "From Table/Range" (Từ Bảng/Phạm vi) trong tab "Data" được tô sáng.
Thao tác này sẽ khởi chạy Power Query Editor, nơi “phép màu” sẽ xảy ra!
Nhấp chuột phải vào tiêu đề cột bạn muốn tách, và nhấp vào Split Column (Tách cột) > By Delimiter (Theo dấu phân cách).
Một tiêu đề cột trong Power Query Editor của Excel được chọn qua chuột phải, và tùy chọn "By Delimited" (Theo dấu phân cách) trong "Split Column" (Tách cột) được chọn.
Sau đó, trong hộp thoại, chọn dấu phân cách – trong trường hợp này là khoảng trắng – và xem lại dữ liệu để xác định tại các dấu phân cách nào bạn muốn tách dữ liệu. Vì một số khu vực trong tên đội NFL có nhiều hơn một từ, nhưng tất cả các hậu tố của chúng chỉ chứa một từ, văn bản cần được chia theo dấu phân cách ngoài cùng bên phải (right-most delimiter).
Hộp thoại Split Column By Delimiter trong Power Query Editor của Excel với khoảng trắng được chọn làm dấu phân cách và "Right-most delimiter" (Dấu phân cách ngoài cùng bên phải) được chọn làm vị trí tách.
Bây giờ, khi bạn nhấp “OK”, bạn sẽ thấy tên các đội được chia thành hai cột tại dấu phân cách cuối cùng. Tại thời điểm này, hãy nhấp đúp vào các tiêu đề cột mới để đổi tên chúng.
Các cột đang được đổi tên trong Power Query Editor của Excel sau khi tách dữ liệu.
Cuối cùng, nhấp vào nửa trên của biểu tượng “Close And Load” (Đóng và Tải) ở góc trên bên trái của Power Query Editor, và dữ liệu mới được tách sẽ mở ra trong một bảng tính mới.
Nút "Close And Load" (Đóng và Tải) trong Power Query Editor của Excel được chọn để đưa dữ liệu đã xử lý về bảng tính.
Nếu dữ liệu gốc thay đổi, hãy nhớ nhấp vào “Refresh” (Làm mới) trong tab Table Design (Thiết kế Bảng) sau khi chọn bảng được tạo thông qua Power Query Editor. Ví dụ, nếu bạn thêm một đội khác vào cuối bảng, Excel sẽ tự động tách đội này theo bước bạn đã tạo trong Power Query Editor.
Tách dữ liệu thành nhiều cột không phải là cách duy nhất để sắp xếp lại dữ liệu trong Excel. Chẳng hạn, bạn có thể hợp nhất dữ liệu từ hai cột thành một, tách các hàng xen kẽ thành hai cột, hoặc chuyển đổi dữ liệu dọc thành ngang. Bất kể bạn muốn tổ chức dữ liệu của mình như thế nào, luôn có một công cụ Excel tích hợp sẵn sẽ giúp bạn thực hiện công việc đó.
Nếu bạn có bất kỳ thắc mắc hay mẹo nào khác về cách tách dữ liệu trong Excel, đừng ngần ngại chia sẻ trong phần bình luận bên dưới nhé!
