Cách sử dụng hàm lấy ký tự có điều kiện trong Excel chi tiết
Bạn đang gặp khó khăn trong việc xử lý các chuỗi dữ liệu và muốn biết chính xác hàm lấy ký tự có điều kiện trong Excel là gì? Các phương pháp thông thường sẽ không hiệu quả khi bạn cần cách tách ký tự trong Excel dựa trên các tiêu chí cụ thể. Do đó, Sforum sẽ hướng dẫn chi tiết cách kết hợp các hàm để tạo ra công thức linh hoạt theo mọi điều kiện.
Hàm lấy ký tự có điều kiện trong Excel là gì?
Hàm lấy ký tự có điều kiện trong Excel thực chất là sự kết hợp linh hoạt giữa các lệnh xử lý chuỗi cơ bản với cú pháp logic. Bộ công cụ này cho phép người dùng tự động trích xuất chính xác một phần văn bản cụ thể từ chuỗi dữ liệu gốc khi phần đó đáp ứng các tiêu chí hay điều kiện đã được thiết lập.
Nắm vững cách xây dựng hàm lấy ký tự có điều kiện trong Excel là một kỹ năng quan trọng, giúp các chuyên viên dữ liệu dễ dàng chuẩn hóa và phân loại thông tin một cách nhanh chóng. Phương pháp này thay thế cho cách tách ký tự trong Excel thủ công kém linh hoạt.
Các hàm thường dùng để lấy ký tự trong Excel
Để áp dụng cách lấy ký tự có điều kiện trong Excel, bạn cần hiểu các công cụ và hàm cơ bản hỗ trợ. Hai nhóm chính được sử dụng bao gồm các lệnh tách chuỗi cơ bản và các hàm phụ trợ để định vị vị trí.
Các hàm lấy ký tự cơ bản
Các hàm sau đây sẽ chịu trách nhiệm trực tiếp cắt và lấy ký tự từ chuỗi văn bản gốc:
| Tên hàm | Công dụng | Cú pháp |
| LEFT | Lấy một số lượng ký tự nhất định tính từ bên trái chuỗi. | =LEFT(Text, [Num_chars]) |
| RIGHT | Lấy một số lượng ký tự nhất định tính từ bên phải chuỗi. | =RIGHT(Text, [Num_chars]) |
| MID | Hàm này giúp bạn lấy ra một đoạn văn bản cụ thể, bắt đầu tại vị trí tùy chọn trong chuỗi dữ liệu ban đầu. | =MID(Text, Start_num, Num_chars) |
Các hàm có thể kết hợp để lấy ký tự
Các công thức này đóng vai trò xác định vị trí để các hàm cơ bản biết được nên bắt đầu, kết thúc cắt chuỗi ở đâu hoặc đặt ra điều kiện để việc tách chuỗi xảy ra. Đây là chìa khóa giúp tạo ra hàm lấy ký tự có điều kiện trong Excel hiệu quả:
| Tên hàm | Công dụng | Cú pháp |
| SEARCH/FIND | Tìm vị trí của một ký tự hoặc chuỗi con cụ thể trong chuỗi gốc. | SEARCH/FIND(find_text, within_text, [start_num]) |
| LEN |
Công thức có thể được dùng để xác định tổng độ dài của chuỗi văn bản, giúp việc tách hoặc cắt chuỗi chính xác hơn. Lệnh này đóng vai trò quan trọng khi được sử dụng kết hợp với các hàm khác trong Excel để xử lý chuỗi hiệu quả hơn. |
=LEN(text) |
| IF | Bạn có thể tách phần văn bản mong muốn nếu điều kiện đặt ra đúng, đồng thời trả về giá trị thay thế khi điều kiện sai. | =IF(điều_kiện, giá_trị_nếu_đúng, giá_trị_nếu_sai) |
| IFERROR | Xử lý lỗi (ví dụ: lỗi #VALUE!, #N/A) nếu điều kiện tìm kiếm hoặc tách ký tự không được thỏa mãn. | =IFERROR(công_thức, giá_trị_nếu_lỗi) |
Cách kết hợp các hàm để lấy ký tự theo điều kiện kèm ví dụ
Việc kết hợp các hàm là chìa khóa để xử lý dữ liệu phức tạp và làm chủ cách lấy ký tự có điều kiện trong Excel một cách hiệu quả. Dưới đây là các ví dụ thực tế giúp bạn hiểu hơn cách kết hợp các hàm.
Lấy ký tự bên trái nếu thỏa điều kiện (LEFT + IF)
Phương pháp này sử dụng IF để kiểm tra một điều kiện logic (thường là kiểm tra các ký tự đầu chuỗi) trước khi quyết định có thực hiện việc trích xuất bằng hàm LEFT hay không.
Cấu trúc chung:
=IF(điều_kiện; LEFT(text; num_chars); giá_trị_nếu_sai)
Giải thích:
- Nếu điều kiện đúng, thì lấy ký tự bên trái theo LEFT.
- Nếu sai, trả về giá trị khác (thường là chuỗi rỗng "", hoặc giá trị mặc định).
Tình huống: Trong cột Mã Đơn Hàng, bạn chỉ muốn lấy 2 ký tự đầu tiên (VN) nếu đơn hàng thuộc khu vực Việt Nam. Nếu không, ô kết quả sẽ trống.
Giải thích công thức:
LEFT(C6;2)="VN": Kiểm tra xem 2 ký tự đầu tiên của ô C6 có tồn tại "VN" hay không.
- Nếu đúng, công thức trả về LEFT(C6;2) (kết quả là "VN").
- Nếu sai, công thức trả về ô trống ("").
=> Kết quả:
Khi làm việc với các công thức phức tạp như thực hiện hàm lấy ký tự có điều kiện trong excel, hiệu năng của máy tính rất quan trọng. Hãy tham khảo ngay các dòng laptop Dell với hiệu năng mạnh mẽ:
[Product_Listing categoryid="154" propertyid="" customlink="https://cellphones.com.vn/laptop/dell.html" title="Danh sách Laptop Dell đang được quan tâm nhiều tại CellphoneS"]
Lấy ký tự bên phải nếu chứa ký tự đặc biệt (RIGHT + SEARCH)
Đây là cách lấy ký tự có điều kiện trong Excel phổ biến khi bạn muốn trích xuất phần cuối của chuỗi mà không biết trước độ dài. Tuy nhiên, trong dữ liệu đó, bạn biết rõ thành phần cần trích xuất nằm sau một ký tự phân cách nào đó (ví dụ: dấu @, dấu gạch ngang -) thì đây là phương pháp tối ưu:
Cấu trúc chung:
=RIGHT(A1; LEN(A1) - SEARCH("_"; A1))
Giải thích:
- Xác định vị trí ký tự đặc biệt (dùng SEARCH)
- Dùng LEN để biết tổng độ dài chuỗi
- Tính số ký tự bên phải cần lấy: LEN(A1) - vị_trí_ký_tự_đặc_biệt
Tình huống: Tách phần tên miền (sau ký tự @) khỏi địa chỉ email. Độ dài tên miền khác nhau nên phải dùng hàm SEARCH để định vị.
Giải thích công thức:
- SEARCH("@"; C6): Tìm vị trí của ký tự @ (ví dụ: 6).
- LEN(C6): Lấy tổng độ dài chuỗi.
- LEN(C6) - SEARCH("@"; C6): Tính số ký tự cần lấy từ bên phải. Ví dụ: 15 - 6 = 9.
- RIGHT(C6; 9): Trích xuất 9 ký tự từ bên phải chuỗi, cho ra tên miền.
Đây là ví dụ điển hình cho hàm lấy ký tự có điều kiện trong Excel được thiết kế dựa trên vị trí dữ liệu cần trích xuất.
=> Kết quả:
Lấy ký tự ở giữa nếu điều kiện đúng (MID + IF + SEARCH)
Phương pháp này thể hiện rõ cách lấy ký tự có điều kiện trong Excel bằng việc kiểm tra sự tồn tại của một thành phần đặc biệt, sau đó mới thực hiện trích xuất một phần cố định của chuỗi.
Công thức chung:
=IF(ISNUMBER(SEARCH("_";A1)); MID(A1; SEARCH("_";A1)+1; LEN(A1)); "")
Giải thích:
- ISNUMBER(...) giúp kiểm tra nếu tìm thấy ký tự _ (khi đó SEARCH trả về vị trí là một số).
- Dùng SEARCH("_"; A1) để tìm vị trí ký tự đặc biệt trong chuỗi.
- SEARCH("_"; A1)+1 → vị trí bắt đầu cắt (ngay sau ký tự đặc biệt).
- LEN(A1) → độ dài tối đa để lấy đến hết chuỗi (không cần tính số ký tự cụ thể).
- Nếu trong chuỗi không có ký tự _, hàm SEARCH sẽ báo lỗi.
- Nhờ có IF, khi ISNUMBER là FALSE → công thức trả về "" (chuỗi rỗng) => không lỗi.
Tình huống: Bạn có chuỗi như PX_ABC123 trong ô C6. Yêu cầu là lấy 3 ký tự ngay sau dấu gạch dưới (_), nếu chuỗi có chứa dấu gạch dưới. Nếu chuỗi không chứa dấu _, ô kết quả sẽ trả về trống ("").
Giải thích công thức:
Điều kiện kiểm tra (SEARCH + ISNUMBER): ISNUMBER(SEARCH("_"; C6)) xác định xem có tìm thấy dấu gạch dưới (_) trong chuỗi hay không. Nếu có, nó trả về TRUE.
- MID (Nếu điều kiện đúng): MID(C6, SEARCH("_";C6)+1; 3) được thực hiện:
- SEARCH("_";C6)+1: Xác định vị trí bắt đầu, ngay sau dấu _.
- 3: Quy định việc trích xuất chính xác 3 ký tự tiếp theo.
- Giá trị trả về nếu sai: Trả về ô trống ("").
=> Kết quả:
Lấy phần chữ trước số (khi chuỗi chứa cả chữ và số)
Khi cần cách tách ký tự trong Excel để lấy tên sản phẩm dạng chữ khỏi chuỗi Tên Sản Phẩm + Số lượng (ví dụ: Bánh_Quy_120), ta cần tìm vị trí của ký tự số đầu tiên.
Công thức chung:
=LEFT(A1; MIN(IFERROR(FIND(SEQUENCE(10;1;0); A1); 9999)) - 1)
Giải thích:
- FIND(SEQUENCE(10;1;0); A1): Dùng SEQUENCE để tự động tạo mảng từ 0 đến 9 (thay vì liệt kê {0;1;2;3;4;5;6;7;8;9}) => Excel tìm vị trí xuất hiện của các chữ số từ 0-9 trong chuỗi A1 => Kết quả là một mảng vị trí hoặc lỗi #VALUE!.
- IFERROR(...; 9999): Khi không tìm thấy số (báo lỗi), Excel thay bằng giá trị lớn 9999, giúp chuỗi không chứa số vẫn không bị lỗi khi tính MIN.
- MIN(...): Xác định vị trí nhỏ nhất trong mảng, tức là vị trí của ký tự số đầu tiên trong chuỗi.
- LEFT(A1; … - 1): Lấy tất cả ký tự nằm trước vị trí ký tự số đầu tiên.
Tình huống: Tách phần tên sản phẩm (chữ) khỏi chuỗi gộp, với ký tự phân cách là dấu gạch dưới (_) ngay trước phần số.
Giải thích công thức:
- FIND(SEQUENCE(10;1;0); A1): Tìm vị trí của tất cả các chữ số (0–9) trong chuỗi
- IFERROR(...; 9999): Nếu không có chữ số nào, thay lỗi bằng giá trị 9999
- MIN(...): Trả về vị trí của chữ số đầu tiên xuất hiện
- -1: Trừ đi 1 để bỏ chính ký tự số đó
- LEFT(A1; ...): Lấy phần chữ bên trái trước ký tự số đầu tiên
=> Kết quả:
Ứng dụng thực tế của hàm lấy ký tự có điều kiện
Việc thành thạo cách lấy ký tự có điều kiện trong Excel giúp chúng ta giải quyết nhiều bài toán dữ liệu thực tế phức tạp. Dưới đây là các ứng dụng chính khi sử dụng hàm lấy ký tự có điều kiện trong Excel trong các tác vụ công việc:
- Phân tích Mã sản phẩm/Mã đơn hàng: Ứng dụng này cho phép trích xuất các thành phần phân loại (khu vực, loại hàng, năm sản xuất) từ một chuỗi mã hỗn hợp.
- Xử lý dữ liệu định danh: Thực hiện cách tách ký tự trong Excel để phân tách Họ và Tên đệm ra khỏi Tên riêng hoặc tách mã số nhân viên khỏi chức danh được gộp chung trong một ô.
- Trích xuất thông tin liên hệ: Dùng các hàm có điều kiện để lấy tên miền từ danh sách địa chỉ email hoặc trích xuất mã vùng điện thoại để phục vụ cho các chiến dịch marketing.
- Lọc dữ liệu dựa trên quy tắc: Chỉ trích xuất số lượng hàng tồn kho (phần số) khi tên sản phẩm (phần chữ) thỏa mãn một điều kiện nhất định.
Lỗi thường gặp khi dùng hàm lấy ký tự có điều kiện trong Excel
Khi thực hiện cách lấy ký tự có điều kiện trong Excel thông qua việc kết hợp nhiều hàm, người dùng thường gặp phải một số lỗi phổ biến có thể dễ dàng khắc phục:
- Lỗi #VALUE!: Thường xảy ra khi hàm SEARCH hoặc FIND không tìm thấy ký tự đặc biệt được chỉ định trong chuỗi (ví dụ: tìm dấu phẩy trong ô không có dấu phẩy).
Khắc phục: Luôn lồng công thức vào hàm IFERROR(công thức, giá trị thay thế) để trả về một giá trị dễ đọc (như 0 hoặc ô trống "") thay vì báo lỗi.
- Lỗi #NUM!: Xảy ra khi số lượng ký tự bạn yêu cầu MID, LEFT, hoặc RIGHT trích xuất là một số âm (ví dụ: do tính toán vị trí SEARCH bị sai lệch).
Khắc phục: Kiểm tra lại logic tính toán tham số Num_chars. Đảm bảo rằng kết quả của phép tính luôn lớn hơn hoặc bằng 0.
- Vấn đề phân biệt chữ hoa/thường: Một điểm cần lưu ý là hàm SEARCH không phân biệt chữ hoa hay chữ thường, trong khi đó, FIND thì ngược lại.
Khắc phục: Nếu điều kiện của bạn cần phân biệt chữ hoa/thường (ví dụ: chỉ lấy ký tự khi gặp "SP" chứ không phải "sp"), hãy ưu tiên sử dụng hàm FIND trong công thức kết hợp.
Qua bài viết này, bạn đã hiểu rõ bản chất hàm lấy ký tự có điều kiện trong Excel là gì và cách áp dụng thực tế. Việc thành thạo cách tách ký tự trong Excel sẽ giúp bạn xây dựng quy trình làm việc chuyên nghiệp và nâng cao đáng kể hiệu suất phân tích dữ liệu. Hy vọng bạn sẽ vận dụng thành thạo các công thức trên để nâng cao hiệu quả làm việc trong Excel.
Xem thêm bài viết tại chuyên mục: Tin học văn phòng
Bình luận (0)