Hướng dẫn này sẽ chỉ cho bạn cách tách một ô bằng dấu phân cách với sự trợ giúp của công thức trong Excel. Dấu phân tách là một ký tự phân tách các phần dữ liệu trong chuỗi văn bản. Trong bài viết này, chúng tôi sẽ trình bày các cách khác nhau để tách các ô bằng dấu phân cách bằng cách sử dụng các công thức trong excel.
Trước khi bắt đầu phiên, hãy tìm hiểu về sổ làm việc mẫu của ngày hôm nay.
Cơ sở của ví dụ của chúng tôi sẽ là dữ liệu liên quan đến sinh viên ( Tên , ID , Khóa học , Thành phố ). Sử dụng những dữ liệu này, chúng tôi sẽ hiển thị các phương pháp khác nhau hoạt động trong các trường hợp khác nhau.
Ví dụ về tất cả các phương pháp sẽ được lưu trữ trong các trang tính riêng biệt.
Sách bài tập Thực hành
Bạn có thể tải xuống sổ làm việc từ liên kết bên dưới.
8 cách khác nhau để chia ô theo dấu phân cách bằng công thức trong Excel
Bạn có thể cần chia ô trong Excel trong một số trường hợp. Điều này có thể xảy ra khi ai đó sao chép thông tin từ internet, cơ sở dữ liệu hoặc đồng nghiệp. Nếu bạn có toàn bộ tên và muốn tách chúng thành họ và tên, đó là một ví dụ đơn giản về thời điểm bạn cần tách các ô trong Excel.
1. Kết hợp các hàm chuỗi trong Excel với hàm TÌM KIẾM để phân tách bằng dấu gạch ngang / dấu gạch nối văn bản được phân tách
Điều đầu tiên bạn cần làm để tách bằng dấu phân tách là xác định vị trí của chính dấu phân cách. Khi bạn xác định được dấu phân cách, bạn có thể dễ dàng tách từ hai bên của dấu phân cách. Chúng tôi sẽ sử dụng chức năng TÌM KIẾM để định vị dấu phân cách, sau đó chúng tôi sẽ trích xuất các giá trị từ văn bản bằng cách sử dụng LEFT , MID hoặc RIGHT chức năng.
1.1. Tích hợp các chức năng TRÁI và TÌM KIẾM
Hãy bắt đầu nào. Kể từ LEFT hàm có hai tham số, văn bản và số ký tự. Chúng tôi sẽ chèn văn bản khi chúng tôi biết giá trị văn bản của mình. Đối với số ký tự, chúng tôi sẽ sử dụng TÌM KIẾM chức năng.
CÁC BƯỚC:
- Trước tiên, hãy chọn ô và đặt công thức vào ô đó.
=LEFT(B5, SEARCH("-",B5,1)-1) - Hơn nữa, nhấn Enter từ bàn phím của bạn.
- Kéo Tay cầm điền vào biểu tượng xuống để sao chép công thức trên phạm vi. Hoặc để Tự động điền phạm vi, nhấp đúp trên cộng ( + ) biểu tượng.
- Cuối cùng, chúng ta có thể thấy kết quả.
🔎 Công thức hoạt động như thế nào?
Trong ví dụ, dấu phân cách của chúng ta là dấu gạch nối ‘ - '. TÌM KIẾM hàm sẽ cung cấp cho chúng ta vị trí của dấu gạch nối. Bây giờ, chúng ta không cần chính dấu gạch nối, chúng ta cần trích xuất nó trước dấu gạch nối.
1.2. Hợp nhất các hàm MID &SEARCH
Bây giờ, hãy viết cho giá trị trung bình. Đối với điều này, chúng tôi sẽ sử dụng MID & TÌM KIẾM chức năng. Hãy làm theo các quy trình bên dưới.
CÁC BƯỚC:
- Để bắt đầu, hãy chọn ô và nhập công thức sau.
=MID(B5, SEARCH("-",B5) + 1, SEARCH("-",B5,SEARCH("-",B5)+1) - SEARCH("-",B5) - 1) - Nhấn Enter .
- Để sao chép công thức trên phạm vi, hãy kéo Xử lý điền biểu tượng hướng xuống. Ngoài ra, bạn có thể nhấp đúp vào phần bổ sung ( + ) đăng nhập vào Tự động điền phạm vi.
- Cuối cùng, bạn có thể thấy tất cả các giá trị giữa giờ đã được phân tách.
🔎 Công thức hoạt động như thế nào?
Vị trí của một chuỗi văn bản bên trong chuỗi văn bản khác được trả về bởi TÌM KIẾM hàm số. Nó sẽ bắt đầu từ ký tự bên cạnh dấu gạch nối. Dựa trên số ký tự chúng tôi cung cấp, MID lấy một số ký tự nhất định từ một chuỗi văn bản, bắt đầu từ vị trí bạn chỉ định.
1.3. Các hàm RIGHT, LEN &SEARCH kết hợp
Bây giờ, để tách ô cuối cùng, chúng tôi sẽ sử dụng kết hợp RIGHT , LEN và TÌM KIẾM chức năng. Hãy xem các bước dưới đây để chia ô theo dấu phân cách bằng cách sử dụng kết hợp công thức.
CÁC BƯỚC:
- Ở vị trí đầu tiên, hãy chọn ô và chèn công thức sau vào ô đó.
=RIGHT(B5,LEN(B5) - SEARCH("-", B5, SEARCH("-", B5) + 1)) - Nhấn phím Enter trên bàn phím của bạn một lần nữa.
- Sau đó, kéo Fill Handle biểu tượng để sao chép công thức trên phạm vi. Hoặc nhấp đúp trên cộng ( + ) dấu hiệu. Điều này cũng trùng lặp công thức.
- Do đó, giá trị cuối cùng sẽ được chia bởi dấu phân cách.
🔎 Công thức hoạt động như thế nào?
Đây, LEN hàm trả về tổng độ dài của chuỗi, từ đó chúng ta trừ đi vị trí của dấu gạch ngang cuối cùng. TÌM KIẾM hàm sẽ cung cấp cho chúng ta vị trí của dấu gạch nối. Sau đó, sự khác biệt là số ký tự sau dấu gạch ngang cuối cùng và RIGHT chức năng trích xuất chúng.
LƯU Ý: Bạn có thể chia các cột theo bất kỳ ký tự nào khác theo cách tương tự. Tất cả những gì bạn cần làm là thay thế ‘ - 'Với dấu phân cách bắt buộc của bạn.Đọc thêm: Công thức Excel để chia chuỗi bằng dấu phẩy (5 ví dụ)
2. Hợp nhất các công thức để tách văn bản theo ngắt dòng
Để chia chuỗi theo ngắt dòng, chúng ta sẽ sử dụng một công thức tương tự như phần trước. Một chức năng bổ sung mà chúng tôi cần thêm vào các công thức trước đó của chúng tôi. Chức năng là CHAR .
2.1. Kết hợp các chức năng TRÁI, TÌM KIẾM &CHAR
CHAR này hàm sẽ cung cấp ký tự ngắt dòng. Để nhận giá trị đầu tiên và tách nó khỏi ô, chúng tôi sẽ sử dụng LEFT , TÌM KIẾM và CHAR chức năng. Hãy xem xét các thủ tục cho việc này.
CÁC BƯỚC:
- Tương tự như các phương pháp trước đó, trước tiên, hãy chọn bất kỳ ô nào và đặt công thức sau để trích xuất giá trị cao nhất.
=LEFT(B5, SEARCH(CHAR(10),B5,1)-1) - Nhấn vào Enter để xem kết quả.
- Hơn nữa, bằng cách kéo dấu cộng, bạn có thể sao chép công thức và nhận kết quả cho phạm vi ô.
🔎 Công thức hoạt động như thế nào?
10 là ASCII mã cho dòng. Chúng tôi đang cung cấp 10 trong CHAR để tìm kiếm ngắt dòng. Một ký tự được xác định bởi một số được trả về. Hơn nữa, nó tìm kiếm sự phá vỡ. Sau đó, nó trả về giá trị cao nhất.
2.2. Thêm các chức năng MID, SEARCH và CHAR cùng nhau
Để tách giá trị giữa, hãy xem các bước dưới đây.
CÁC BƯỚC:
- Tương tự như các phương pháp khác, trước tiên hãy chọn ô bất kỳ và nhập công thức sau để trích xuất giá trị cao nhất.
=MID(B5, SEARCH(CHAR(10),B5) + 1, SEARCH(CHAR(10),B5, SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) - 1) - Để xem kết quả, nhấn Enter chìa khóa.
- Ngoài ra, bạn có thể sao chép công thức và nhận kết quả cho phạm vi ô được chỉ định bằng cách kéo dấu cộng ký tên.
2.3. Tham gia các chức năng RIGHT, LEN, CHAR và SEARCH
Bây giờ đối với phía bên phải của văn bản, công thức của chúng tôi sẽ là sự kết hợp của RIGHT , LEN , CHAR và TÌM KIẾM chức năng. Sử dụng công thức thích hợp cho các giá trị còn lại. Vì vậy, để tách giá trị dưới cùng, hãy làm theo hướng dẫn.
CÁC BƯỚC:
- Giống như các kỹ thuật trước đó, hãy chọn ô và nhập công thức sau để trích xuất giá trị dưới cùng.
=RIGHT(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10), B5) + 1)) - Nhấn Enter từ bàn phím.
- Cuối cùng, bạn có thể sao chép công thức và truy xuất câu trả lời cho phạm vi ô đã chỉ định bằng cách kéo dấu cộng.
Đọc thêm: Cách tách ô trong Excel (5 thủ thuật dễ dàng)
3. Chia ô theo mẫu chuỗi văn bản &số trong Excel
Trong phần này, chúng ta sẽ thấy cách tách một văn bản có một chuỗi ký tự theo sau là một số. Để đơn giản, chúng tôi đã mang đến một số thay đổi cho trang tính của mình (Đừng lo lắng, tất cả các trang tính sẽ nằm trong sổ làm việc). Trong ví dụ của chúng tôi, chúng tôi có Tên sinh viên và ID cùng nhau trong một cột và chia chúng thành hai cột khác nhau.
3.1. Kết hợp Các hàm RIGHT, SUM, LEN và SUBSTITUTE
Trong SUBSTITUTE chúng tôi đang thay thế các số bằng khoảng trắng và đếm chúng bằng LEN . Để tách văn bản theo sau một chuỗi định dạng số, trước tiên chúng ta cần tìm ra số đó, sau đó nhờ sự trợ giúp của số được trích xuất đó, chúng ta có thể trích xuất văn bản.
CÁC BƯỚC:
- Đầu tiên, hãy chọn ô mà chúng tôi muốn đặt kết quả. Trong trường hợp của chúng tôi, chúng tôi sẽ chọn ô C5 .
- Sau đó, chèn công thức vào ô đó.
=RIGHT(B5,SUM(LEN(B5) -LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"},"")))) - Nhấn vào Enter chìa khóa.
- Bạn cũng có thể sao chép một công thức và truy xuất câu trả lời cho một dải ô bằng cách kéo dấu cộng.
🔎 Công thức hoạt động như thế nào?
Để trích xuất các số, chúng tôi cần tìm kiếm mọi số có thể từ 0 đến 9 trong chuỗi của chúng tôi. Sau đó, lấy tổng số và trả về số ký tự từ cuối chuỗi.
3.2. Tích hợp các hàm LEFT &LEN
Để trích xuất giá trị văn bản, bây giờ chúng ta cần sử dụng LEFT và trong trình giữ chỗ cho số lượng ký tự để cung cấp tổng độ dài của độ dài ô của các chữ số trong đó. Và chúng tôi nhận được các chữ số từ ô D5 , khi chúng tôi tách ID trong phương pháp trước.
CÁC BƯỚC:
- Đầu tiên, hãy chọn một ô cụ thể và nhập công thức vào đó.
=LEFT(B5,LEN(B5)-LEN(D5)) - Nhấn Enter .
- Bằng cách kéo dấu cộng, bạn có thể sao chép một công thức và nhận kết quả cho một nhóm ô.
Đọc thêm: Excel VBA:Chia chuỗi theo số ký tự (2 phương pháp dễ dàng)
4. Ngắt ô theo số &mẫu chuỗi văn bản sử dụng công thức
Nếu bạn đã hiểu phương pháp tách một ‘ văn bản + số ‘, Thì hy vọng rằng bạn đã bắt đầu hình dung ra một cách để tách một chuỗi số theo sau bằng định dạng văn bản. Cách tiếp cận sẽ giống như trước đó, chỉ cần một thay đổi là bạn sẽ nhận thấy. Bây giờ, số ở bên trái văn bản của chúng tôi, vì vậy chúng tôi cần sử dụng LEFT để tìm nạp số và đối với văn bản ký tự, chúng tôi sẽ sử dụng RIGHT chức năng.
4.1. Hợp nhất các hàm LEFT, SUM, LEN và SUBSTITUTE
Để chia ô theo số và mẫu chuỗi văn bản cho giá trị cao nhất, chúng ta cần hợp nhất LEFT , SUM , LEN, và SUBSTITUTE chức năng.
CÁC BƯỚC:
- Đầu tiên, hãy chọn ô cụ thể ngay từ đầu và nhập công thức vào đó.
=LEFT(B5, SUM(LEN(B5) -LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"}, "")))) - Nhấn vào Enter chìa khóa.
- Ngoài ra, bằng cách kéo biểu tượng bổ sung, bạn có thể sao chép một công thức và nhận kết quả cho một nhóm ô.
4.2. Hàm RIGHT &LEN kết hợp
Chúng ta cần kết hợp RIGHT và LEN chức năng chia ô theo số và mẫu chuỗi văn bản cho giá trị cuối cùng.
CÁC BƯỚC:
- Để bắt đầu, hãy chọn ô cụ thể và nhập công thức vào đó.
=RIGHT(B5,LEN(B5)-LEN(C5)) - Nhấn phím Enter nút.
- Ngoài ra, bạn có thể sao chép một công thức và nhận được câu trả lời cho một tập hợp các ô bằng cách kéo dấu cộng.
5. Tách ngày khỏi ô bằng cách kết hợp các hàm RIGHT, LEN, FIND và SUBSTITUTE
Để tách ngày khỏi văn bản, bạn có thể sử dụng tổ hợp phím RIGHT , LEN , TÌM HIỂU và SUBSTITUTE chức năng.
CÁC BƯỚC:
- Chọn ô mong muốn rồi nhập công thức vào đó.
=RIGHT(B5,LEN(B5)-FIND(" ",SUBSTITUTE(B5," "," ",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-2))) - Hơn nữa, nhấn Enter chìa khóa.
- Bạn cũng có thể sao chép một công thức và lấy kết quả cho một tập hợp các ô bằng cách kéo biểu tượng bổ sung.
🔎 Công thức hoạt động như thế nào?
Vì giá trị ngày ở cuối chuỗi nên chúng tôi đã duyệt qua một số trường hợp để tháng, ngày và năm đó có thể được tóm tắt. Nếu giá trị mục tiêu của bạn yêu cầu nhiều văn bản hơn để thúc đẩy, bạn có thể trích xuất chúng bằng cách thay đổi số lượng phiên bản.
LƯU Ý: Công thức này sẽ chỉ hữu ích khi bạn có ngày ở cuối chuỗi văn bản của mình.Đọc thêm: Excel VBA:Chia chuỗi thành các ô (4 ứng dụng hữu ích)
6. Kết hợp các hàm FILTERXML &SUBSTITUTE để tách ô
Sử dụng xpath được cung cấp, hàm FILTERXML trích xuất dữ liệu cụ thể từ XML các tài liệu. Chúng tôi có thể kết hợp FILTERXML và SUBSTITUTE có chức năng ngăn cách các ô. Hãy chia ô bằng dấu phân cách bằng công thức trong Excel.
CÁC BƯỚC:
- Trước hết, hãy chọn ô mong muốn và nhập công thức vào đó.
=FILTERXML("<t><s>"&SUBSTITUTE(B5,",","</s><s>")&"</s></t>","//s[2]") - Sau đó, nhấn Enter để xem kết quả.
- Cuối cùng, bằng cách kéo dấu cộng, bạn có thể sao chép một công thức và nhận được kết quả cho một tập hợp các ô.
🔎 Công thức hoạt động như thế nào?
Đây, SUBSTITUTE là thay thế văn bản cụ thể trong một chuỗi văn bản. Sau đó, FILTERXML của Excel cho phép bạn lấy dữ liệu từ một XML tệp.
7. Áp dụng hàm TEXTSPLIT để ngắt các ô theo dấu phân cách
Chúng tôi sử dụng chức năng TEXTSPLIT trong đó các cột và hàng được sử dụng làm dấu phân cách để phân chia chuỗi văn bản. Bạn có thể chia nó theo hàng hoặc theo cột. Đây là cách ngắn nhất và đơn giản nhất để chia bất kỳ ô nào bằng dấu phân cách. Để chia ô bằng dấu phân cách bằng công thức trong Excel, hãy làm theo các bước dưới đây.
CÁC BƯỚC:
- Chọn ô mà bạn muốn xem kết quả và đặt công thức ở đó.
=TEXTSPLIT(B5,",") - Sau đó, nhấn Enter .
- Hơn nữa, bạn có thể sao chép một công thức và lấy kết quả cho một tập hợp các ô bằng cách kéo dấu cộng.
8. Tách các ô bằng cách kết hợp các hàm TRIM, MID, SUBSTITUTE, REPT &LEN
Một kết hợp khác của công thức là TRIM , MID , SUBSTITUTE , REPT và LEN , với điều này, chúng ta có thể chia các ô bằng dấu phân cách bằng công thức trong Excel.
CÁC BƯỚC:
- Đặt công thức vào ô mà bạn muốn xem kết quả sau khi chọn.
=TRIM(MID(SUBSTITUTE($B5,"|",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5))) - Sau đó, nhấn Enter .
- Bằng cách trượt dấu cộng, bạn cũng có thể sao chép một công thức và nhận kết quả cho một nhóm ô.
🔎 Công thức hoạt động như thế nào?
Đây, LEN trả về độ dài của một chuỗi văn bản tính bằng ký tự. Sau đó, SUBSTITUTE hàm thay thế văn bản xuất hiện tại một vị trí nhất định trong chuỗi văn bản. Sau đó, MID hàm cung cấp một số từ nhất định từ một chuỗi văn bản, bắt đầu từ vị trí bạn chỉ định. Cuối cùng, TRIM hàm xóa tất cả khoảng trắng khỏi văn bản, ngoại trừ dấu cách kép sau chữ.
Cách chia ô bằng dấu phân cách bằng lệnh trong Excel
Excel có một tính năng bên trong nó để chia ô . Bạn sẽ tìm thấy nó bên trong các tùy chọn của Tab Dữ liệu . Để sử dụng tính năng chia ô bằng dấu phân cách bằng công thức trong Excel, bạn cần làm theo hướng dẫn bên dưới.
CÁC BƯỚC:
- Đầu tiên, hãy chọn ô hoặc cột (thường thì bạn cần chọn toàn bộ một cột).
- Sau đó, khám phá Dữ liệu tab . Tại đây trong Công cụ dữ liệu , bạn sẽ tìm thấy một tùy chọn có tên là Văn bản thành cột .
- Sau đó, nhấp vào đó.
- Một hộp thoại sẽ xuất hiện trước mặt bạn. Thông thường, bạn sẽ cần phải chia các ô bằng dấu phân cách, vì vậy hãy chọn mục Được phân cách và nhấp vào Tiếp theo .
- Sau đó, bạn sẽ tìm thấy một giao diện chứa một số dấu phân cách.
- Ngoài ra, hãy chọn dấu hiệu ưa thích của bạn hoặc bạn cũng có thể nhập dấu phân cách của riêng mình.
- Sau khi chọn dấu phân cách, bạn sẽ thấy kết quả ở cuối hộp.
- Hơn nữa, nhấp vào Tiếp theo sau đó.
- Trong ví dụ này, chúng tôi đã chọn một dấu phẩy ở đây, vì các giá trị của chúng tôi được phân tách bằng dấu phẩy.
- Sau khi nhấp vào Tiếp theo bạn sẽ tìm thấy các tùy chọn để chọn loại giá trị của mình và nhấp vào Hoàn tất . Bạn sẽ nhận được một giá trị riêng.
- Hiện tại, chúng tôi đang giữ điều này là Chung ( theo mặc định) . Định dạng hiển thị trong hình ảnh dưới đây được tạo ra sau khi tạo một số hình dạng.
Kết luận
Các phương pháp trên sẽ hỗ trợ bạn chia ô bằng dấu phân cách bằng công thức trong Excel. Đó là tất cả cho ngày hôm nay. Với các phương pháp trên, bạn có thể chia ô bằng dấu phân cách bằng cách sử dụng công thức trong Excel. Chúng tôi đã thử liệt kê một số cách để tách ô bằng dấu phân cách bằng công thức. Hy vọng điều này sẽ hữu ích. Vui lòng bình luận nếu bất cứ điều gì có vẻ khó hiểu. Bạn cũng có thể cho chúng tôi biết bất kỳ cách nào khác trong trường hợp chúng tôi bỏ sót nó tại đây.
Bài đọc thêm
- Cách tách một ô thành hai trong Excel (5 phương pháp hữu ích)
- Cách chia đôi một ô trong Excel (theo đường chéo &theo chiều ngang)
- VBA để chia chuỗi thành nhiều cột trong Excel (2 cách)
- VBA trong Excel:Chia chuỗi theo ký tự (6 ví dụ hữu ích)
- Cách tạo hai dòng trong một ô trong Excel (4 phương pháp)