Cách chuyển văn bản thành cột mà không ghi đè trong Excel

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

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

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

Để dễ hiểu, chúng tôi sẽ sử dụng Danh sách tên và tuổi của học sinh của một định chế 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 tôi sẽ chuyển đổi các văn bản này trong Cột B vào các cột khác nhau mà không ghi đè theo nhiều cách. Ở đây, ghi đè có nghĩa là, văn bản chính trở nên nguyên vẹn trong khi thực hiện tác vụ. Vì vậy, chúng ta hãy khám phá từng cái một.
Ở đây, chúng tôi đã sử dụng Microsoft Excel 365 phiên bản, bạn có thể sử dụng bất kỳ phiên bản nào khác tùy theo sự thuận tiện của bạn.

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

Đối với những người thích chơi với các công thức, phương pháp này là dành cho họ. Trong phương pháp này, chúng tôi sẽ kết hợp một số hàm để làm cho công thức của chúng tôi hoạt động. Vì vậy, hãy xem nó hoạt động.

📌 Các bước:

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

• 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) → Sự Hàm LEN trả về số ký tự trong một chuỗi văn bản. Đây, B6 ô là văn bản đối số mang lại giá trị 11.
  • Đầu ra → 11
• REPT (”“, LEN ($ B6)) → trở thành
  • REPT (”“, 11) → Chức năng REPT lặp lại văn bản một số lần nhất định. Ở đây, ““ là văn bản đối số đề cập đến trống dấu cách trong khi 11 là number_times đối số hướng dẫn hàm chèn 11 khoảng trống nhiều lần.
  • Đầu ra → “”
• SUBSTITUTE ($ B6, “”, REPT (“”, LEN ($ B6))) → Hàm SUBSTITUTE thay thế văn bản hiện có bằng văn bản mới trong một chuỗi văn bản. Đây, B6 đề cập đến văn bản trong khi Tiếp theo, “,” đại diện cho old_text và REPT (“”, LEN ($ B6)) trỏ đến new_text đối số thay thế dấu phẩy bằng dấu cách trống.
  • Đầu ra → John 18 tuổi
• MID (SUBSTITUTE ($ B6, ”,”, REPT (“”, LEN ($ B6))), (C $ 5-1) * LEN ($ B6) + 1, LEN ($ B6) ) → Hàm MID trả về các ký tự từ giữa một chuỗi văn bản, với vị trí và độ dài bắt đầu. Đây, SUBSTITUTE ($ B6, ”,”, REPT (”“, LEN ($ B6))) ô là văn bản đối số, (C $ 5-1) * LEN ($ B6) +1 là start_num đối số và LEN ($ B6) là num_chars đối số sao cho hàm trả về ký tự đầu tiên từ phía bên trái.
  • Đầu ra → John
• TRIM (MID (SUBSTITUTE ($ B6, ”,”, REPT (“”, LEN ($ B6))), (C $ 5-1) * LEN ($ B6) + 1, LEN ($ B6))) → trở thành
  • TRIM (John) → Chức năng TRIM xóa tất cả trừ các khoảng trắng khỏi văn bản. Đây, John ô là văn bản đối số và hàm loại bỏ khoảng trắng thừa sau văn bản.
  • Đầu ra → John

• Sau đó, nhấn ENTER .

• Thứ hai, kéo Xử lý điền công cụ để sao chép công thức trên hàng.

Do đó, chúng ta có thể thấy phần bên phải của văn bản, đó là độ tuổi trong ô D6 .

• Tại thời điểm này, hãy chọn hai ô C6 và D6 cùng với nhau. Đặt con trỏ đến góc dưới cùng bên phải của ô D6 . Bạn có thể xem Xử lý điền công cụ một lần nữa.
• Sau đó, nhấp đúp vào nó để áp dụng công thức cho các ô bên dưới.

Đây là kết quả xuất hiện trước mắt chúng ta.

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

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

