Trong công việc sử dụng Microsoft Excel, một trong những câu hỏi thường gặp là làm cách nào để lấy giá trị sau một ký tự cụ thể trong một ô dữ liệu. Dù bạn đang làm việc với danh sách khách hàng, dữ liệu bán hàng, hay dữ liệu tài chính, việc trích xuất phần sau ký tự mà bạn quan tâm là điều cần thiết để phân tích, báo cáo, hay làm sạch dữ liệu. Bài viết này sẽ cung cấp một hướng dẫn chi tiết, kèm theo các công thức, ví dụ minh hoạ và mẹo vặt giúp bạn thực hiện công việc này một cách nhanh chóng và chính xác nhất.
Có thể bạn quan tâm: Làm Thế Nào Để Lấy Dữ Liệu Theo Điều Kiện Trong Excel: Hướng Dẫn Cụ Thể Và Thực Tiễn
1. Tại sao cần lấy giá trị sau ký tự trong Excel?
Trong thực tế, dữ liệu thường được lưu trữ dưới dạng chuỗi kết hợp nhiều thành phần, ví dụ:
VN12345– mã khách hàng gồm tiền tốVNvà số ID.2026-10-15; 08:45– ngày giờ có dấu;phân cách.ABC-XYZ-9876– mã sản phẩm có nhiều dấu gạch ngang.
Nếu bạn muốn lấy phần số 12345, 08:45, hoặc 9876, bạn sẽ cần một công thức có thể tách phần sau ký tự (VN, ;, -,…) mà bạn muốn.
Có thể bạn quan tâm: Cách Lấy 2 Số Cuối Của Năm Trong Excel – Hướng Dẫn Chi Tiết Và Ứng Dụng Thực Tế
2. Các công thức cơ bản để lấy giá trị sau ký tự
2.1. Công thức MID + FIND
Công thức này là cách tiếp cận truyền thống và linh hoạt nhất:
=MID(A1, FIND("VN", A1) + LEN("VN"), LEN(A1))
Giải thích:
FIND("VN", A1)trả về vị trí bắt đầu của chuỗi"VN".LEN("VN")là độ dài của chuỗi"VN". Thêm vào vị trí tìm được để xác định vị trí bắt đầu của phần cần lấy.MID(A1, start, length)trích xuất phần từ vị trístartvới độ dàilength. Ở đây, ta lấyLEN(A1)để lấy hết phần còn lại.
Ví dụ thực tế
| A1 | Công thức | Kết quả |
|---|---|---|
| VN12345 | =MID(A1, FIND("VN", A1)+LEN("VN"), LEN(A1)) | 12345 |
| 2026-10-15; 08:45 | =MID(A1, FIND(";", A1)+1, LEN(A1)) | 08:45 |
| ABC-XYZ-9876 | =MID(A1, FIND("-", A1, FIND("-", A1)+1)+1, LEN(A1)) | 9876 |
Lưu ý: FIND nhạy cảm với chữ hoa/độ, trong khi SEARCH không.
2.2. Công thức RIGHT + FIND
Nếu bạn chỉ cần phần đằng sau ký tự cuối cùng trong chuỗi, công thức này tiện lợi:
=RIGHT(A1, LEN(A1) - FIND("VN", A1) - LEN("VN") + 1)
Giải thích: RIGHT(A1, n) trả về n ký tự từ phía cuối chuỗi. Ta tính n bằng tổng độ dài chuỗi trừ đi vị trí bắt đầu của phần cần lấy.
Ví dụ
| A1 | Công thức | Kết quả |
|---|---|---|
| VN12345 | =RIGHT(A1, LEN(A1)-FIND("VN",A1)-LEN("VN")+1) | 12345 |
| 2026-10-15; 08:45 | =RIGHT(A1, LEN(A1)-FIND(";",A1)) | 08:45 |
2.3. Công thức TEXTAFTER (Excel 365/Excel 2026 mới nhất)
Từ Office 365 hoặc Excel 2026, Microsoft đã giới thiệu hàm TEXTAFTER:
=TEXTAFTER(A1, "VN")
Kết quả: 12345. Hàm này rất ngắn gọn và dễ đọc.
Ví dụ
| A1 | Công thức | Kết quả |
|---|---|---|
| VN12345 | =TEXTAFTER(A1, "VN") | 12345 |
| 2026-10-15; 08:45 | =TEXTAFTER(A1, ";") | 08:45 |
Chú ý:
TEXTAFTERchỉ khả dụng trong các phiên bản mới nhất. Nếu sử dụng phiên bản cũ hơn, bạn phải dùng MID + FIND.
2.4. Công thức TRIM + SUBSTITUTE
Nếu ký tự xuất hiện nhiều lần trong chuỗi và bạn muốn lấy phần sau lần xuất hiện cuối cùng, bạn có thể kết hợp SUBSTITUTE và TRIM:
=TRIM(RIGHT(SUBSTITUTE(A1, "VN", REPT(" ", LEN(A1))), LEN(A1)))
Giải thích: SUBSTITUTE thay thế mọi "VN" bằng một dãy khoảng trắng dài, sau đó RIGHT lấy phần cuối cùng và TRIM gỡ bỏ khoảng trắng thừa. Kết quả là phần sau ký tự cuối cùng.
Ví dụ
| A1 | Công thức | Kết quả |
|---|---|---|
| VN1VN2VN3 | =TRIM(RIGHT(SUBSTITUTE(A1,"VN",REPT(" ",LEN(A1))),LEN(A1))) | 3 |
| ABC-XYZ-9876 | =TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),LEN(A1))) | 9876 |
Có thể bạn quan tâm: Làm Tròn Số Trong Excel Như Thế Nào
3. Các trường hợp đặc biệt và cách xử lý
3.1. Khi ký tự không tồn tại trong chuỗi
Nếu chuỗi không chứa ký tự cần tìm, FIND sẽ trả về lỗi #VALUE!. Để tránh lỗi, ta có thể dùng IFERROR:
=IFERROR(MID(A1, FIND("VN", A1)+LEN("VN"), LEN(A1)), "Không có VN")
3.2. Khi ký tự xuất hiện nhiều lần
Có thể bạn quan tâm: Lấy Dữ Liệu Từ Google Sheet Về Excel: Hướng Dẫn Chi Tiết Và Các Kỹ Thuật Thực Tiễn
Nếu bạn muốn lấy phần sau lần thứ nhất xuất hiện của ký tự, công thức MID + FIND đã chính xác. Tuy nhiên, nếu muốn lấy phần sau lần cuối cùng, ta phải tìm vị trí cuối cùng:
=IFERROR(MID(A1, FIND("VN", A1, LEN(A1)-LEN(SUBSTITUTE(A1,"VN",""))+1)+LEN("VN"), LEN(A1)), "Không có VN")
Giải thích: SUBSTITUTE(A1,"VN","") loại bỏ tất cả ký tự "VN", so sánh độ dài để tìm vị trí cuối cùng.
3.3. Ký tự là dấu phân cách đặc biệt
Nếu ký tự là dấu phân cách đặc biệt như ;, :, |, -, , etc., bạn vẫn có thể dùng FIND như bình thường. Tuy nhiên, khi ký tự là dấu - hoặc , hãy đảm bảo không gây nhầm lẫn với ký tự đặc biệt trong công thức.
3.4. Trích xuất phần sau nhiều ký tự
Nếu cần lấy phần sau hai ký tự liên tiếp, bạn chỉ cần tăng LEN("VN") lên 2:
=MID(A1, FIND("VN", A1) + 2, LEN(A1))
4. Kết hợp với các hàm khác để xử lý dữ liệu phức tạp
4.1. Lấy phần sau ký tự và chuyển thành số
=VALUE(MID(A1, FIND("VN", A1)+LEN("VN"), LEN(A1)))
4.2. Lấy phần sau ký tự và chuyển thành ngày
=DATEVALUE(MID(A1, FIND(";", A1)+1, LEN(A1)))
4.3. Lấy phần sau ký tự và chuyển thành giờ
=TIMEVALUE(MID(A1, FIND(";", A1)+2, LEN(A1)))
4.4. Lấy phần sau ký tự và chuyển thành chuỗi không khoảng trắng
=SUBSTITUTE(MID(A1, FIND("VN", A1)+LEN("VN"), LEN(A1)), " ", "")
5. Mẹo vặt khi làm việc với dữ liệu có ký tự phân cách
- Kiểm tra dữ liệu đầu vào: Đôi khi dữ liệu mang ký tự khoảng trắng thừa, hoặc ký tự lạ. Sử dụng
TRIMvàCLEANđể làm sạch dữ liệu trước khi trích xuất. - Sử dụng bảng tính điều kiện: Khi áp dụng công thức phân tích dữ liệu cho hàng ngàn dòng, hãy tạo một cột phụ để hiển thị kết quả kiểm tra lỗi (nếu có).
- Chèn hàm
IFERROR: Giúp tránh lỗi khi ký tự không tồn tại hoặc dữ liệu sai định dạng. - Định dạng ô: Khi kết quả cần là số, ngày, hay giờ, hãy định dạng ô tương ứng để tránh hiển thị sai.
6. Bài tập thực hành
- Bài tập 1: Cho dữ liệu
A1là chuỗiABC-123-XYZ. Viết công thức lấy phần sau ký tự-cuối cùng. - Bài tập 2: Dữ liệu
B1là2026-10-15; 08:45. Trích xuất giờ (08:45) và chuyển thành giá trị thời gian. - Bài tập 3: Trong chuỗi
C1 = "VN12345;VN67890", lấy phần sau ký tự;cuối cùng và biến đổi thành số.
Gợi ý: Sử dụng
MID + FIND,RIGHT,TEXTAFTER(nếu có),VALUE,TIMEVALUE,IFERROR.
7. Kết luận
Việc lấy giá trị sau ký tự trong Excel không chỉ là một kỹ thuật cơ bản mà còn là chìa khóa giúp bạn xử lý dữ liệu phức tạp. Bằng cách kết hợp các hàm như MID, FIND, TEXTAFTER, RIGHT, cùng với các hàm xử lý lỗi và chuyển đổi kiểu dữ liệu, bạn có thể tự tin làm sạch, phân tích, và báo cáo dữ liệu một cách chính xác và nhanh chóng. Hy vọng bài viết này đã cung cấp cho bạn đủ công cụ và kiến thức để thực hiện công việc một cách hiệu quả nhất. Happy Excel!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.