Có thể bạn quan tâm: Excel Error “not Enough Memory”: Nguyên Nhân, Hậu Quả Và Cách Khắc Phục Chi Tiết
Mở Đầu
Trong công việc hằng ngày, đặc biệt là với những người làm việc trong lĩnh vực phân tích dữ liệu, kế toán, marketing hay quản trị dự án, việc xử lý dữ liệu trong Excel là một kỹ năng không thể thiếu. Một trong những thao tác thường gặp nhưng lại gây ra không ít khó khăn là trích xuất văn bản (text) từ một chuỗi (string). Dù bạn cần tách ra số điện thoại, mã sản phẩm, email, ngày tháng, hay bất kỳ thông tin nào được nhúng trong một ô, thì việc nắm vững các hàm và kỹ thuật trích xuất sẽ giúp bạn tiết kiệm thời gian và giảm thiểu sai sót.
Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, đầy đủ và thực hành về cách trích xuất văn bản từ chuỗi trong Excel, bao gồm:
- Giới thiệu các hàm cơ bản và nâng cao.
- Cách kết hợp hàm để giải quyết các tình huống phức tạp.
- Ví dụ thực tế và bài tập thực hành.
- So sánh phương pháp sử dụng công thức truyền thống vs. Power Query vs. VBA.
- Mẹo tối ưu và lưu ý khi làm việc với dữ liệu lớn.
Mục tiêu cuối cùng là giúp bạn độc lập trong việc xử lý mọi loại chuỗi dữ liệu, từ đơn giản đến phức tạp, ngay trong môi trường Excel.
Có thể bạn quan tâm: Không Xuống Dòng Được Trong Excel: Nguyên Nhân, Giải Pháp Và Cách Tối Ưu Hóa Công Việc
1. Kiến Thức Cơ Bản Về Chuỗi Và Văn Bản Trong Excel
1.1. Định Nghĩa Chuỗi (String) và Văn Bản (Text)
- Chuỗi (String): Là một dãy ký tự liên tiếp, có thể bao gồm chữ cái, số, ký tự đặc biệt, khoảng trắng, v.v. Ví dụ:
"ABC123-XYZ","2026/07/01". - Văn bản (Text): Trong Excel, mọi dữ liệu được định dạng là Text nếu nó không được công thức tính toán. Khi nói “trích xuất văn bản” chúng ta thực chất đang tách ra các ký tự hoặc phần con của một chuỗi.
1.2. Các Kiểu Dữ Liệu Liên Quan
| Kiểu dữ liệu | Mô tả | Ví dụ |
|---|---|---|
| Text (Văn bản) | Dữ liệu ký tự | "John Doe" |
| Number (Số) | Dữ liệu số | 12345 |
| Date (Ngày) | Dữ liệu ngày tháng | 01/01/2026 |
| Boolean | TRUE / FALSE | TRUE |
Khi trích xuất, chúng ta thường làm việc với Text, nhưng kết quả có thể được chuyển đổi sang Number hoặc Date nếu cần.
1.3. Tại Sao Việc Trích Xuất Quan Trọng?
- Làm sạch dữ liệu: Loại bỏ ký tự không cần thiết, chuẩn hoá định dạng.
- Tách dữ liệu: Đưa các thông tin được gộp trong một ô ra các cột riêng biệt.
- Chuẩn bị cho phân tích: Đảm bảo dữ liệu ở dạng chuẩn để dùng trong PivotTable, Power BI, v.v.
- Tự động hoá: Giảm công việc thủ công, giảm lỗi nhập liệu.
Có thể bạn quan tâm: Excel Encountered Errors During Save: Nguyên Nhân, Giải Pháp Và Cách Phòng Ngừa Khi Làm Việc Với Microsoft Excel
2. Các Hàm Cơ Bản Để Trích Xuất Văn Bản
2.1. LEFT, RIGHT, MID
| Hàm | Cú pháp | Mô tả |
|---|---|---|
LEFT(text, num_chars) | Trích xuất num_chars ký tự từ bên trái của chuỗi. | =LEFT("Excel",2) → "Ex" |
RIGHT(text, num_chars) | Trích xuất num_chars ký tự từ bên phải của chuỗi. | =RIGHT("Excel",3) → "cel" |
MID(text, start_num, num_chars) | Trích xuất num_chars ký tự bắt đầu từ vị trí start_num. | =MID("Excel",2,3) → "xce" |
Lưu ý: start_num bắt đầu từ 1, không phải 0.
2.1.1. Ví dụ thực tế
Giả sử cột A chứa mã sản phẩm có dạng "PROD-2026-00123" và bạn muốn tách:
- Phần tiền tố
"PROD"→=LEFT(A2,4) - Năm
"2026"→=MID(A2,6,4) - Số thứ tự
"00123"→=RIGHT(A2,5)
2.2. FIND và SEARCH
Hai hàm này trả về vị trí (số thứ tự) của một chuỗi con trong chuỗi cha.
| Hàm | Cú pháp | Khác biệt |
|---|---|---|
FIND(find_text, within_text, start_num) | Case-sensitive (phân biệt chữ hoa/thường). | =FIND("-",A2) trả về vị trí dấu gạch ngang đầu tiên. |
SEARCH(find_text, within_text, start_num) | Không phân biệt chữ hoa/thường. | =SEARCH("@",B2) tìm vị trí ký tự “@”. |
2.2.1. Kết hợp FIND/SEARCH với MID
Nếu bạn không biết trước vị trí bắt đầu, bạn có thể dùng FIND để xác định:
=MID(A2, FIND("-",A2)+1, FIND("-",A2, FIND("-",A2)+1) - FIND("-",A2) - 1)
Công thức trên tách phần giữa của chuỗi "PROD-2026-00123" thành "2026".
2.3. LEN
LEN(text) trả về độ dài (số ký tự) của chuỗi. Rất hữu dụng khi cần tính toán vị trí động.
2.4. TRIM, CLEAN, SUBSTITUTE
| Hàm | Mô tả |
|---|---|
TRIM(text) | Loại bỏ khoảng trắng thừa ở đầu, cuối và giảm các khoảng trắng liên tiếp thành một. |
CLEAN(text) | Loại bỏ các ký tự không hiển thị (ASCII < 32). |
SUBSTITUTE(text, old_text, new_text, instance_num) | Thay thế old_text bằng new_text. Nếu bỏ instance_num, thay thế tất cả. |
Ví dụ: =SUBSTITUTE(A2,"-","/") chuyển "2026-07-01" thành "2026/07/01".
3. Trường Hợp Thực Tế Và Giải Pháp
3.1. Tách Email Thành Username và Domain
Giả sử cột A chứa email: john.doe@example.com.
- Username (phần trước
@):=LEFT(A2, FIND("@",A2)-1) - Domain (phần sau
@):=MID(A2, FIND("@",A2)+1, LEN(A2)-FIND("@",A2))
3.2. Lấy Số Điện Thoại Từ Văn Bản
Chuỗi: "Khách hàng: Nguyen Van A, SDT: 0909-123-456".
Bước 1: Xác định vị trí “SDT: ”:
=FIND("SDT:", A2) + LEN("SDT: ")
Bước 2: Lấy 12 ký tự (độ dài số điện thoại chuẩn):
=MID(A2, FIND("SDT:",A2)+LEN("SDT: ")+1, 12)
Bước 3: Loại bỏ dấu gạch ngang:
=SUBSTITUTE(MID(A2, FIND("SDT:",A2)+LEN("SDT: ")+1, 12), "-", "")
Kết quả: 0909123456.
3.3. Chuyển Định Dạng Ngày Từ Văn Bản “dd-mm-yyyy” Thành “yyyy/mm/dd”
Chuỗi: "15-08-2026" ở ô B2.
=DATE(RIGHT(B2,4), MID(B2,4,2), LEFT(B2,2))
Hoặc dùng TEXT để hiển thị:
=TEXT(DATE(RIGHT(B2,4), MID(B2,4,2), LEFT(B2,2)), "yyyy/mm/dd")
3.4. Tách Mã Sản Phẩm Với Độ Dài Biến Đổi
Mỗi mã sản phẩm có dạng: "ABC-12345-XYZ" hoặc "ABC-12-XYZ".
Giải pháp: Dùng FIND để xác định vị trí dấu gạch ngang đầu tiên và cuối cùng, sau đó MID.
= MID(A2, FIND("-",A2)+1, FIND("-",A2,FIND("-",A2)+1) - FIND("-",A2) - 1)
Công thức này tự động lấy phần giữa bất kể độ dài.
3.5. Lấy Từ Cuối Cùng Trong Câu
Có thể bạn quan tâm: Cách Trích Xuất Văn Bản Sau Một Ký Tự Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
Câu: "Excel là công cụ mạnh mẽ".
Bước 1: Đảo ngược chuỗi bằng hàm tùy chỉnh (VBA) hoặc dùng Power Query. Tuy nhiên, với công thức:
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)))
Giải thích: SUBSTITUTE thay mỗi khoảng trắng bằng một chuỗi dài các khoảng trắng, sau đó RIGHT lấy phần cuối có độ dài bằng độ dài chuỗi gốc, cuối cùng TRIM loại bỏ khoảng trắng dư thừa, cho ra từ cuối cùng: "mạnh".
4. Kỹ Thuật Nâng Cao: Kết Hợp Nhiều Hàm
4.1. Sử Dụng ARRAY (Mảng) Động Khi Có Nhiều Ký Tự Phân Tách
Giả sử một ô chứa danh sách email cách nhau bằng dấu chấm phẩy ;:
"john@example.com; jane@domain.com; bob@site.org"
Bạn muốn tách mỗi email ra cột riêng. Không có hàm SPLIT trong Excel (trừ Office 365/Excel 2026 với hàm TEXTSPLIT). Hai cách:
Cách 1: TEXTSPLIT (Excel 365)
=TEXTSPLIT(A2, ";")
Kết quả sẽ rải ra các cột.
Cách 2: Công Thức Mảng (Không TEXTSPLIT)
=TRIM(MID(SUBSTITUTE($A2,";",REPT(" ",LEN($A2))), (COLUMN(A:A)-1)LEN($A2)+1, LEN($A2)))
Kéo công thức sang phải để lấy từng email.
4.2. Hàm Regex (Regular Expressions) Với VBA
Excel không có hàm Regex tích hợp, nhưng bạn có thể viết UDF (User Defined Function) trong VBA:
Function RegExtract(text As String, pattern As String) As String Dim regEx As Object Set regEx = CreateObject("VBScript.RegExp") regEx.Pattern = pattern regEx.Global = False If regEx.Test(text) Then RegExtract = regEx.Execute(text)(0).SubMatches(0) Else RegExtract = "" End If
End Function
Sử dụng trong sheet:
=RegExtract(A2, "(\d{4})-(\d{2})-(\d{2})")
Trích xuất ngày dạng yyyy-mm-dd.
4.3. Power Query (Get & Transform)
Power Query cung cấp giao diện kéo thả và hàm M cho việc tách, chuyển đổi dữ liệu mạnh mẽ.
Bước thực hiện:
- Chọn dữ liệu →
Data→From Table/Range. - Trong Power Query Editor, chọn cột →
Split Column→By Delimiter(hoặcBy Number of Characters). - Đối với regex, dùng
Add Column→Custom Column→ nhập công thức M:
Text.RegexReplace(Column1, "(\d{2})/(\d{2})/(\d{4})", "$3-$2-$1")
- Khi hoàn tất,
Close & Load.
Power Query rất thích hợp với dữ liệu lớn vì nó thực hiện trên bộ nhớ tạm và không làm chậm file Excel.
5. So Sánh Các Phương Pháp
| Phương pháp | Ưu điểm | Nhược điểm | Khi nào dùng |
|---|---|---|---|
| Công thức truyền thống (LEFT, MID, FIND, …) | Không cần cài đặt, nhanh cho dữ liệu nhỏ, dễ hiểu. | Khi chuỗi phức tạp, công thức dài, khó bảo trì. | Dữ liệu ít, yêu cầu nhanh, không có quyền cài macro. |
| Power Query | Xử lý dữ liệu lớn, giao diện trực quan, hỗ trợ regex, tự động hoá. | Cần học M language cơ bản, không thực thi ngay trong ô. | Dữ liệu từ nhiều nguồn, cần làm sạch, chuẩn hoá, tái sử dụng. |
| VBA (UDF, Regex) | Linh hoạt, hỗ trợ regex mạnh, có thể tạo hàm tùy chỉnh. | Cần bật macro, có rủi ro bảo mật, không phù hợp cho môi trường hạn chế macro. | Khi cần logic phức tạp, regex, hoặc muốn tái sử dụng hàm trong nhiều file. |
| TEXTSPLIT (Office 365) | Đơn giản, không cần VBA, hỗ trợ mảng động. | Chỉ có trong các phiên bản mới. | Khi làm việc trên Office 365/Excel 2026 và cần tách danh sách. |
6. Thực Hành: Bài Tập Tự Làm
Bài 1: Tách Mã Đơn Hàng
Dữ liệu ở cột A: "ORD-20230715-XYZ123".
Yêu cầu:
- Lấy ngày (
20230715) → định dạng ngày15/07/2026. - Lấy mã khách hàng (
XYZ123).
Giải pháp:
=DATE(MID(A2,9,4), MID(A2,13,2), MID(A2,15,2))
=RIGHT(A2,6)
Bài 2: Kiểm Tra Định Dạng Số Điện Thoại
Chuỗi ở B2: "(+84) 090-123-4567".
Mục tiêu: Trích xuất số chỉ gồm 10 chữ số, bỏ dấu +, -, và dấu cách.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(B2, FIND(")",B2)+2, LEN(B2)), "-", ""), " ", ""), "(", "")
Kết quả: 0901234567.
Bài 3: Đếm Số Lần Xuất Hiện Của Từ “Excel”
Cột C chứa các mô tả: "Excel là công cụ mạnh. Excel hỗ trợ tính toán."
Công thức:
=(LEN(C2)-LEN(SUBSTITUTE(LOWER(C2),"excel","")))/LEN("excel")
Kết quả: 2.
Bài 4: Tách Địa Chỉ Email và Kiểm Tra Độ Dài
Cột D: "customer1@myshop.vn".
- Email:
=LEFT(D2, FIND("@", D2)-1) - Domain:
=RIGHT(D2, LEN(D2)-FIND("@", D2)) - Kiểm tra độ dài username > 5:
=IF(LEN(LEFT(D2,FIND("@",D2)-1))>5,"OK","SHORT")
7. Mẹo Tối Ưu Khi Làm Việc Với Chuỗi Dài
- Sử dụng Named Ranges: Đặt tên cho các cột quan trọng để công thức dễ đọc.
- Tránh lặp lại tính toán: Nếu một giá trị
FINDđược dùng nhiều lần, lưu kết quả vào một ô phụ. - Kiểm tra lỗi: Dùng
IFERRORhoặcIFNAđể tránh lỗi#VALUE!khi ký tự không tồn tại.excel =IFERROR(LEFT(A2, FIND("-",A2)-1), "") - Sử dụng Table: Khi dữ liệu được định dạng thành Table, công thức tự mở rộng khi thêm dòng.
- Đánh dấu dữ liệu chưa chuẩn: Dùng Conditional Formatting để tô màu các ô chứa lỗi trích xuất.
8. Khi Nào Nên Chuyển Sang Power Query Hoặc VBA
- Dữ liệu > 10.000 dòng: Công thức truyền thống làm chậm, Power Query xử lý tốt hơn.
- Chuỗi phức tạp, nhiều ký tự đặc biệt: Regex trong VBA hoặc Power Query M language sẽ ngắn gọn hơn.
- Cần lặp lại quy trình trên nhiều file: Tạo một Query hoặc macro một lần, áp dụng cho mọi file.
9. Tổng Kết
Việc trích xuất văn bản từ chuỗi trong Excel không còn là một thách thức khi bạn nắm vững:
- Các hàm cơ bản:
LEFT,RIGHT,MID,FIND,SEARCH,LEN,TRIM,SUBSTITUTE. - Kỹ thuật kết hợp để giải quyết các trường hợp không cố định.
- Công cụ nâng cao: Power Query và VBA (regex) cho dữ liệu lớn và phức tạp.
- Phương pháp tối ưu: Sử dụng bảng, named ranges, và quản lý lỗi để bảo trì công thức.
Hy vọng bài viết này đã cung cấp cho bạn một cẩm nang toàn diện để xử lý mọi yêu cầu trích xuất chuỗi trong Excel. Hãy thử áp dụng vào công việc thực tế, và đừng ngần ngại tùy chỉnh các công thức sao cho phù hợp với dữ liệu của bạn. Chúc bạn làm việc hiệu quả và luôn “Excel” trong mọi dự án!
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ẻ.