Bạn lo lắng về việc viết các biểu thức phức tạp và kết hợp các hàm? Sau đó, phương pháp thứ hai của chúng tôi là câu trả lời cho lời cầu nguyện của bạn! Trong phương pháp này, chúng tôi cũng sẽ sử dụng công thức, nhưng lần này sẽ dễ hiểu hơn. Vì vậy, đừng chần chừ thêm nữa, hãy xem cách chúng tôi thực hiện.

📌 Các bước:

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

Đây, B5 đóng vai trò là Tên, Tuổi đầu tiên .

• TÌM KIẾM (“,”, B5) → Chức năng TÌM KIẾM trả về vị trí bắt đầu của một chuỗi văn bản trong một chuỗi văn bản khác. Đây, “,” là find_text đối số trong khi B5 là within_text tranh luận. Cụ thể, chức năng TÌM KIẾM trả về vị trí của dấu phẩy (,) ký tự trong chuỗi văn bản.
  • Đầu ra → 5

• LEFT (B5, SEARCH (“;”, B5) -1) → trở thành
  • TRÁI (B5,5) → Hàm LEFT trả về số ký tự được chỉ định từ đầu chuỗi. Đây, B5 ô là văn bản đối số trong khi 5 là num_chars đối số sao cho hàm trả về giá trị 5 ký tự từ phía bên trái.
  • Đầu ra → John

• Thứ hai, nhấn vào ENTER chìa khóa.

Bây giờ, chúng tôi sẽ sử dụng một công thức tương tự khác để tìm kết quả trong ô D5 . Trong công thức trước đó , chúng tôi đã sử dụng hàm LEFT để lấy phần đầu tiên của chuỗi văn bản. Tại đây, chúng tôi sẽ tìm nạp phần thứ hai của chuỗi.

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

• LEN (B5) -SEARCH (“,”, B5) → Chức năng LEN trả về độ dài của chuỗi trong B5 ngược lại, ô hàm TÌM KIẾM trả về vị trí của dấu phẩy (,) nhân vật.
  • Đầu ra → 11 - 5 → 6
• RIGHT (B5, LEN (B5) -SEARCH (“,”, B5)) → trở thành
  • RIGHT (B5,6) → Chức năng RIGHT trả về số ký tự được chỉ định từ cuối chuỗi. Đây, B5 ô là đối số văn bản trong khi 6 là num_chars đối số sao cho hàm trả về giá trị 6 ký tự từ phía bên phải.
• Đầu ra → 18 năm

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

Trong phương pháp này, chúng tôi sẽ thực hiện tác vụ với Văn bản thành cột của Excel tính năng, một công cụ tiện dụng để chuyển đổi văn bản thành cột mà không cần ghi đè. Do đó, hãy quan sát và tìm hiểu về quy trình trong các bước được hiển thị bên dưới.

📌 Các bước:

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

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

• Ở bước 1, chọn Được phân cách trong phần Chọn loại tệp mô tả tốt nhất dữ liệu của bạn phần.
• Sau đó, nhấp vào nút Tiếp theo nút.

• Ở bước 2, chọn Dấu phẩy trong Delimeter phần.
• Sau đó, nhấp vào Tiếp theo .

• Ở bước thứ ba và cuối cùng, hãy đặt Đích đến ô dưới dạng C5 .
• Cuối cùng, nhấp vào nút Hoàn tất nút.

Sau khi hoàn thành các bước, Excel sẽ hiển thị MsgBox với một cảnh báo. Đừng lo lắng.

• Chỉ cần nhấp vào OK tại đây.

Thật ngạc nhiên, văn bản thành cột mà không ghi đè được thực hiện trong Excel.

Đọc thêm: Cách sử dụng tính năng Văn bản thành Cột với Tính năng Chuyển hàng trở lại trong Excel

4. Triển khai tính năng lấp đầy Flash

Nếu việc sử dụng các công thức phức tạp không phù hợp với bạn, thì phương pháp tiếp theo của chúng tôi có thể là câu trả lời mà bạn đang tìm kiếm. Tại đây, chúng tôi sẽ áp dụng Flash Fill tính năng của Excel để chuyển đổi văn bản thành cột mà không ghi đè.

