Cách chuyển văn bản thành cột với nhiều dấu phân cách trong Excel

Chuyển đổi văn bản thành cột thực sự là một nhiệm vụ phổ biến trong cuộc sống hàng ngày của chúng ta. Trong vô số tình huống, chúng ta phải chia văn bản thành các cột và đây là điểm mà Microsoft Excel vượt trội.
Trong bài viết này, chúng ta sẽ khám phá tất cả những điều thực tế về cách chuyển đổi văn bản thành cột có nhiều dấu phân cách trong Excel .

6 cách chuyển văn bản thành cột với nhiều dấu phân cách trong Excel

Nhân dịp này, giả sử Danh sách những người bán chạy nhất tập dữ liệu được hiển thị trong B4:B13 ô, chứa Tên sách, tác giả và thể loại cột. Trong trường hợp này, chúng tôi muốn tách Tên sách , Tác giả và Thể loại thành các cột riêng biệt. Do đó, đừng chần chừ thêm nữa, hãy khám phá từng phương pháp riêng lẻ.

Ở đâ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.

Phương pháp 1 :Sử dụng Tính năng Văn bản thành Cột

Trước hết, chúng ta sẽ bắt đầu với Văn bản thành cột của Excel tính năng này là một công cụ tiện dụng để chuyển đổi văn bản thành các cột có nhiều dấu phân cách. 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 :

• Ngay từ đầu, hãy chọn B5:B13 ô>> chuyển đến Dữ liệu >> nhấp vào tab Văn bản thành cột tùy chọn.

Ngay lập tức, nút Chuyển văn bản thành cột trình hướng dẫn bật ra.

• Sau đó, chọn Được phân cách tùy chọn>> nhấn vào Tiếp theo nút.

• Tại thời điểm này, hãy chèn một dấu kiểm cho Dấu phẩy dấu phân cách>> nhấn nút Tiếp theo nút.

• Đến lượt mình, hãy nhập Điểm đến ô theo sở thích của bạn, đây là ô C5 ô>> nhấp vào nút Kết thúc nút.

• Bây giờ, một cảnh báo có thể xuất hiện, trong trường hợp này, hãy nhấp vào nút OK nút.

Sau đó, kết quả cuối cùng sẽ giống như ảnh chụp màn hình dưới đây.

Đọ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

Phương pháp 2 :Sử dụng các hàm TRIM, MID, SUBSTITUTE, REPT và LEN

Đối với một điều, các hàm là mạch máu của bảng tính Excel và ở đây chúng tôi sẽ kết hợp TRIM , MID , SUBSTITUTE , REPT và LEN chức năng tách văn bản có nhiều dấu phân cách thành cột. Đây, LEN hàm trả về độ dài của chuỗi và REPT hàm lặp lại văn bản. Tiếp theo, SUBSTITUTE hàm thay thế văn bản cũ bằng văn bản mới trong khi MID hàm trả về các ký tự trong văn bản. Cuối cùng, TRIM hàm xóa bất kỳ khoảng trắng bổ sung nào.

📌 Các bước :

• Đầu tiên, chuyển đến C6 ô>> nhập phương trình cho bên dưới.

