Tách văn bản thành cột trong Excel an toàn không ghi đè dữ liệu gốc

Thường xuyên, chúng ta cần chuyển văn bản thành cột trong Excel trong công việc hàng ngày. Thực tế, Microsoft Excel là công cụ tuyệt vời để thực hiện nhiệm vụ này. Và chắc chắn, bạn có thể tìm thấy số lượng bài viết đầy đủ về chủ đề này. Nhưng đặc điểm của bài viết này là chúng tôi sẽ chỉ cách chuyển văn bản thành cột mà không ghi đè trong Excel bằng nhiều phương pháp. Nghĩa là cột nguồn vẫn nguyên vẹn thay vì bị ghi đè. Vì vậy, hãy xem qua để tự làm nhiệm vụ này hiệu quả.

Bạn có thể tải sổ làm việc Excel sau để hiểu tốt hơn và tự thực hành.

5 phương pháp chuyển văn bản thành cột không ghi đè trong Excel

Để dễ hiểu, chúng ta sẽ dùng Danh sách Tên và Tuổi Học sinh của một cơ sở nào đó. Tập dữ liệu này bao gồm Tên, Tuổi trong Cột B.

Bây giờ, chúng ta sẽ chuyển văn bản trong Cột B thành các cột khác nhau không ghi đè theo nhiều cách. Ở đây, ghi đè nghĩa là văn bản chính vẫn nguyên vẹn khi làm nhiệm vụ. Vì vậy, hãy khám phá từng phương pháp một. Ở đây, chúng tôi dùng phiên bản Microsoft Excel 365, bạn có thể dùng phiên bản khác tùy thuận tiện.

1. Áp dụng hàm LEN, MID, REPT, SUBSTITUTE và TRIM

Dành cho những ai thích chơi với công thức, phương pháp này dành cho bạn. Trong phương pháp này, chúng ta sẽ kết hợp một số hàm để công thức hoạt động. Vậy, hãy xem nó hoạt động như thế nào.

📌 Các bước:

• Đầu tiên, tạo 2 cột khác nhau với tiêu đề Tên và Tuổi dưới cột C và D.
• Trong ô C5 và D5, viết 1 và 2 tương ứng. Đây là số thứ tự các cột mới. Bạn sẽ hiểu chức năng của 2 ô này ở các bước sau.

• Sau đó, chọn ô C6 và nhập công thức sau.

Ở đây, ô B6 và C5 tham chiếu đến Tên, Tuổi đầu tiên và số 1.

• LEN($B6) → Hàm LEN trả về số ký tự trong chuỗi văn bản. Ở đây, ô B6 là đối số text cho ra giá trị 11.
  • Kết quả → 11
• REPT(" ",LEN($B6)) → trở thành
  • REPT(" ",11) → Hàm REPT lặp lại văn bản số lần cho trước. Ở đây, " " là đối số text là khoảng trống, 11 là đối số number_times yêu cầu chèn 11 khoảng trống liên tiếp.
  • Kết quả → " "
• SUBSTITUTE($B6,",",REPT(" ",LEN($B6))) → Hàm SUBSTITUTE thay thế văn bản cũ bằng mới trong chuỗi. Ở đây, B6 là đối số text, "," là đối số old_text, và REPT(" ",LEN($B6)) là đối số new_text thay dấu phẩy bằng khoảng trống.
  • Kết quả → John 18 yr
