Có thể bạn quan tâm: Excel Gantt Chart Và Conditional Formatting: Hướng Dẫn Chi Tiết Từ A Đến Z
Mở Đầu
Trong công việc hàng ngày, từ việc quản lý dữ liệu khách hàng, báo cáo tài chính đến việc phân tích dữ liệu bán hàng, Excel luôn là công cụ không thể thiếu. Một trong những thao tác cơ bản nhưng lại rất quan trọng là lấy (extract) văn bản từ một ô. Bạn có thể muốn tách họ và tên, lấy mã sản phẩm, trích xuất ngày tháng hay chỉ đơn giản là loại bỏ các ký tự không mong muốn. Bài viết này sẽ cung cấp cho bạn toàn bộ kiến thức cần thiết để lấy văn bản từ ô trong Excel một cách hiệu quả, bao gồm:
- Giới thiệu các hàm cơ bản và nâng cao.
- Hướng dẫn chi tiết từng bước thực hiện.
- Các ví dụ thực tế và mẫu công thức.
- Mẹo tối ưu hoá, tránh lỗi và tự động hoá quy trình bằng VBA.
- So sánh các phương pháp và lựa chọn giải pháp phù hợp.
Với hơn 1500 từ, bài viết sẽ giúp bạn không chỉ hiểu mà còn áp dụng ngay vào công việc thực tế.
Có thể bạn quan tâm: Cách Đóng Băng (freeze) Hai Dòng Đầu Tiên Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z
1. Tổng Quan Về Các Hàm Lấy Văn Bản Trong Excel
1.1. LEFT, RIGHT, MID
- LEFT(text, num_chars): Lấy một số ký tự nhất định từ phía bên trái.
- RIGHT(text, num_chars): Lấy một số ký tự nhất định từ phía bên phải.
- MID(text, start_num, num_chars): Lấy một đoạn ký tự bắt đầu từ vị trí
start_num.
1.2. FIND & SEARCH
- FIND(find_text, within_text, start_num): Tìm vị trí của một chuỗi ký tự, phân biệt chữ hoa/thường.
- SEARCH(find_text, within_text, start_num): Tương tự FIND nhưng không phân biệt chữ hoa/thường.
1.3. LEN
- LEN(text): Đếm số ký tự trong một chuỗi, giúp xác định độ dài cho các hàm LEFT, RIGHT, MID.
1.4. TRIM, CLEAN, SUBSTITUTE
- TRIM(text): Loại bỏ khoảng trắng thừa ở đầu và cuối.
- CLEAN(text): Loại bỏ ký tự không hiển thị.
- SUBSTITUTE(text, old_text, new_text, instance_num): Thay thế một chuỗi ký tự bằng chuỗi khác.
1.5. TEXTAFTER, TEXTBEFORE, TEXTSPLIT (Excel 365)
- TEXTAFTER(text, delimiter, instance_num, match_mode, match_end): Lấy phần sau dấu phân cách.
- TEXTBEFORE(text, delimiter, instance_num, match_mode, match_end): Lấy phần trước dấu phân cách.
- TEXTSPLIT(text, col_delimiter, row_delimiter, ignore_empty, match_mode): Tách chuỗi thành mảng.
1.6. FILTERXML & WEBSERVICE (đối với dữ liệu XML/HTML)
- FILTERXML(xml, xpath): Trích xuất dữ liệu từ XML.
- WEBSERVICE(url): Lấy dữ liệu từ một URL (kết hợp với FILTERXML để lấy văn bản).
1.7. VBA (Visual Basic for Applications)
Khi công thức không đáp ứng được yêu cầu phức tạp, VBA cho phép viết macro để trích xuất, xử lý và đưa kết quả trở lại ô.
Có thể bạn quan tâm: Cách “freeze Both Row And Column” Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z
2. Các Tình Huống Thực Tế Và Cách Áp Dụng
2.1. Tách Họ và Tên Từ Ô “Họ Tên”
Giả sử cột A chứa họ và tên đầy đủ (Ví dụ: “Nguyễn Văn A”). Bạn muốn tách Họ và Tên ra các cột B và C.
2.1.1. Lấy Họ (phần trước dấu cách đầu tiên)
=LEFT(A2, FIND(" ", A2) - 1)
FIND(" ", A2)trả về vị trí dấu cách đầu tiên.- Trừ 1 để không lấy dấu cách.
2.1.2. Lấy Tên (phần sau dấu cách cuối cùng)
=TRIM(RIGHT(A2, LEN(A2) - FIND("@", SUBSTITUTE(A2, " ", "@", LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))
SUBSTITUTE(A2, " ", "@", LEN(A2)-LEN(SUBSTITUTE(A2," ","")))thay dấu cách cuối cùng bằng “@”.FIND("@", ...)xác định vị trí dấu “@”.RIGHTvàLENlấy phần còn lại,TRIMloại bỏ khoảng trắng thừa.
Lưu ý: Nếu tên luôn chỉ gồm một khoảng trắng, công thức đơn giản hơn:
=RIGHT(A2, LEN(A2) - FIND(" ", A2))
2.2. Lấy Mã Sản Phẩm Từ Chuỗi “SKU-12345-XYZ”
Chuỗi trong cột D: “SKU-12345-XYZ”. Bạn muốn lấy 12345.
=MID(D2, FIND("-", D2)+1, FIND("-", D2, FIND("-", D2)+1) - FIND("-", D2) - 1)
FIND("-", D2)vị trí dấu “-” đầu tiên.FIND("-", D2, FIND("-", D2)+1)vị trí dấu “-” thứ hai.MIDlấy ký tự giữa hai dấu “-”.
2.3. Trích Xuất Ngày Từ Định Dạng “2026/04/15 – Completed”
Cột E chứa “2026/04/15 – Completed”. Lấy ngày:
=LEFT(E2, FIND(" ", E2)-1)
Nếu định dạng có thể thay đổi (có hoặc không có “ – Completed”), bạn có thể dùng:
=TEXTBEFORE(E2, " ", 1)
2.4. Loại Bỏ Ký Tự Đặc Biệt
Giả sử ô F chứa “ABC#123$%”. Muốn giữ lại chỉ chữ và số:
=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(F2, ROW(INDIRECT("1:"&LEN(F2))),1)), MID(F2, ROW(INDIRECT("1:"&LEN(F2))),1), ""))
Giải thích:
– MID(F2, ROW(INDIRECT("1:"&LEN(F2))),1) tạo mảng các ký tự.
– ISNUMBER(--... ) kiểm tra ký tự có phải số không.
– TEXTJOIN ghép lại thành chuỗi mới.
2.5. Tách Danh Sách Phân Tách Bằng Dấu Phẩy
Có thể bạn quan tâm: Cách Lấy Màu Nền (cell Color) Trong Excel: Hướng Dẫn Chi Tiết Và Thực Hành
Cột G chứa “Apple, Banana, Cherry, Date”. Để đưa mỗi món vào cột riêng (H, I, J, K), dùng TEXTSPLIT (Excel 365):
=TEXTSPLIT(G2, ", ")
Kết quả sẽ tự động tràn ra các ô bên phải.
Nếu bạn dùng phiên bản cũ hơn, có thể kết hợp FILTERXML:
=FILTERXML("<t><s>" & SUBSTITUTE(G2, ", ", "</s><s>") & "</s></t>", "//s")
3. Kỹ Thuật Nâng Cao Với VBA
Khi dữ liệu phức tạp (ví dụ: lấy URL trong chuỗi, trích xuất email, hoặc xử lý hàng ngàn dòng), VBA sẽ giúp:
3.1. Hàm VBA Lấy Văn Bản Giữa Hai Dấu
Function GetBetween(text As String, startDelim As String, endDelim As String) As String Dim p1 As Long, p2 As Long p1 = InStr(text, startDelim) If p1 = 0 Then GetBetween = "" Exit Function End If p1 = p1 + Len(startDelim) p2 = InStr(p1, text, endDelim) If p2 = 0 Then GetBetween = Mid(text, p1) Else GetBetween = Mid(text, p1, p2 - p1) End If
End Function
Cách dùng: =GetBetween(A2, "SKU-", "-XYZ") sẽ trả về “12345”.
3.2. Macro Loại Bỏ Các Ký Tự Không Phải Chữ và Số
Sub CleanAlphaNumeric() Dim rng As Range, cell As Range Set rng = Selection ' Chọn các ô cần xử lý For Each cell In rng Dim s As String, i As Long, ch As String s = cell.Value Dim result As String For i = 1 To Len(s) ch = Mid(s, i, 1) If ch Like "A-Za-z0-9" Then result = result & ch Next i cell.Value = result Next cell
End Sub
3.3. Tự Động Tách Email Từ Văn Bản
Function ExtractEmail(text As String) As String Dim regEx As Object Set regEx = CreateObject("VBScript.RegExp") regEx.Pattern = "\bA-Z0-9._%+-+@A-Z0-9.-+\.A-Z{2,}\b" regEx.IgnoreCase = True regEx.Global = True Dim matches As Object Set matches = regEx.Execute(text) If matches.Count > 0 Then ExtractEmail = matches(0) ' Lấy email đầu tiên Else ExtractEmail = "" End If
End Function
Sử dụng trong Excel: =ExtractEmail(A2).
4. Các Bước Thực Hiện Chi Tiết
Bước 1: Xác Định Định Dạng Dữ Liệu
- Kiểm tra xem dữ liệu có đều dạng hay không (có dấu phân cách cố định, vị trí ký tự cố định…).
- Nếu dữ liệu không đồng nhất, cân nhắc chuẩn hoá bằng hàm
TRIM,CLEANtrước khi tách.
Bước 2: Chọn Hàm Phù Hợp
| Tình huống | Hàm đề xuất |
|---|---|
| Lấy ký tự cố định từ đầu/cuối | LEFT, RIGHT |
| Lấy đoạn dựa trên vị trí động | MID + FIND |
| Tách theo dấu phân cách | TEXTBEFORE, TEXTAFTER, TEXTSPLIT |
| Thay thế hoặc loại bỏ ký tự | SUBSTITUTE, TRIM, CLEAN |
| Xử lý phức tạp (regex) | VBA + RegExp |
Bước 3: Áp Dụng Công Thức và Kiểm Tra Kết Quả
- Đưa công thức vào ô đầu tiên.
- Kéo công thức xuống để áp dụng cho toàn cột.
- Kiểm tra một vài ô ngẫu nhiên để đảm bảo không có lỗi.
Bước 4: Xử Lý Lỗi Thông Thường
- #VALUE!: Thường do
FINDkhông tìm thấy ký tự. DùngIFERRORđể xử lý.excel =IFERROR(LEFT(A2, FIND("-", A2)-1), A2) - #NAME?: Hàm không tồn tại trong phiên bản Excel hiện tại (ví dụ
TEXTSPLITtrong Excel 2026). Thay bằng công thức cũ hoặc VBA.
Bước 5: Tối Ưu Hóa
- Tránh lặp lại tính toán
LEN,FINDnhiều lần bằng cách đặt vào ô phụ hoặc dùng LET (Excel 365):excel =LET( txt, A2, pos, FIND("-", txt), LEFT(txt, pos-1) )
5. So Sánh Các Phương Pháp
| Phương pháp | Độ phức tạp | Tốc độ | Linh hoạt | Yêu cầu phiên bản |
|---|---|---|---|---|
| LEFT/RIGHT/MID + FIND | Thấp | Rất nhanh | Trung bình | Tất cả |
| TEXTBEFORE/TEXTAFTER/TEXTSPLIT | Thấp | Nhanh | Cao (hỗ trợ mảng) | Excel 365 |
| FILTERXML | Trung bình | Trung bình | Trung bình (đòi hỏi XML) | Excel 2026+ |
| VBA | Cao | Nhanh (đối với khối lượng lớn) | Rất cao (regex, vòng lặp) | Tất cả (có macro) |
| Power Query | Trung bình | Nhanh (xử lý dữ liệu lớn) | Rất cao (giao diện trực quan) | Excel 2026+ |
Lời khuyên: Đối với công việc thường ngày và dữ liệu không quá lớn, dùng công thức LEFT/RIGHT/MID hoặc TEXTBEFORE/TEXTAFTER là đủ. Khi dữ liệu phức tạp hoặc cần tự động hoá quy trình, hãy cân nhắc VBA hoặc Power Query.
6. Thực Hành: Bài Tập Tự Làm
- Tách Mã Đơn Vị: Cột H chứa “Unit-001-A”. Lấy “001”.
Gợi ý: Dùng
MIDkết hợp vớiFIND.Lấy Địa Chỉ Email: Cột I chứa “Contact: john.doe@example.com (Sales)”. Lấy email.
Gợi ý: Sử dụng hàm
FILTERXMLvới regex (nếu có) hoặc VBA.Chuyển Định Dạng Ngày: Cột J chứa “15/04/2026”. Chuyển sang định dạng “2026-04-15”.
- Gợi ý:
=DATE(RIGHT(J2,4), MID(J2,4,2), LEFT(J2,2)).
Thực hiện các bài tập này sẽ giúp bạn nắm vững cách lấy văn bản từ ô trong Excel.
7. Kết Luận
Việc lấy văn bản từ ô trong Excel không chỉ là một thao tác đơn thuần mà còn là nền tảng để xử lý dữ liệu, tự động hoá báo cáo và tăng năng suất làm việc. Bằng việc nắm vững các hàm cơ bản như LEFT, RIGHT, MID, FIND, SEARCH, TEXTBEFORE, TEXTAFTER, và mở rộng sang các công cụ mạnh mẽ như VBA, Power Query hay TEXTSPLIT, bạn sẽ có khả năng:
- Tách dữ liệu nhanh chóng và chính xác.
- Xử lý dữ liệu không đồng nhất một cách linh hoạt.
- Tối ưu hoá quy trình, giảm thời gian và lỗi con người.
- Tự động hoá công việc lặp đi lặp lại nhờ macro và script.
Hãy áp dụng những kiến thức trong bài viết này vào thực tiễn, thử nghiệm các công thức, và không ngừng khám phá thêm các hàm mới trong các phiên bản Excel hiện đại. Khi bạn đã thành thạo, việc lấy văn bản từ ô sẽ trở thành một thao tác “ngầm” nhưng vô cùng quan trọng, giúp bạn biến dữ liệu thô thành thông tin có giá trị.
Chúc bạn thành công và luôn “excel” trong công việc!
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ẻ.