=TRIM(MID(SUBSTITUTE($B6,",",REPT(" ",LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6)))

Đây, B6 và C5 các ô tham chiếu đến Tên sách, Tác giả, Thể loại và số 1 .

Phân tích công thức:

• LEN ($ B6) → 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ị 43 .
  • Đầu ra → 43 ”“
• REPT (”“, LEN ($ B6)) → trở thành
  • REPT (”“, 43) → 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 43 là đối số number_times hướng dẫn hàm chèn 43 để trống nhiều lần.
  • Đầu ra → ”“
• SUBSTITUTE ($ B6, ”,”, REPT (”“, LEN ($ B6))) → 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 → “Kinh tế nghèo Abhijit Banarjee Economics”
• MID (SUBSTITUTE ($ B6, ”,”, REPT (”“, LEN ($ B6))), (C $ 5-1) * LEN ($ B6) + 1, LEN ($ B6) ) → 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 → “Kinh tế kém”
• TRIM (MID (SUBSTITUTE ($ B6, ”,”, REPT (”“, LEN ($ B6))), (C $ 5-1) * LEN ($ B6) + 1, LEN ($ B6))) → trở thành
  • TRIM (“Kinh tế kém”) → xóa tất cả trừ các khoảng trắng khỏi văn bản. Đây, “Kinh tế kém” ô 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 → “Kinh tế kém”

• Thứ hai, sử dụng Công cụ xử lý điền để sao chép công thức qua các hàng.

• Thứ ba, chọn C6:E6 ô>> kéo Xử lý điền để áp dụng công thức cho các ô bên dưới.

Cuối cùng, đầu ra của bạn sẽ giống như hình bên dưới.

Đọ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

Phương pháp 3 :Kết hợp các hàm LEFT, RIGHT, MID, LEN và FIND

Ngoài ra, chúng ta có thể sử dụng tổ hợp LEFT , RIGHT , MID , LEN và TÌM HIỂU chức năng chia đoạn văn bản có nhiều dấu phân cách thành các cột khác nhau. Trong trường hợp này, nút TÌM KIẾM hàm tìm kiếm các số trong mảng đã cho và LEN hàm trả về độ dài của chuỗi văn bản. Đổi lại, MID hàm trả về các ký tự từ giữa chuỗi văn bản trong khi LEFT và RIGHT các hàm trích xuất văn bản từ đầu bên trái và bên phải của chuỗi tương ứng.
Giả sử chúng ta có Danh sách khách hàng tập dữ liệu được hiển thị trong B4:B12 các ô chứa Tên, Quốc gia và Thành phố các cột có văn bản được phân tách bằng dấu chấm phẩy. Ở đây, chúng tôi muốn tách Tên, Quốc gia và Thành phố vào các cột khác nhau, vì vậy hãy xem quy trình chi tiết.

📌 Các bước :

Ngay từ đầu, hãy điều hướng đến C5 ô>> chèn biểu thức sau vào Thanh công thức .

=LEFT(B5,FIND(";",B5)-1)

Trong biểu thức trên, B5 ô đại diện cho Tên, Quốc gia và Thành phố cột.

Phân tích công thức:

• TÌM (“;”, B5) → 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ể, FIND hàm trả về vị trí của dấu chấm phẩy (; ) ký tự trong chuỗi văn bản.
  • Đầu ra → 7
• LEFT (B5, FIND (“;”, B5) -1) → trở thành
  • LEFT (B5,7) → trả về số ký tự được chỉ định từ đầu chuỗi. Đây, B5 ô là văn bản đối số trong khi 7 là num_chars đối số sao cho hàm trả về giá trị 7 ký tự từ phía bên trái.
  • Đầu ra → Trevor

• Sau đó, chuyển đến D5 ô>> nhập biểu thức sau.

=MID(B5,FIND(";",B5)+1,FIND("@",B5)-FIND(";",B5)-1)

Giải thích Công thức:

• TÌM (“@”, B5) -FIND (“;”, B5) -1 → đây, TÌM HIỂU hàm trả về vị trí của dấu chấm phẩy (; ) và ở tỷ lệ ( “@” ) các ký tự trong chuỗi văn bản.
  • 16 - 7 - 1 → 8
• TÌM (“;”, B5) +1 → ví dụ: FIND hàm định vị dấu chấm phẩy (; ) các ký tự trong chuỗi văn bản.
  • 7 + 1 → 8
• MID (B5, FIND (“;”, B5) + 1, FIND (“@”, B5) -FIND (“;”, B5) -1) → trở thành
  • MID (B5,8,8) → đây, B5 ô là văn bản đối số, 8 là start_num đối số và 8 là num_chars đối số sao cho hàm trả về giá trị 8 các ký tự sau 8 đầu tiên ký tự.
  • Đầu ra → Iceland

• Sau đó, hãy chèn công thức bên dưới vào E5 ô.

=RIGHT(B5,LEN(B5)-FIND("@",B5))

Công thức này hoạt động như thế nào:

• LEN (B5) -FIND (“@”, B5) → LEN hàm trả về độ dài của chuỗi trong B5 ngược lại, ô TÌM hàm trả về vị trí của ở tỷ lệ ( “@” ) ký tự.
  • 26 - 16 → 10

• PHẢI (B5, LEN (B5) -FIND (“@”, B5)) → trở thành
  • PHẢI (B5,10) → trả về số ký tự được chỉ định từ cuối chuỗi. Đây, B5 ô là văn bản đối số trong khi 10 là num_chars đối số sao cho hàm trả về giá trị 10 ký tự từ phía bên phải.
  • Đầu ra → Reykjavik

Cuối cùng, kết quả sẽ giống như ảnh chụp màn hình bên dưới.

Phương pháp 4 :Sử dụng Flash Fill

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 tính năng Flash Fill của Excel để chuyển đổi văn bản thành các cột có nhiều dấu phân cách.

📌 Các bước :

• Ngay từ đầu, hãy nhập thủ công Tên Trevor trong C5 ô>> trong Trang chủ , nhấp vào tab Điền thả xuống>> chọn Flash Fill tùy chọn.

Bây giờ, Excel sẽ tự động điền phần còn lại của ô.

Tương tự như vậy, áp dụng kỹ thuật cho Quốc gia và Thành phố và đầu ra cuối cùng sẽ giống như hình ảnh dưới đây.

Phương pháp 5 :Sử dụng Power Query

Phải thừa nhận rằng, Power Query là một tính năng bị bỏ qua của Excel, mặc dù nó có khả năng tổ chức và phân tích dữ liệu nhanh chóng và hiệu quả. Trong phần sau, chúng ta sẽ thảo luận về cách chúng ta có thể chuyển đổi văn bản thành các cột có nhiều dấu phân cách chỉ với một vài cú nhấp chuột. Vì vậy, hãy xem nó hoạt động.

📌 Các bước :

• Đầu tiên và quan trọng nhất, hãy chuyển đến B4 ô>> nhấn phím tắt CTRL + T để chèn Bảng Excel >> nhấn OK .

• Tiếp theo, chuyển đến Dữ liệu >> nhấp vào tab Từ Bảng / Phạm vi tùy chọn.

Không lâu sau, Power Query Editor xuất hiện.

• Từ thời điểm này, hãy nhấn vào Cột Tách thả xuống>> chọn By Delimiter tùy chọn.

• Sau đó, chọn Dấu chấm phẩy tùy chọn>> chèn dấu kiểm trên Mỗi lần xuất hiện dấu phân cách tùy chọn>> nhấn vào OK nút.

• Sau đó, Nhấp đúp vào các tiêu đề cột để đổi tên chúng>> nhấn nút Đóng &tải để thoát khỏi Power Query cửa sổ.

Cuối cùng, hoàn thành tất cả các bước sẽ mang lại kết quả như sau.

Đọ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

Phương pháp 6 :Áp dụng mã VBA

Nếu bạn thường xuyên cần chuyển đổi văn bản thành các cột có nhiều dấu phân cách trong Excel, thì bạn có thể xem xét Mã VBA phía dưới. Thật đơn giản và dễ dàng, chỉ cần làm theo.

📌 Các bước :

• Để bắt đầu, hãy điều hướng đến Nhà phát triển >> nhấp vào tab Visual Basic nút.

Ngay lập tức, Visual Basic Editor mở trong một cửa sổ mới.

• Tiếp theo, đi tới Chèn tab>> chọn Mô-đun .

Bây giờ, để bạn dễ tham khảo, bạn có thể sao chép mã từ đây và dán vào cửa sổ như hình dưới đây.

Sub Separate_Text_String()

Dim Arr() As String, _
cnt As Long, _
j As Variant

For k = 5 To 13
 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

⚡ Phân tích mã:

Ở đây, tôi sẽ giải thích về VBA mã được sử dụng để chuyển đổi văn bản thành các cột có nhiều dấu phân cách. Trong trường hợp này, mã được chia thành 2 bước.

• Trong phần đầu tiên, quy trình con được đặt tên, ở đây là Separate_Text_String () .
• 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 chấm phẩy.
• Bây giờ, trong mã, câu lệnh “For k =5 To 13” đạ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 13 .
• Sau đó, “;” trong “Arr =Split (Cells (k, 2),“; ”)” là dấu phân cách mà bạn có thể thay đổi thành dấu 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) .

• Sau đó, nhấn nút Chạy hoặc nút F5 trên bàn phím của bạn.

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

Chúng tôi đã cung cấp một Thực hành phần bên phải của mỗi tờ để bạn có thể tự luyện tập. Hãy đảm bảo bạn tự làm.

Kết luận

Tóm lại, bài viết này chỉ ra 6 cách hiệu quả để chuyển đổi văn bản thành cột có nhiều dấu phân cách trong Excel. Vì vậy, hãy đọc kỹ toàn bộ bài viết và tải về sách bài tập miễn phí để thực hành. Bây giờ, chúng tôi hy vọng bạn thấy bài viết này hữu ích và nếu bạn có thêm bất kỳ thắc mắc hoặc đề xuất nào, vui lòng bình luận tại đây. Cuối cùng, hãy truy cập ExcelDemy cho nhiều bài viết như thế này.

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 mà không cần ghi đè trong Excel