📌 Các bước:

• Ban đầu, hãy viết ra John trong ô C5 . Đó là kết quả mong muốn trong ô này. Nhưng đây là lần đầu tiên, chúng tôi phải làm thủ công.
• Sau đó, chuyển đến Trang chủ tab.
• Sau đó, nhấp vào nút Điền biểu tượng thả xuống trên Chỉnh sửa nhóm.
• Sau đó, chọn Flash Fill tính năng.

Thật kỳ diệu, Excel sẽ tự động điền phần còn lại của các ô.

Tương tự , hãy làm tương tự đối với Age dưới Cột D .

Đọc thêm: Cách sử dụng Ngắt dòng làm Dấu phân cách trong Excel Văn bản thành Cột

5. Áp dụng mã VBA

Mặc dù sử dụng công thức là một cách nhanh chóng để chỉnh sửa dữ liệu, nhưng nó có thể khó diễn giải. Hơn nữa, nếu bạn thường xuyên cần làm điều đó tự động, thì bạn có thể xem xét VBA mã bên dưới.

📌 Các bước:

• Trước hết, hãy chuyển đến Nhà phát triển tab.
• Thứ hai, chọn Visual Basic trên Mã nhóm.
• Ngoài ra, sử dụng phím tắt ALT + F11 để lặp lại nhiệm vụ.

Đột nhiên, Microsoft Visual Basic cho ứng dụng cửa sổ xuất hiện.

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

Ngay lập tức, một mô-đun mã mở ra ở phía bên phải.

• Trong trường hợp này, hãy sao chép mã sau và dán chúng vào mô-đun.

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, chúng tôi sẽ giải thích VBA mã được sử dụng để chuyển đổi văn bản thành cột mà không ghi đè. Trong trường hợp này, mã được chia thành 2 các bước.

• Trong phần đầu tiên, quy trình phụ được đặt tên, ở đây nó là Text_to_Columns_without_Overwriting () .
• Tiếp theo, xác định các biến Arr , cnt và j dưới dạng Chuỗi , Dài và Biến thể .
• Trong lọ thuốc thứ hai, sử dụng For Loop qua từng ô và tách văn bản được phân tách bằng dấu phẩy.
• Bây giờ, trong mã, câu lệnh For k =5 To 14 đại diện cho số hàng bắt đầu và số hàng kết thúc của dữ liệu, đây là 5 đến 14 .
• Sau đó, “,” trong Arr =Split (Ô (k, 2), “;”) là dấu phân cách mà bạn có thể thay đổi thành dấu chấm phẩy, dấu gạch ngang, v.v. nếu muốn.
• Cuối cùng, cnt =3 cho biết số cột thứ ba ( Cột C ).

• Bây giờ, hãy nhấp vào nút phát màu xanh lục trên dải băng. Trên thực tế, đó là Run cái nút. Ngoài ra, bạn có thể nhấn F5 trên bàn phím để làm tương tự.

Cuối cùng, kết quả sẽ xuất hiện trong ảnh chụp màn hình dưới đây.

Phần thực hành

Để tự mình thực hành, chúng tôi đã cung cấp một Thực hành như hình dưới đây trong mỗi trang tính ở phía bên phải. Hãy làm điều đó một mình.

Kết luận

Bài viết này giải thích cách chuyển văn bản thành cột mà không ghi đè trong Excel một cách đơn giản và ngắn gọn. Đừng quên tải xuống Thực hành tập tin. Cảm ơn bạn đã đọc bài viết này. Chúng tôi hy vọng điều này là hữu ích. Vui lòng cho chúng tôi biết trong phần bình luận nếu bạn có bất kỳ thắc mắc hoặc đề xuất nào. Vui lòng truy cập trang web của chúng tôi, Exceldemy , một nhà cung cấp giải pháp Excel một cửa, để khám phá thêm.

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

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