Đây là tổng quan.
Bộ dữ liệu thể hiện hồ sơ sinh viên.
Phương pháp 1 – Sử dụng tính năng Flash Fill để tách chuỗi theo độ dài
Mã sinh viên (Student Id) chứa tên Trường đại học, Năm, Lớp và Số thứ tự.
Trích xuất dữ liệu bằng cách tách Mã sinh viên theo độ dài ký tự 3, 4 và 3.
Bước 1:
- Chọn ô đầu ra C5.
- Nhập 3 ký tự đầu tiên.
Bước 2:
- Nhấn ENTER.
- Ở ô tiếp theo C6, đi đến tab Dữ liệu (Data) >> Công cụ dữ liệu (Data Tools) >> Flash Fill.
Kết quả:
Bạn sẽ nhận được tên Trường đại học trong cột Trường.
Bước 3:
- Chọn ô đầu ra D5.
- Nhập 4 ký tự giữa.
- Làm theo Bước 2.
Kết quả:
Bạn sẽ nhận được Năm trong cột Năm.
Bước 4:
- Chọn ô đầu ra E5.
- Nhập 3 ký tự cuối.
- Làm theo Bước 2.
Kết quả:
Bạn sẽ nhận được Lớp và Số thứ tự của mỗi sinh viên trong cột Lớp và Số thứ tự.
Đọc thêm: Cách tách văn bản trong Excel theo ký tự
Phương pháp 2 – Sử dụng tùy chọn Văn bản thành cột (Text to Columns)
Tách Mã sinh viên để lấy tên Trường đại học, Năm, Lớp và Số thứ tự của mỗi sinh viên.
Bước 1:
- Chọn Cột Mã sinh viên.
- Đi đến Dữ liệu (Data) >> Công cụ dữ liệu (Data Tools) >> Văn bản thành cột (Text to Columns).
Trong Phép chuyển đổi Văn bản thành cột (Convert Text to Columns Wizard):
- Chọn Chiều rộng cố định (Fixed width).
- Nhấp Tiếp theo (Next).
- Nhấp vào vị trí như hình dưới để tạo ngắt dòng. (Ở đây, sau tên Trường đại học và sau Năm).
- Chọn Tiếp theo (Next).
- Chọn Chung (General) làm Định dạng dữ liệu cột (Column data format).
- Nhập ô đầu ra $C$5 trong Đích (Destination).
- Nhấp Hoàn tất (Finish).
Kết quả:
Bạn sẽ nhận được dữ liệu tách trong ba cột: Trường, Năm, Lớp và Số thứ tự.
Đọc thêm: Cách tách văn bản trong Excel bằng công thức
Phương pháp 3 – Sử dụng dữ liệu hỗ trợ (Helper Data)
Tách Mã khóa học (Course No) theo độ dài ký tự 3 để lấy Môn học (Subject) và Mã khóa học (Course Id).
Bước 1:
- Chọn ô đầu ra D6.
- Nhập công thức sau.
=MID($C6,COLUMN()+D$4-COLUMN($D6)+1,3)
Phân tích công thức
COLUMN()+D$4-COLUMN($D6)+1 sẽ trả về số bắt đầu
COLUMN($D6) tạo ra số cột của ô này.
3 là số ký tự
- Nhấn ENTER.
- Kéo Cây điền (Fill Handle) xuống.
Kết quả:
Bạn sẽ nhận được các Môn học trong cột Môn học.
Bước 2:
- Chọn ô đầu ra D6.
- Nhập công thức sau.
=MID($C6,COLUMN()+E$4-COLUMN($E6)+3,3)
COLUMN()+E$4-COLUMN($E6)+3 sẽ trả về số bắt đầu
COLUMN($E6) tạo ra số cột của ô này.
3 là số ký tự.
- Nhấn ENTER.
- Kéo Cây điền (Fill Handle) xuống.
Kết quả:
Bạn sẽ thấy Mã khóa học trong cột Mã khóa học.
Đọc thêm: Cách tách văn bản theo số ký tự trong Excel
Phương pháp 4 – Sử dụng hàm LEFT và RIGHT
Tách Mã khóa học theo độ dài ký tự 3 mỗi lần, Môn học và Mã khóa học sẽ được tách riêng.
Bước 1:
- Chọn ô đầu ra C5.
- Nhập công thức sau.
C5 là văn bản
3 là số ký tự
- Nhấn ENTER.
- Kéo Cây điền (Fill Handle) xuống.
Bạn sẽ thấy các Môn học trong cột Môn học.
Bước 2:
- Chọn ô đầu ra E5.
- Nhập công thức sau.
C5 là văn bản
3 là số ký tự
- Nhấn ENTER.
- Kéo Cây điền (Fill Handle) xuống.
Bạn sẽ thấy Mã khóa học trong cột Mã khóa học.
Đọc thêm: Tách chuỗi theo ký tự trong Excel
Phương pháp 5 – Sử dụng hàm FIND để tách chuỗi với ký tự đặc biệt
Tách Email Ids bằng ký tự đặc biệt “@” và “.” .
Bước 1:
- Chọn ô đầu ra C5.
- Nhập công thức sau.
B5 là văn bản
FIND(“@”,B5)-1 là số ký tự
FIND sẽ cho vị trí của “@” . Sẽ trừ 1 và trả về số ký tự.
- Nhấn ENTER.
- Kéo Cây điền (Fill Handle) xuống.
Kết quả:
Bạn sẽ nhận được phần đầu của Email Id
Bước 2:
- Chọn ô đầu ra C5.
- Nhập công thức sau.
=MID(B5,FIND("@",B5),FIND(".",B5,FIND("@",B5)+1)-FIND("@",B5))
B5 là văn bản
FIND(“@”, B5) là số bắt đầu, là vị trí của ký tự “@”
FIND(“.”,B5,FIND(“@”,B5)+1)-FIND(“@”,B5) là số ký tự
Là sự khác biệt giữa ký tự “.” và ký tự “@”.
- Nhấn ENTER.
- Kéo Cây điền (Fill Handle) xuống.
Kết quả:
Bạn sẽ thấy phần giữa của Email Id này.
Bước 3:
- Chọn ô đầu ra C5.
- Nhập công thức sau.
=RIGHT(B5,LEN(B5)-FIND(".",B5,FIND("@",B5))+1)
B5 là văn bản
LEN(B5)-FIND(“.”,B5,FIND(“@”,B5))+1 là số ký tự
hàm LEN sẽ cho độ dài ký tự, và sẽ trừ đi vị trí của ký tự “.”. Sự khác biệt sẽ là số ký tự sau ký tự “.”.
- Nhấn ENTER.
- Kéo Cây điền (Fill Handle) xuống.
Kết quả:
Bạn sẽ nhận được phần cuối của Email Id.
Phương pháp 6 – Sử dụng hàm SEARCH để tách chuỗi với ký tự đặc biệt
Tách Email Ids bằng ký tự đặc biệt “@” và “.”
Bước 1:
- Chọn ô đầu ra C5.
- Nhập công thức sau.
=LEFT(B5,SEARCH("@",B5)-1)
Phân tích công thức
B5 là văn bản
SEARCH(“@”,B5)-1 là số ký tự
SEARCH sẽ cho vị trí ký tự “@”.
Sẽ trừ 1.
- Nhấn ENTER.
- Kéo Cây điền (Fill Handle) xuống.
Kết quả:
Bạn sẽ nhận được phần đầu của Email Id.
Bước 2:
- Chọn ô đầu ra D5.
- Nhập công thức sau.
=MID(B5,SEARCH("@",B5),SEARCH(".",B5,SEARCH("@",B5)+1)-SEARCH("@",B5))
Phân tích công thức
Ở đây, B5 là văn bản
SEARCH(“@”, B5) là số bắt đầu, là vị trí ký tự “@”
SEARCH(“.”,B5,SEARCH(“@”,B5)+1)-SEARCH(“@”,B5) là số ký tự
Là sự khác biệt giữa ký tự “.” và ký tự “@”
- Nhấn ENTER.
- Kéo Cây điền (Fill Handle) xuống.
Bạn sẽ nhận được phần giữa của Email Id.
Bước 3:
- Chọn ô đầu ra E5.
- Nhập công thức sau.
=RIGHT(B5,LEN(B5)-SEARCH(".",B5,SEARCH("@",B5))+1)
B5 là văn bản
LEN(B5)-SEARCH(“.”,B5,SEARCH(“@”,B5))+1 là số ký tự
Ở đây, hàm LEN sẽ cho độ dài ký tự. Sẽ trừ đi vị trí ký tự “.”, và sự khác biệt sẽ là số ký tự sau ký tự “.”.
- Nhấn ENTER.
- Kéo Cây điền (Fill Handle) xuống.
Bạn sẽ nhận được phần cuối của Email Id.
Phương pháp 7 – Tách chuỗi bằng ngắt dòng (Line Break)
Mã sinh viên (Student Id) và Tên sinh viên (Student Name) được phân tách bằng ngắt dòng. Để trích xuất Mã sinh viên và Tên sinh viên, tách chuỗi bằng ngắt dòng.
Bước 1:
- Chọn ô đầu ra C5.
- Nhập công thức sau.
=LEFT(B5,FIND(CHAR(10),B5))
B5 là văn bản
FIND(CHAR(10), B5) là số ký tự
FIND sẽ cho vị trí ngắt dòng và CHAR(10) dùng cho ngắt dòng.
- Nhấn ENTER.
- Kéo Cây điền (Fill Handle) xuống.
Kết quả:
Bạn sẽ nhận được Mã sinh viên trong cột Mã sinh viên.
Bước 2:
- Chọn ô đầu ra E5.
- Nhập công thức sau.
=RIGHT(B5,LEN(B5)-FIND(CHAR(10),B5)+1)
B5 là văn bản
LEN(B5)-FIND(CHAR(10), B5)+1 là số ký tự
Ở đây, hàm LEN sẽ cho độ dài ký tự. Sẽ trừ đi vị trí ngắt dòng và sự khác biệt sẽ là số ký tự sau ngắt dòng.
- Nhấn ENTER.
- Kéo Cây điền (Fill Handle) xuống.
Bạn sẽ nhận được Tên sinh viên trong cột Tên sinh viên.
Đọc thêm: Cách tách hai từ trong Excel
Phương pháp 8 – Tách chuỗi kết hợp số và văn bản
Điểm (Marks) và Xếp loại (Grade) hiển thị trong cùng một ô.
Bước 1:
- Chọn ô đầu ra D5.
- Nhập công thức sau.
=LEFT(C5, SUM(LEN(C5) - LEN(SUBSTITUTE(C5, {"0","1","2","3","4","5","6","7","8","9"}, ""))))
C5 là văn bản
SUBSTITUTE(C5, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, “”) sẽ thay thế bất kỳ số nào bằng Rỗng và hàm LEN sẽ trả về số giá trị số.
Sau đó sẽ trừ từ tổng độ dài ký tự và tổng của giá trị này sẽ là số ký tự.
- Nhấn ENTER.
- Kéo Cây điền (Fill Handle) xuống.
Bạn sẽ nhận được Điểm trong cột Điểm.
Bước 2:
- Chọn ô đầu ra E5.
- Nhập công thức sau.
=RIGHT(C5,LEN(C5)-LEN(D5)-1)
C5 là văn bản
LEN(C5)-LEN(D5)-1 sẽ cho độ dài ký tự.
- Nhấn ENTER.
- Kéo Cây điền (Fill Handle) và bạn sẽ thấy Xếp loại trong cột Xếp loại.
Phần thực hành
Thực hành tại đây.
Tải xuống tệp thực hành
Đọc thêm
- Cách tách văn bản trong Excel thành nhiều hàng
- Cách tách văn bản sau một từ cụ thể trong Excel
- Tách văn bản bằng khoảng trắng với công thức trong Excel
- Cách tách Họ và Tên trong Excel
<< Quay lại Tách văn bản | Tách trong Excel | Học Excel
Nhận miễn phí Bài tập Excel nâng cao có lời giải!