Làm việc trên các loại văn bản khác nhau là rất phổ biến trong excel. Nhưng đôi khi chúng ta cần tách chúng ra để hiểu rõ hơn. Trong bài viết này, chúng ta sẽ tìm hiểu về cách tách văn bản trong Excel với 8 ứng dụng.
Nhận tệp mẫu này và tự thử.
8 Ứng dụng Hữu ích để Tách Văn bản trong Excel
Để minh họa, đây là tập dữ liệu với thông tin của Tên &Địa chỉ gồm 5 người sống ở các quốc gia khác nhau.
Bây giờ, hãy làm theo các ví dụ bên dưới để tách văn bản trong tập dữ liệu này.
1. Sử dụng trình hướng dẫn cột để tách văn bản trong Excel
Phương pháp đầu tiên này sẽ hướng dẫn bạn cách tách văn bản với sự trợ giúp của Trình hướng dẫn chuyển văn bản thành cột xác định Dấu phân cách . Hãy kiểm tra quy trình bên dưới:
- Ban đầu, hãy chuyển đến Dữ liệu và chọn Văn bản thành cột trong Dữ liệu Nhóm công cụ.
- Sau đó, tùy chọn Được phân cách trong bước đầu tiên của Trình hướng dẫn chuyển văn bản thành cột cửa sổ và nhấn Tiếp theo .
- Trong bước thứ hai, hãy chọn Dấu phân cách dưới dạng Dấu phẩy và nhấn Tiếp theo .
- Trong bước cuối cùng, hãy cung cấp Đích đến dưới dạng ô C5 và nhấn vào Kết thúc .
- Vậy là xong, chúng tôi có văn bản được chia tách của chúng tôi như hình ảnh sau:
2. Tách văn bản bằng Flash Fill trong Excel
Tô flash là một bổ sung tốt cho Trình hướng dẫn cột để tách văn bản trong excel. Sau đây là quy trình:
- Trước tiên, hãy nhập phần đầu tiên của văn bản vào ô C5 .
- Sau đó, chuyển đến Dữ liệu và chọn Flash Fill trong Công cụ dữ liệu nhóm.
- Theo đó, bạn sẽ thấy dải ô C6:C9 được tự động điền vào văn bản phần đầu tiên từ mỗi nguồn của nó.
- Thực hiện theo quy trình tương tự và bạn sẽ nhận được kết quả cuối cùng.
3. Kết hợp các hàm LEFT, RIGHT, FIND &LEN để tách văn bản
Một phương pháp hữu ích khác để tách văn bản là kết hợp LEFT , RIGHT , TÌM HIỂU & Hàm LEN trong excel. Hãy xem nó hoạt động như thế nào.
- Trước tiên, hãy chèn công thức này vào ô C5 .
=LEFT(B5,FIND(",",B5)-1)
- Sau đó, nhấn Enter .
- Do đó, hãy sử dụng Tự động điền công cụ và bạn sẽ có kết quả bên dưới:
Ở đây, chúng tôi đã áp dụng LEFT hàm để tìm nạp dữ liệu từ phía ngoài cùng bên trái của ô. Sau đây, chúng tôi đã sử dụng FIND hàm để tìm kiếm vị trí văn bản đó trong ô B5 .
- Bây giờ, hãy chèn công thức này vào ô D5 .
=RIGHT(B5,LEN(B5)-FIND(",",B5))
- Tiếp theo, nhấn Enter .
- Tương tự, sử dụng Tự động điền công cụ để nhận văn bản được tách trong phạm vi ô D5:D9 .
Ở đây, chúng tôi đã áp dụng RIGHT chức năng trích xuất văn bản từ phần bên phải của ô B5 . Sau đó, chúng tôi sử dụng LEN hàm xác định độ dài của chuỗi văn bản. Cuối cùng, áp dụng FIND hàm để tìm kiếm chuỗi văn bản cụ thể.
4. Chèn hàm TRANSPOSE để phân tách
Chức năng TRANSPOSE cũng rất hữu ích cho việc tách văn bản. Sau đây là quy trình:
- Trước hết, hãy chèn công thức này vào ô C5 .
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","") & "","//s"))
- Thứ hai, nhấn Enter .
- Sau đây, bạn sẽ thấy rằng văn bản được tự động chia thành ô C5 , D5 và E5 .
Ở đây, chúng tôi đã sử dụng TRANSPOSE hàm tạo mảng để tách văn bản. Sau đó, chúng tôi chèn hàm FILTERXML để trích xuất các mục riêng lẻ từ ô B5 . Gần đây, chúng tôi đã sử dụng hàm SUBSTITUTE để thay thế văn bản dựa trên tập dữ liệu.
- Cuối cùng, sử dụng Xử lý điền công cụ cho các ô sau.
=FILTERXML(""&SUBSTITUTE(B3,","")&"", "//s")
5. Áp dụng Excel VBA để tách văn bản
Excel VBA là một giải pháp tuyệt vời để tách văn bản trong excel. Hãy kiểm tra các bước đơn giản bên dưới:
- Ban đầu, hãy chuyển đến Nhà phát triển và chọn Visual Basic từ Mã nhóm.
- Sau đó, chọn Mô-đun từ Chèn phần.
- Sau đó, hãy chèn mã này vào trang trống.
Sub SplitCellText()
Dim MyAry() As String, Cnt As Long, j As Variant
For n = 5 To 9
MyAry = Split(Cells(n, 2), ",")
Cnt = 3
For Each j In MyAry
Cells(n, Cnt) = j
Cnt = Cnt + 1
Next j
Next n
End Sub
- Sau đó, nhấp vào Chạy phụ hoặc nhấn F5 trên bàn phím của bạn.
- Tiếp theo, nhấp vào Chạy trong Macro cửa sổ.
- Cuối cùng, bạn đã thực hiện thành công việc chia nhỏ văn bản trong excel.
6. Sử dụng Power Query để tách văn bản trong Excel
Một phương pháp hữu ích khác để tách văn bản là sử dụng Power Query trong excel. Công cụ này giúp nhập và kết nối với sổ làm việc bên ngoài và định hình lại chúng theo sở thích. Hãy cẩn thận làm theo quy trình bên dưới:
- Trước tiên, hãy chuyển đến Dữ liệu và chọn Nhận dữ liệu .
- Sau đó, chọn Từ tệp rồi đến Từ Sổ làm việc Excel trong menu ngữ cảnh.
- Tiếp theo, chọn Power Query trang tính trong danh sách Bộ điều hướng .
- Sau đó, nhấn Chuyển đổi dữ liệu .
- Sau đó, trong cửa sổ, chuyển đến Chuyển đổi và nhấp vào Cột phân tách .
- Sau đó, chọn Theo Dấu phân cách từ trình đơn thả xuống .
- Sau đây, bạn sẽ được chuyển hướng đến Cột Phân tách theo Dấu phân cách hộp thoại.
- Tại đây, chọn dấu phân cách là Dấu phẩy .
- Cùng với nó, hãy chọn tùy chọn Mỗi lần xuất hiện dấu phân cách thành Tách lúc .
- Tiếp theo, nhấn OK .
- Cuối cùng, nhấn vào Đóng &Tải .
- Cuối cùng, bạn sẽ thấy một trang tính mới, nơi các bài kiểm tra được tách ra
- Sau đó, bạn có thể sửa đổi bảng này theo tùy chọn của mình.
7. Tách văn bản với các hàm TEXTBEFORE &TEXTAFTER
Trong phương pháp cuối cùng này, chúng tôi sẽ mô tả quy trình bằng TEXTBEFORE và chức năng TEXTAFTER trong một tập dữ liệu mới. Tại đây, nó hiển thị thông tin về tên đầy đủ của 5 người trong phạm vi ô B5:B9 . Hãy kiểm tra các bước bên dưới:
- Trước hết, hãy chèn công thức này vào ô C5 và nhấn Enter .
=TEXTBEFORE(B5," ")
Đây, TEXBEFORE hàm được sử dụng để trả về văn bản trong ô B5 trước bất kỳ dấu phân cách nào.
- Tiếp theo, áp dụng công thức này trong ô D5 và nhấn Enter .
=IFERROR(TEXTBEFORE(TEXTAFTER(B5," ")," ",-1),"")
Đây, hàm IFERROR được áp dụng để trả về một giá trị trong công thức chỉ định lỗi. Sau đây, chúng tôi đã sử dụng TEXTBEFORE và TEXTAFTER hàm để trả về văn bản trong ô B5 trước và sau bất kỳ dấu phân cách nào tương ứng.
- Cuối cùng, áp dụng công thức này trong ô E5 .
=TEXTAFTER(B5," ",-1)
Đây, TEXAFTER hàm được sử dụng để trả về văn bản trong ô B5 sau bất kỳ dấu phân cách nào.
- Bây giờ, bạn sẽ thấy đầu ra của ô B5 .
- Cuối cùng, sử dụng Xử lý điền công cụ và nhận đầu ra cuối cùng.
8. Tách văn bản khỏi ngắt dòng trong Excel
Trong phần này, chúng ta sẽ thảo luận về một khía cạnh mới của việc tách văn bản. Ở đây, chúng ta sẽ thấy quá trình tách văn bản từ ngắt dòng. Hãy kiểm tra quy trình bên dưới:
- Trước tiên, hãy chèn công thức này vào ô C5 .
=LEFT(B5, SEARCH(CHAR(10),B5,1)-1)
Ở đây, trước tiên, chúng tôi sử dụng LEFT hàm để trích xuất giá trị bên trái. Sau đó, áp dụng chức năng TÌM KIẾM để trả về vị trí dưới dạng một số trong chuỗi văn bản. Cuối cùng, chúng tôi đã sử dụng chức năng CHAR để trả về một ký tự được chỉ định bởi một số trong ô B5 .
- Sau đó, áp dụng công thức này trong ô D5 .
=MID(B5, SEARCH(CHAR(10),B5) + 1, SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) - 1)
Đây, chức năng MID được sử dụng để tìm nạp giá trị từ dòng giữa trong ô B5 . Sau đó, áp dụng TÌM KIẾM hàm để trả về vị trí trong chuỗi văn bản. Cuối cùng, chúng tôi đã sử dụng CHAR hàm để trả về một ký tự được chỉ định bởi một số.
- Cuối cùng, hãy chèn cái này vào ô E5 .
=RIGHT(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10),B5) + 1))
Ở đây, trước tiên, chúng tôi sử dụng RIGHT hàm để trích xuất giá trị bên phải. Sau đó, áp dụng TÌM KIẾM hàm để trả về vị trí dưới dạng một số trong chuỗi văn bản và LEN hàm xác định độ dài của chuỗi đó. Cuối cùng, chúng tôi đã sử dụng CHAR hàm để trả về một ký tự được chỉ định bởi một số trong ô B5 .
- Cho đến nay, đây là kết quả so với ô B5 .
- Thực hiện theo quy trình tương tự và bạn sẽ thấy tập hợp văn bản tách cuối cùng.
Cách tách số khỏi văn bản trong Excel
Hình ảnh sau đây cho thấy một trang tính với một số số và văn bản trong một cột duy nhất. Mục tiêu của chúng tôi là trích xuất phần số từ mỗi ô và đặt những số này vào một cột riêng biệt bằng cách sử dụng Flash Fill công cụ.
- Đầu tiên, hãy chèn số ô B5 bên trong ô C5 .
- Sau đó, nhấp vào ô tiếp theo ngay lập tức.
- Sau đó, chuyển đến Dữ liệu và chọn Flash Fill .
- Vậy là xong, bạn sẽ nhận được tất cả các con số cùng một lúc.
Kết luận
Đến đây chúng ta đã cùng nhau bàn về quy trình tách văn bản trong excel với 8 ứng dụng thực tế. Hãy thử chúng và cho chúng tôi biết phản hồi của bạn. Theo dõi ExcelDemy để có thêm blog excel.