• MID(SUBSTITUTE($B6,",",REPT(" ",LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6)) → Hàm MID trả về ký tự từ giữa chuỗi, cho vị trí bắt đầu và độ dài. Ở đây, SUBSTITUTE(...) là đối số text, (C$5-1)*LEN($B6)+1 là start_num, và LEN($B6) là num_chars để hàm trả về ký tự đầu tiên từ trái.
  • Kết quả → John
• TRIM(MID(...)) → trở thành
  • TRIM(John ) → Hàm TRIM xóa khoảng trống thừa, chỉ giữ một khoảng. Ở đây, John là đối số text, hàm loại bỏ khoảng trống thừa sau văn bản.
  • Kết quả → John

• Sau đó, nhấn ENTER.

• Thứ hai, kéo Fill Handle để sao chép công thức ngang hàng.

Do đó, ta thấy phần phải của văn bản, là tuổi, ở ô D6.

• Lúc này, chọn hai ô C6 và D6 cùng lúc. Đặt con trỏ góc dưới phải ô D6. Bạn sẽ thấy Fill Handle lại.
• Sau đó, nhấp đúp để áp dụng công thức cho các ô dưới.

Kết quả xuất hiện trước mắt.

Xem thêm: Cách tách văn bản thành cột tự động bằng công thức trong Excel

2. Chèn hàm LEFT, LEN, RIGHT và SEARCH

Bạn lo lắng về viết biểu thức phức tạp và kết hợp hàm? Phương pháp 2 là câu trả lời cho bạn! Ở đây chúng ta cũng dùng công thức, nhưng dễ hiểu hơn. Không chần chừ, hãy xem cách làm.

📌 Các bước:

• Trước hết, đến ô C5 và chèn công thức sau.

Ở đây, B5 đóng vai Tên, Tuổi đầu tiên.

• SEARCH(",",B5) → Hàm SEARCH trả về vị trí bắt đầu của chuỗi trong chuỗi khác. Ở đây, "," là đối số find_text, B5 là within_text. Cụ thể, SEARCH trả về vị trí dấu phẩy (,) trong chuỗi.
  • Kết quả → 5

• LEFT(B5,SEARCH(",",B5)-1) → trở thành
  • LEFT(B5,5) → Hàm LEFT trả về số ký tự từ đầu chuỗi. Ở đây, B5 là đối số text, 5 là num_chars trả về 5 ký tự từ trái.
  • Kết quả → John

• Thứ hai, nhấn ENTER.

Bây giờ, dùng công thức tương tự cho ô D5. Trong công thức trước, dùng LEFT lấy phần đầu chuỗi. Ở đây, lấy phần sau.

• Hiện tại, đến ô D5 và chèn công thức sau.

• LEN(B5)-SEARCH(",",B5) → LEN trả về độ dài chuỗi ô B5, ngược lại SEARCH trả về vị trí dấu phẩy (,).
  • Kết quả → 11 – 5 → 6
• RIGHT(B5,LEN(B5)-SEARCH(",",B5)) → trở thành
  • RIGHT(B5,6) → Hàm RIGHT trả về ký tự từ cuối chuỗi. B5 là đối số text, 6 là num_chars trả về 6 ký tự từ phải.
• Kết quả → 18 yr

• Sau đó, nhấn ENTER.

3. Sử dụng tính năng Văn bản thành cột

Phương pháp này dùng tính năng Văn bản thành cột của Excel, công cụ tiện lợi chuyển văn bản thành cột không ghi đè. Hãy quan sát và học quy trình qua các bước dưới.

📌 Các bước:

• Trước hết, chọn ô trong phạm vi B5:B14.
• Sau đó, chuyển đến thẻ Dữ liệu.
• Tiếp theo, nhấp vào biểu tượng thả xuống Công cụ dữ liệu.
• Từ danh sách thả xuống, chọn Văn bản thành cột.

Ngay lập tức, hộp thoại Chuyển đổi văn bản thành cột mở ra.

• Bước 1, chọn Phân cách dưới Chọn loại file mô tả dữ liệu tốt nhất.
• Sau đó, nhấp nút Tiếp theo.

• Bước 2, chọn Dấu phẩy trong phần Dấu phân cách.
• Sau đó, nhấp Tiếp theo.

• Bước 3 cuối, đặt ô Đích đến là C5.
• Cuối cùng, nhấp nút Hoàn tất.

Sau các bước, Excel hiển thị MsgBox cảnh báo. Đừng lo.

• Chỉ cần nhấp OK.

Tuyệt vời, tách văn bản thành cột không ghi đè đã xong trong Excel.

Xem thêm: Cách dùng tính năng Văn bản thành cột với ký tự xuống dòng trong Excel

4. Triển khai tính năng Flash Fill

Nếu dùng công thức phức tạp không hợp, phương pháp tiếp theo có thể là câu trả lời. Ở đây, áp dụng Flash Fill của Excel để chuyển văn bản thành cột không ghi đè.

📌 Các bước:

• Ban đầu, viết John vào ô C5. Đó là kết quả mong muốn. Nhưng lần đầu phải làm thủ công.
• Sau đó, chuyển đến thẻ Home.
• Tiếp theo, nhấp biểu tượng thả xuống Điền trong nhóm Chỉnh sửa.
• Sau đó, chọn Flash Fill.

Thần kỳ, Excel tự động điền các ô còn lại.

Tương tự, làm như vậy cho cột Tuổi dưới Cột D.

Xem thêm: Cách dùng ngắt dòng làm dấu phân cách trong Văn bản thành cột Excel

5. Áp dụng mã VBA

Dù dùng công thức nhanh, nhưng khó hiểu. Nếu bạn thường cần tự động, hãy cân nhắc mã VBA dưới.

📌 Các bước:

• Chủ yếu, đến thẻ Developer.
• Phụ, chọn Visual Basic trong nhóm Mã.
• Thay thế, dùng phím tắt ALT + F11.

Đột ngột, cửa sổ Microsoft Visual Basic for Applications xuất hiện.

• Ở đây, nhấp đúp vào Sheet6(VBA) trong phần Project Explorer.

Ngay lập tức, module mã mở bên phải.

• Lúc này, sao chép mã sau và dán vào module.

Sub Text_to_Columns_without_Overwriting()
Dim Arr() As String, cnt As Long, j As Variant
For k = 5 To 14
Arr = Split(Cells(k, 2), ",")
cnt = 3
For Each j In Arr
Cells(k, cnt) = j
cnt = cnt + 1
Next j
Next k
End Sub

Ở đây, giải thích mã VBA chuyển văn bản thành cột không ghi đè. Mã chia thành 2 phần.

• Phần đầu, thủ tục con được đặt tên, ở đây là Text_to_Columns_without_Overwriting().
• Tiếp theo, định nghĩa biến Arr, cnt, j kiểu String, Long, Variant.
• Phần hai, dùng Vòng lặp For qua từng ô và tách văn bản phân cách bằng dấu phẩy.
• Trong mã, câu lệnh For k = 5 To 14 đại diện hàng bắt đầu và kết thúc dữ liệu, ở đây 5 đến 14.
• Sau đó, "," trong Arr = Split(Cells(k, 2), ",") là dấu phân cách, bạn có thể đổi thành chấm phẩy, gạch dọc, v.v.
• Cuối cùng, cnt = 3 chỉ số cột thứ ba (Cột C).

• Bây giờ, nhấp nút chạy màu xanh trên ribbon. Thực tế, đó là nút Run. Cũng có thể nhấn F5.

Cuối cùng, kết quả xuất hiện như ảnh dưới.

Phần thực hành

Để tự thực hành, chúng tôi cung cấp phần Thực hành như bên dưới ở mỗi trang tính bên phải. Hãy tự làm.

Kết luận

Bài viết giải thích cách chuyển văn bản thành cột không ghi đè trong Excel đơn giản, súc tích. Đừng quên tải file Thực hành. Cảm ơn bạn đã đọc. Hy vọng hữu ích. Hãy cho chúng tôi biết trong phần bình luận nếu có thắc mắc hoặc đề xuất. Truy cập trang web, Exceldemy, nhà cung cấp giải pháp Excel trọn gói, để khám phá thêm.

Các bài viết liên quan

• Cách dùng Văn bản thành cột trong Excel cho ngày tháng (Với các bước dễ dàng)
• [Đã khắc phục!] Văn bản thành cột Excel đang xóa dữ liệu
• Cách chuyển văn bản thành cột với nhiều dấu phân cách trong Excel