Có thể bạn quan tâm: Cách Chuyển Đổi Văn Bản Thành Ngày Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
Mở đầu
Trong công việc hàng ngày với Microsoft Excel, chúng ta thường gặp phải những tình huống cần tìm kiếm ký tự hoặc xác định vị trí của một ký tự trong một chuỗi văn bản. Việc này không chỉ giúp lọc dữ liệu, chuẩn hoá thông tin mà còn hỗ trợ trong việc tách, ghép, hoặc thay thế nội dung một cách tự động, tiết kiệm thời gian và giảm thiểu sai sót. Bài viết này sẽ tập trung vào các hàm và kỹ thuật liên quan đến việc tìm ký tự trong chuỗi trên Excel, bao gồm:
- Giới thiệu các hàm cơ bản:
FIND,SEARCH,LEFT,RIGHT,MID,LEN,SUBSTITUTE. - So sánh
FINDvàSEARCH– khi nào dùng hàm nào. - Các công thức nâng cao: tìm ký tự xuất hiện lần thứ N, tìm ký tự không tồn tại, trích xuất phần trước hoặc sau ký tự.
- Sử dụng định dạng có điều kiện (Conditional Formatting) để đánh dấu ô chứa ký tự.
- Ứng dụng Power Query và VBA để thực hiện tìm kiếm ký tự trong tập dữ liệu lớn.
- Một số ví dụ thực tế trong kinh doanh, tài chính, và quản lý dữ liệu.
- Các lỗi thường gặp và cách khắc phục.
- Tổng kết và các tài nguyên học tập thêm.
Mục tiêu của bài viết là cung cấp cho bạn một cẩm nang toàn diện từ cơ bản đến nâng cao, giúp bạn tự tin xử lý mọi yêu cầu liên quan đến ký tự trong chuỗi trên Excel.
Có thể bạn quan tâm: Excel Countif Not Empty – Hướng Dẫn Chi Tiết Đếm Ô Không Rỗng Trong Bảng Tính
1. Các hàm cơ bản để tìm ký tự trong chuỗi
1.1 Hàm FIND
=FIND(find_text, within_text, start_num)
find_text: ký tự hoặc chuỗi cần tìm.within_text: chuỗi mà bạn muốn tìm kiếm.start_num(tùy chọn): vị trí bắt đầu tìm kiếm (mặc định là 1).
Đặc điểm:
– Phân biệt chữ hoa/thường (case‑sensitive).
– Trả về vị trí đầu tiên của find_text trong within_text.
– Nếu không tìm thấy, hàm sẽ trả về lỗi #VALUE!.
Ví dụ:
Trong ô A2 chứa giá trị "Excel2024", công thức =FIND("2", A2) sẽ trả về 6, vì ký tự “2” xuất hiện ở vị trí thứ 6.
1.2 Hàm SEARCH
=SEARCH(find_text, within_text, start_num)
- Cấu trúc giống
FIND, nhưng không phân biệt chữ hoa/thường (case‑insensitive).
Ví dụ:
Nếu A3 chứa "DataScience", công thức =SEARCH("science", A3) trả về 5 (bởi vì “Science” được coi là “science”).
1.3 Hàm LEN – Độ dài chuỗi
=LEN(text)
Trả về số ký tự trong một chuỗi. Thường dùng để xác định vị trí cuối cùng khi trích xuất phần sau ký tự.
1.4 Hàm LEFT, RIGHT, MID – Trích xuất ký tự
- LEFT(text, num_chars): Lấy n ký tự đầu tiên.
- RIGHT(text, num_chars): Lấy n ký tự cuối cùng.
- MID(text, start_num, num_chars): Lấy n ký tự bắt đầu từ vị trí
start_num.
Kết hợp với FIND hay SEARCH giúp cắt chuỗi dựa trên vị trí ký tự.
1.5 Hàm SUBSTITUTE – Thay thế ký tự
=SUBSTITUTE(text, old_text, new_text, instance_num)
Thay thế old_text bằng new_text. Tham số instance_num cho phép thay thế lần xuất hiện thứ N.
Có thể bạn quan tâm: Cách Đếm Ô Chứa Văn Bản Trong Excel: Hướng Dẫn Chi Tiết Và Thực Hành
2. Khi nào dùng FIND và khi nào dùng SEARCH?
| Tiêu chí | FIND | SEARCH |
|---|---|---|
| Phân biệt chữ hoa/thường | Có (case‑sensitive) | Không (case‑insensitive) |
| Hỗ trợ ký tự đại diện | Không (không có wildcard) | Có (hỗ trợ ?) |
| Tốc độ | Hơi nhanh hơn (đơn giản hơn) | Chậm hơn một chút do xử lý không phân biệt |
| Sử dụng phổ biến | Khi dữ liệu cần chính xác (mã, ID) | Khi tìm kiếm nội dung mô tả, bình luận |
Lưu ý: Nếu bạn muốn tìm vị trí của ký tự “” hoặc “?” trong chuỗi, hãy dùng FIND vì SEARCH sẽ coi chúng là ký tự đại diện.
3. Các công thức nâng cao
3.1 Tìm ký tự xuất hiện lần thứ N
Giả sử ô A5 chứa "abcaabca" và bạn muốn tìm vị trí của ký tự “a” lần thứ 3.
Công thức:
=FIND("~a", SUBSTITUTE(A5, "a", "~a", 3))
Giải thích:
1. SUBSTITUTE(A5, "a", "~a", 3) thay thế ký tự “a” lần thứ 3 bằng ký tự tạm “~a”.
2. FIND("~a", ...) trả về vị trí của “~a”, chính là vị trí của “a” lần thứ 3.
Nếu ký tự không xuất hiện đủ N lần, công thức sẽ trả về lỗi #VALUE!, có thể xử lý bằng IFERROR.
=IFERROR(FIND("~a", SUBSTITUTE(A5, "a", "~a", 3)), "Không đủ lần xuất hiện")
3.2 Tìm vị trí ký tự cuối cùng
Không có hàm trực tiếp, nhưng có thể dùng kết hợp FIND và SUBSTITUTE:
=FIND("@", SUBSTITUTE(A5, "a", "@", LEN(A5)-LEN(SUBSTITUTE(A5, "a", ""))))
LEN(A5)-LEN(SUBSTITUTE(A5, "a", ""))tính số lần xuất hiện của “a”.- Thay thế lần cuối cùng bằng “@”, sau đó dùng
FINDđể lấy vị trí.
3.3 Trích xuất phần trước ký tự đầu tiên
=LEFT(A5, FIND("@", A5)-1)
Nếu ký tự “@” không tồn tại, dùng IFERROR để trả về toàn bộ chuỗi:
=IFERROR(LEFT(A5, FIND("@", A5)-1), A5)
3.4 Trích xuất phần sau ký tự cuối cùng
=RIGHT(A5, LEN(A5)-FIND("@", SUBSTITUTE(A5, "@", "@", LEN(A5)-LEN(SUBSTITUTE(A5, "@", "")))))
Công thức phức tạp này giúp lấy mọi ký tự sau ký tự “@” cuối cùng.
3.5 Kiểm tra tồn tại ký tự (TRUE/FALSE)
=ISNUMBER(FIND("@", A5))
Nếu FIND trả về vị trí (số), ISNUMBER sẽ cho TRUE; nếu lỗi, trả về FALSE.
4. Đánh dấu ô chứa ký tự bằng Conditional Formatting
4.1 Bước thực hiện
- Chọn vùng dữ liệu cần kiểm tra (ví dụ A1:A100).
- Vào Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
- Nhập công thức, ví dụ để đánh dấu ô có ký tự “#”:
excel =ISNUMBER(FIND("#", A1))
- Chọn định dạng (màu nền, chữ…) và OK.
Khi có bất kỳ ký tự “#” nào trong ô, Excel sẽ tự động áp dụng định dạng đã chọn.
Có thể bạn quan tâm: Cách Trích Xuất Số Khỏi Ô Trong Excel: Hướng Dẫn Chi Tiết, Thực Hành Và Các Mẹo Nhanh
4.2 Định dạng cho nhiều ký tự
Nếu muốn đánh dấu ô chứa bất kỳ ký tự trong danh sách {"@", "#", "$"}:
=OR(ISNUMBER(FIND("@", A1)), ISNUMBER(FIND("#", A1)), ISNUMBER(FIND("$", A1)))
Hoặc dùng COUNT + SEARCH (không phân biệt chữ hoa/thường):
=COUNT(SEARCH({"@","#","$"}, A1))>0
Lưu ý: Khi dùng mảng trong Conditional Formatting, nhập công thức không cần nhấn Ctrl+Shift+Enter.
5. Xử lý dữ liệu lớn với Power Query
Power Query (Get & Transform) cung cấp một môi trường ETL mạnh mẽ, cho phép thực hiện tìm ký tự mà không cần viết công thức phức tạp trong ô.
5.1 Bước nhập dữ liệu
- Data → Get Data → From File → From Workbook (hoặc nguồn khác).
- Chọn bảng dữ liệu và Load To → Only Create Connection.
5.2 Thêm cột tùy chỉnh để tìm ký tự
Trong Power Query Editor:
- Add Column → Custom Column.
- Đặt tên cột, ví dụ
Pos_At. - Nhập công thức M:
m = Text.PositionOf(ColumnName, "@", Occurrence.First)
Occurrence.Firsttrả về vị trí xuất hiện đầu tiên (0‑based). Nếu không tìm thấy, trả về-1.
5.3 Lọc hoặc tách chuỗi
- Lọc: Chọn cột
Pos_At→ Filter → does not equal -1. - Tách: Thêm cột mới sử dụng
Text.BeforeDelimiterhoặcText.AfterDelimiter.
m = Text.BeforeDelimiter(ColumnName, "@") = Text.AfterDelimiter(ColumnName, "@")
Power Query thực hiện các bước này trên toàn bộ tập dữ liệu mà không làm chậm Excel, rất phù hợp với hàng chục nghìn, hàng trăm nghìn dòng.
6. Tự động hoá bằng VBA
Khi công việc yêu cầu tìm ký tự trong nhiều sheet, hoặc thực hiện thao tác lặp lại, VBA là giải pháp linh hoạt.
6.1 Ví dụ: Đánh dấu ô có ký tự “#” trong toàn bộ workbook
Sub HighlightHash() Dim ws As Worksheet, rng As Range, cell As Range For Each ws In ThisWorkbook.Worksheets Set rng = ws.UsedRange For Each cell In rng If Not IsError(cell.Value) Then If InStr(1, cell.Value, "#", vbBinaryCompare) > 0 Then cell.Interior.Color = vbYellow End If End If Next cell Next ws
End Sub
InStrtương đương vớiFIND(phân biệt chữ hoa/thường khi dùngvbBinaryCompare).- Thay
vbBinaryComparebằngvbTextCompaređể không phân biệt.
6.2 Trích xuất phần sau ký tự trong cột A và ghi vào cột B
Sub ExtractAfterAt() Dim ws As Worksheet, lastRow As Long, i As Long, pos As Long Set ws = ThisWorkbook.Sheets("Data") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For i = 2 To lastRow 'giả sử có tiêu đề ở hàng 1 pos = InStrRev(ws.Cells(i, "A").Value, "@") If pos > 0 Then ws.Cells(i, "B").Value = Mid(ws.Cells(i, "A").Value, pos + 1) Else ws.Cells(i, "B").Value = ws.Cells(i, "A").Value End If Next i
End Sub
InStrRevtìm vị trí của ký tự lần cuối (tương tự công thức tìm vị trí cuối).
7. Ứng dụng thực tế
7.1 Kiểm tra định dạng email
Giả sử cột A chứa địa chỉ email, muốn xác định các email không hợp lệ (không có ký tự “@” hoặc “.” sau “@”).
=IF(AND(ISNUMBER(FIND("@", A2)), ISNUMBER(FIND(".", A2, FIND("@", A2)+1))), "Hợp lệ", "Không hợp lệ")
7.2 Phân tách mã sản phẩm
Mã sản phẩm dạng "AB-1234-XYZ" – muốn tách phần số (1234) giữa hai dấu “-”.
=MID(A2, FIND("-", A2)+1, FIND("-", A2, FIND("-", A2)+1)-FIND("-", A2)-1)
7.3 Đếm số lần xuất hiện ký tự trong một cột
Sử dụng công thức mảng (Excel 365/2026) để đếm ký tự “#” trong toàn cột B:
=SUM(LEN(B:B)-LEN(SUBSTITUTE(B:B, "#", "")))
Nếu dữ liệu quá lớn, chuyển sang Power Query hoặc VBA để tăng hiệu suất.
7.4 Xử lý dữ liệu tài chính – Tìm dấu “-” để xác định số âm
=IF(LEFT(A2,1)="-", -1VALUE(MID(A2,2,LEN(A2)-1)), VALUE(A2))
Hoặc đơn giản hơn với --:
=--A2
Khi số được nhập dưới dạng văn bản có dấu “-”, hàm -- sẽ chuyển đổi thành số âm.
8. Các lỗi thường gặp và cách khắc phục
| Lỗi | Nguyên nhân | Cách khắc phục |
|---|---|---|
| #VALUE! | Ký tự không tồn tại; dùng FIND/SEARCH trên giá trị số hoặc lỗi kiểu dữ liệu. | Dùng IFERROR(..., "Không tìm thấy") hoặc ISNUMBER để kiểm tra trước. |
| #NAME? | Tên hàm sai, hoặc dùng hàm không có trong phiên bản Excel (ví dụ TEXTSPLIT chỉ có trong Excel 365). | Kiểm tra chính tả, cập nhật phiên bản Excel. |
| Kết quả sai do phân biệt chữ hoa/thường | Dùng FIND khi cần không phân biệt. | Thay FIND bằng SEARCH, hoặc dùng UPPER/LOWER để chuẩn hoá. |
| Hiệu suất chậm khi áp dụng công thức trên hàng nghìn | Công thức mảng phức tạp tính lại liên tục. | Sử dụng Power Query, VBA, hoặc chuyển sang công thức động (Dynamic Arrays) để giảm tính toán. |
| Ký tự đặc biệt () hoặc ? bị hiểu là wildcard | Dùng SEARCH hoặc FIND trực tiếp. | Đối với SEARCH, thay thế ký tự bằng ~ hoặc ~? để escape. |
9. Tổng kết
Việc tìm ký tự trong chuỗi trên Excel không chỉ dừng lại ở hàm FIND hay SEARCH. Khi kết hợp với các hàm trích xuất (LEFT, RIGHT, MID), độ dài (LEN), thay thế (SUBSTITUTE) và công cụ nâng cao như Power Query hay VBA, bạn có thể giải quyết hầu hết các bài toán xử lý dữ liệu thực tiễn:
- Xác định vị trí ký tự (đầu, cuối, lần N).
- Tách phần trước hoặc sau ký tự.
- Kiểm tra tính hợp lệ (email, mã sản phẩm).
- Đánh dấu dữ liệu bất thường bằng Conditional Formatting.
- Tự động hoá quy trình với VBA khi cần xử lý trên nhiều sheet hoặc workbook.
Hãy thực hành các ví dụ trên trong môi trường thực tế của bạn, dần dần xây dựng thư viện công thức và macro riêng. Khi đã nắm vững, bạn sẽ biến Excel thành một công cụ xử lý văn bản mạnh mẽ, giúp tăng năng suất và giảm thiểu lỗi trong công việc.
10. Tài nguyên học tập thêm
| Loại tài liệu | Link / Tên | Mô tả |
|---|---|---|
| Sách | Excel 2026 Power Programming with VBA – Michael Alexander, Dick Kusleika | Chi tiết về VBA, bao gồm xử lý chuỗi. |
| Blog | Chandoo.org – “Find & Replace Text in Excel” | Hướng dẫn thực tế với hình ảnh. |
| Video | YouTube – “Excel FIND vs SEARCH – When to use which?” (Leila Gharani) | Giải thích nhanh và ví dụ thực tế. |
| Khóa học | Coursera – “Excel Skills for Business” (Macquarie University) | Bao gồm phần xử lý dữ liệu văn bản. |
| Cộng đồng | Reddit r/excel | Đặt câu hỏi, chia sẻ công thức nâng cao. |
Hy vọng bài viết đã cung cấp cho bạn một cẩm nang toàn diện về cách tìm ký tự trong chuỗi trên Excel, từ những công thức đơn giản đến các giải pháp nâng cao. Chúc bạn thành công trong việc áp dụng những kiến thức này vào công việc và dự án của mình!
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ẻ.