Đây là cái nhìn tổng quan.
Tập dữ liệu hiển thị hồ sơ của học sinh.
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 chứa Đại học tên, Năm, Phần và Cuộn.
Trích xuất dữ liệu bằng cách tách Id sinh viên : theo độ dài ký tự 3, theo độ dài 4 và theo độ dài ký tự 3.
Bước 1 :
- Chọn đầu ra Ô, C5.
- Nhập ba ký tự đầu tiên.
Bước 2 :
- Nhấn ENTER.
- Trong ô tiếp theo, C6 , đi tới Dữ liệu Tab>> Công cụ dữ liệu >>Tô flash .
Kết quả :
Bạn sẽ nhận được Đại học tên trong cột Đại học .
Bước 3 :
- Chọn đầu ra Ô, D5.
- Nhập bốn ký tự ở giữa.
- Thực hiện 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 ba ký tự cuối cùng.
- Thực hiện theo Bước 2 .
Kết quả :
Bạn sẽ nhận được Phần và cuộn của mỗi học sinh trong cột Phần và Điểm danh .
Đọ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 chuyển văn bản thành cột
Tách Mã sinh viên để có được Đại học tên, Năm, Phần và Cuộn của mỗi học sinh.
Bước 1 :
- Chọn cột Mã sinh viên .
- Đi tới Dữ liệu Tab>> Công cụ dữ liệu >>Chuyển văn bản thành cột
Trong Trình hướng dẫn chuyển đổi văn bản thành cột:
- Chọn Chiều rộng cố định .
- Nhấp vào Tiếp theo.
- Nhấp vào vị trí hiển thị bên dưới để tạo ngắt dòng. (Ở đây, sau Đại học tên và sau Năm)
- Chọn Tiếp theo.
- Chọn Chung dưới dạng Định dạng dữ liệu cột.
- Nhập ô đầu ra $C$5 ở Đích.
- Nhấp vào Hoàn tất.
Kết quả :
Bạn sẽ nhận được dữ liệu được chia thành ba cột:Đại học , Năm , Phần , và cuộn .
Đọ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 trợ giúp
Tách Mã khóa học theo độ dài ký tự 3 để lấy Chủ đề và Id khóa học .
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
CỘT($D6) tạo ra số cột của ô này.
3 là số ký tự
- Nhấn ENTER.
- Kéo xuống Tay cầm điền Công cụ.
Kết quả :
Bạn sẽ nhận được Chủ đề trong cột Chủ đề .
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
CỘT($E6) tạo ra số cột của ô này.
3 là số ký tự .
- Nhấn ENTER.
- Kéo xuống Tay cầm điền Công cụ.
Kết quả :
Bạn sẽ thấy Id khóa học trong cột Id 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, Chủ đề và Id khóa học sẽ bị chia cắt.
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 xuống Tay cầm điền Công cụ.
Bạn sẽ thấy Chủ đề trong cột Chủ đề .
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 xuống Tay cầm điền Công cụ.
Bạn sẽ thấy Id khóa học trong cột Id 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 có ký tự đặc biệt
Tách Id email bởi các 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
TÌM(“@”,B5)-1 là số ký tự
TÌM sẽ cung cấp vị trí “@” . Nó sẽ bị trừ đi 1 và trả về số ký tự.
- Nhấn ENTER.
- Kéo xuống Tay cầm điền Công cụ.
Kết quả :
Bạn sẽ nhận được phần đầu tiên của Id Email
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
TÌM(“@”, B5) là số bắt đầu , đó là vị trí của “@” nhân vật
TÌM(“.”,B5,TÌM(“@”,B5)+1)-TÌM(“@”,B5) là số ký tự
Đó là sự khác biệt giữa “.” ký tự và “@” nhân vật.
- Nhấn ENTER.
- Kéo xuống Tay cầm điền Công cụ.
Kết quả :
Bạn sẽ thấy phần giữa của Id Email 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 biết độ dài ký tự và nó sẽ bị trừ khỏi vị trí của “.” nhân vật. Sự khác biệt sẽ là số lượng ký tự sau “.” nhân vật.
- Nhấn ENTER.
- Kéo xuống Tay cầm điền Công cụ.
Kết quả :
Bạn sẽ nhận được phần cuối cùng của Id Email.
Phương pháp 6 – Sử dụng hàm TÌM KIẾM để tách chuỗi có ký tự đặc biệt
Tách Id email bởi các 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
TÌM KIẾM(“@”,B5)-1 là số ký tự
TÌM KIẾM sẽ cung cấp vị trí “@” nhân vật.
Nó sẽ bị trừ đi 1.
- Nhấn ENTER.
- Kéo xuống Tay cầm điền Công cụ.
Kết quả :
Bạn sẽ nhận được phần đầu tiên của Id Email .
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
TÌM KIẾM(“@”, B5) là số bắt đầu , đó là vị trí của “@” nhân vật
TÌM KIẾM(“.”,B5,TÌM KIẾM(“@”,B5)+1)-SEARCH(“@”,B5) là số ký tự
Đó là sự khác biệt giữa “.” ký tự và “@” nhân vật
- Nhấn ENTER.
- Kéo xuống Tay cầm điền Công cụ.
Bạn sẽ nhận được phần giữa của Id Email .
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ự. Nó sẽ bị trừ khỏi vị trí của “.” ký tự và sự khác biệt sẽ là số lượng ký tự sau “.” nhân vật.
- Nhấn ENTER.
- Kéo xuống Tay cầm điền Công cụ.
Bạn sẽ nhận được phần cuối cùng của Id Email .
Phương pháp 7 – Tách chuỗi bằng dấu ngắt dòng
Mã sinh viên và Tên sinh viên được phân tách bằng dấu ngắt dòng. Để trích xuất Id sinh viên và Tên sinh viên, chia chuỗi bằng dấu 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
TÌM(CHAR(10), B5) là số ký tự
TÌM sẽ cung cấp vị trí ngắt dòng và CHAR(10) được sử dụng để ngắt dòng.
- Nhấn ENTER.
- Kéo xuống Tay cầm điền Công cụ.
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)-TÌM(CHAR(10), B5)+1 là số ký tự
Ở đây, hàm LEN sẽ cho độ dài ký tự. Nó sẽ bị trừ khỏi vị trí ngắt dòng và sự khác biệt sẽ là số ký tự sau khi ngắt dòng.
- Nhấn ENTER.
- Kéo xuống Tay cầm điền Công cụ.
Bạn sẽ nhận được Tên sinh viên trong cột Tên học sinh .
Đọc thêm: Cách tách hai từ trong Excel
Phương pháp 8 – Tách một chuỗi bằng sự kết hợp giữa số và văn bản
Dấu và Cấp được 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
ĐĂNG KÝ(C5, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, “”) sẽ thay thế bất kỳ số nào bằng Trống và hàm LEN sẽ trả về số lượng giá trị số.
Sau đó, nó sẽ được trừ vào tổng chiều dài ký tự và tổng giá trị này sẽ là số ký tự .
- Nhấn ENTER.
- Kéo xuống Tay cầm điền Công cụ.
Bạn sẽ nhận được Điểm trong cột Dấu .
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 biết độ dài ký tự.
- Nhấn ENTER.
- Kéo xuống Tay cầm điền và bạn sẽ thấy Lớp trong cột Điểm .
Phần thực hành
Thực hành đây.
Tải xuống sổ tay thực hành
Bài đọc thêm
- Cách chia văn bản trong Excel thành nhiều hàng
- Cách tách văn bản sau một từ nhất định trong Excel
- Chia văn bản theo dấu cách bằng công thức trong Excel
- Cách tách họ và tên trong Excel
<