Có thể bạn quan tâm: Lập Trình Vba Trong Excel Full Pdf: Hướng Dẫn Toàn Diện Từ Cơ Bản Đến Nâng Cao
1. Giới thiệu chung về việc cắt chuỗi trong Excel
Trong quá trình làm việc với dữ liệu, chúng ta thường gặp những tình huống cần tách, cắt, hoặc trích xuất một phần chuỗi ký tự từ một ô trong Excel. Việc này không chỉ giúp làm sạch dữ liệu, chuẩn hoá thông tin mà còn tạo điều kiện thuận lợi cho các phân tích, báo cáo và tự động hoá quy trình. Excel cung cấp một loạt các hàm và công cụ mạnh mẽ để thực hiện các thao tác cắt chuỗi, trong đó các hàm LEFT, RIGHT, MID, TEXTBEFORE, TEXTAFTER, TEXTSPLIT, FIND, SEARCH, LEN và các hàm mới hơn trong Office 365/Excel 2026 như LET, LAMBDA… đều đóng vai trò quan trọng.
Bài viết này sẽ đi sâu vào từng lệnh cắt chuỗi, giải thích cách sử dụng, đưa ra các ví dụ thực tế, so sánh ưu nhược điểm, và cung cấp các mẹo tối ưu hoá công việc. Mục tiêu là giúp người đọc, dù là người mới bắt đầu hay người đã có kinh nghiệm, có thể tự tin áp dụng các kỹ thuật cắt chuỗi trong Excel để giải quyết các bài toán thực tiễn.
Có thể bạn quan tâm: Hàm Lấy Số Dư Trong Excel: Cách Sử Dụng, Ứng Dụng Thực Tiễn Và Mẹo Tối Ưu
2. Các hàm cắt chuỗi cơ bản trong Excel
2.1. Hàm LEFT
Cú pháp: LEFT(text, num_chars)
text: Chuỗi ký tự cần cắt.num_chars: Số ký tự cần lấy từ phía bên trái. Nếu bỏ qua, mặc định là 1 ký tự.
Ví dụ:
| A (Giá trị) | Công thức | Kết quả |
|---|---|---|
Apple | =LEFT(A2,3) | App |
123456 | =LEFT(A3,2) | 12 |
Lưu ý: Khi num_chars lớn hơn độ dài chuỗi, hàm sẽ trả về toàn bộ chuỗi.
2.2. Hàm RIGHT
Cú pháp: RIGHT(text, num_chars)
- Tương tự LEFT, nhưng lấy ký tự từ phía bên phải.
Ví dụ:
| A (Giá trị) | Công thức | Kết quả |
|---|---|---|
Banana | =RIGHT(A2,4) | nana |
2026-06-24 | =RIGHT(A3,2) | 24 |
2.3. Hàm MID
Cú pháp: MID(text, start_num, num_chars)
start_num: Vị trí bắt đầu (đánh số từ 1).num_chars: Số ký tự cần lấy.
Ví dụ:
| A (Giá trị) | Công thức | Kết quả |
|---|---|---|
ABCDEFG | =MID(A2,3,4) | CDEF |
john.doe@example.com | =MID(A3,6,3) | doe |
2.4. Hàm LEN
Cú pháp: LEN(text)
Trả về độ dài của chuỗi ký tự. Thường được dùng kết hợp với LEFT/RIGHT/MID để tính số ký tự cần cắt động.
Ví dụ: Lấy phần mở rộng file từ ô chứa tên file report_final.xlsx.
=RIGHT(A2, LEN(A2) - FIND(".", A2))
Kết quả: xlsx.
Có thể bạn quan tâm: Lấy File Excel Trong Cad: Hướng Dẫn Chi Tiết Từ A Đến Z
3. Các hàm mới (Office 365 / Excel 2026) hỗ trợ cắt chuỗi linh hoạt hơn
3.1. TEXTBEFORE và TEXTAFTER
- TEXTBEFORE(text, delimiter, instance_num, match_mode, match_end)
- TEXTAFTER(text, delimiter, instance_num, match_mode, match_end)
Hai hàm này cho phép trích xuất phần trước hoặc sau một ký tự/chuỗi phân tách (delimiter) một cách trực quan.
Ví dụ: Từ chuỗi 2026-06-24:
=TEXTBEFORE(A2, "-") // trả về 2026
=TEXTAFTER(A2, "-") // trả về 06-24 (kết quả đầu tiên sau dấu -)
Nếu muốn phần ngày:
=TEXTAFTER(TEXTAFTER(A2, "-"), "-") // trả về 24
3.2. TEXTSPLIT
Cú pháp: TEXTSPLIT(text, col_delimiter, row_delimiter, ignore_empty, match_mode)
Hàm này tách một chuỗi thành mảng (array) dựa trên các ký tự phân tách, rất hữu ích khi cần cắt nhiều phần một cách đồng thời.
Ví dụ: Chuỗi địa chỉ Hà Nội, Việt Nam, 100000:
=TEXTSPLIT(A2, ", ")
Kết quả là một mảng 3 cột: Hà Nội | Việt Nam | 100000.
Kết hợp với hàm INDEX để lấy từng phần:
=INDEX(TEXTSPLIT(A2, ", "), 1) // Hà Nội
=INDEX(TEXTSPLIT(A2, ", "), 2) // Việt Nam
3.3. LET và LAMBDA (tối ưu hoá công thức)
Khi cần thực hiện nhiều bước cắt chuỗi, LET cho phép gán biến tạm thời, giảm việc tính lại hàm và làm cho công thức dễ đọc hơn.
Ví dụ: Tách tên và họ từ Nguyễn Văn A.
=LET( full, A2, spacePos, FIND(" ", full, FIND(" ", full)+1), firstName, LEFT(full, spacePos-1), lastName, RIGHT(full, LEN(full)-spacePos), firstName & " | " & lastName
)
Kết quả: Nguyễn Văn | A.
4. Kỹ thuật kết hợp hàm để giải quyết các bài toán thực tiễn
4.1. Lấy tên file không phần mở rộng
Giả sử cột A chứa các tên file:
report_2023_Q1.xlsx
data_final.csv
image_01.png
Công thức:
=LEFT(A2, FIND(".", A2)-1)
FIND(".", A2)trả về vị trí dấu chấm.LEFT(..., vị trí-1)lấy phần trước dấu chấm.
4.2. Tách địa chỉ email thành tên người dùng và miền
Email: john.doe@example.com
=LET( email, A2, atPos, FIND("@", email), user, LEFT(email, atPos-1), domain, RIGHT(email, LEN(email)-atPos), user & " | " & domain
)
Kết quả: john.doe | example.com.
4.3. Định dạng lại số điện thoại
Dữ liệu gốc: 0123456789 (10 chữ số). Yêu cầu: (012) 345-6789.
=TEXTJOIN("", TRUE, "(", LEFT(A2,3), ") ", MID(A2,4,3), "-", RIGHT(A2,4)
)
Hoặc dùng TEXT kết hợp:
=TEXT(A2, "(000) 000-0000")
4.4. Xử lý dữ liệu thiếu dấu phân cách
Trong một số bảng, dữ liệu có thể chứa dấu gạch ngang hoặc dấu cách không đồng nhất, ví dụ: JohnDoe-2026, JohnDoe2023. Để tách tên và năm, ta có thể dùng:
=IFERROR( LEFT(A2, FIND("-", A2)-1), LEFT(A2, LEN(A2)-4) // giả sử năm luôn 4 chữ số ở cuối
)
4.5. Tách mã vùng và số điện thoại trong cột quốc tế
Có thể bạn quan tâm: Lệnh Chèn Dòng Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
Giả sử dữ liệu: +84 912345678, +1 202-555-0173.
Công thức:
=LET( txt, TRIM(A2), spacePos, FIND(" ", txt), country, LEFT(txt, spacePos-1), number, MID(txt, spacePos+1, LEN(txt)-spacePos), country & " | " & number
)
Kết quả: +84 | 912345678.
5. Xử lý các trường hợp phức tạp bằng VBA (Macro)
Khi số lượng dữ liệu lớn hoặc yêu cầu cắt chuỗi phức tạp, hàm Excel có thể chậm hoặc khó bảo trì. VBA cho phép viết User Defined Function (UDF) hoặc macro tự động hoá.
5.1. UDF cắt chuỗi dựa trên ký tự thứ N
Function CutString(ByVal txt As String, ByVal delimiter As String, _ Optional occurrence As Long = 1, _ Optional part As String = "before") As String Dim arr() As String arr = Split(txt, delimiter) If occurrence > UBound(arr) + 1 Then CutString = "" Exit Function End If If LCase(part) = "before" Then CutString = Join(Application.Index(arr, 0, Application.WorksheetFunction.Row(WorksheetFunction.Transpose(Array(1 To occurrence)))), delimiter) Else CutString = arr(occurrence - 1) End If
End Function
Cách dùng: =CutString(A2, "-", 2, "after") sẽ trả về phần sau dấu - thứ 2.
5.2. Macro tự động cắt và ghi vào cột mới
Sub SplitFullName() Dim rng As Range, cell As Range Set rng = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row) For Each cell In rng Dim parts() As String parts = Split(cell.Value, " ") cell.Offset(0, 1).Value = parts(0) ' Họ cell.Offset(0, 2).Value = parts(1) ' Tên đệm (nếu có) cell.Offset(0, 3).Value = parts(UBound(parts)) ' Tên Next cell
End Sub
Macro này sẽ tách họ, tên đệm và tên ra các cột kế bên, hữu ích cho danh sách nhân viên.
6. Các lưu ý và lỗi thường gặp khi cắt chuỗi
- Vấn đề ký tự ẩn (non‑printable): Dữ liệu nhập từ hệ thống khác có thể chứa ký tự
CHAR(160)(non‑breaking space). Khi dùngFINDhoặcSEARCHcó thể không nhận ra. Giải pháp: dùngCLEANvàTRIMtrước khi cắt.
excel =TRIM(CLEAN(A2))
Phân biệt chữ hoa/chữ thường:
FINDlà case‑sensitive, cònSEARCHkhông. Chọn hàm phù hợp để tránh lỗi không tìm thấy delimiter.Dữ liệu không có delimiter: Khi dùng
LEFT(..., FIND(...)-1)mà không có dấu phân cách, hàm sẽ trả về lỗi#VALUE!. Sử dụngIFERRORđể xử lý:
excel =IFERROR(LEFT(A2, FIND("-", A2)-1), A2)
Độ dài chuỗi quá 255 ký tự: Một số hàm cũ như
LEFT/RIGHTtrong Excel 2003 giới hạn 255 ký tự, nhưng phiên bản hiện đại không còn. Tuy nhiên, khi dùng Data Validation hoặc VLOOKUP với chuỗi dài, vẫn cần chú ý.Mảng động (Dynamic Array): Khi sử dụng
TEXTSPLIT, kết quả trả về là mảng. Nếu ô xung quanh đã có dữ liệu, Excel sẽ trả về lỗi#SPILL!. Đảm bảo vùng trống hoặc sử dụng hàm@để lấy một phần duy nhất.
7. So sánh hiệu năng: Hàm truyền thống vs. Hàm mới vs. VBA
| Phương pháp | Độ phức tạp công thức | Tốc độ (trên 10.000 hàng) | Dễ bảo trì | Khả năng mở rộng |
|---|---|---|---|---|
| LEFT/RIGHT/MID + FIND/SEARCH | Trung bình | Nhanh (tính toán trực tiếp) | Trung bình (công thức dài) | Giới hạn khi cần nhiều delimiter |
| TEXTBEFORE/TEXTAFTER/TEXTSPLIT (Office 365) | Thấp (công thức ngắn) | Rất nhanh (được tối ưu cho mảng) | Cao (đọc hiểu nhanh) | Rất linh hoạt, hỗ trợ mảng |
| VBA UDF | Thấp (code ngắn) | Trung bình – chậm hơn hàm tích hợp trên khối lượng lớn | Trung bình (cần kiến thức VBA) | Cao (có thể xử lý logic phức tạp) |
| Macro (Bulk processing) | Thấp (code) | Nhanh khi chạy một lần, giảm tải tính toán trên ô | Cao (tự động hoá) | Cao (có thể kết hợp với file CSV, database) |
Kết luận: Đối với hầu hết các tác vụ cắt chuỗi thông thường, TEXTBEFORE/TEXTAFTER/TEXTSPLIT là lựa chọn tốt nhất nếu bạn đang dùng Office 365/Excel 2026. Khi làm việc với phiên bản cũ hoặc cần xử lý logic phức tạp, kết hợp LEFT/RIGHT/MID với FIND/SEARCH hoặc viết VBA là cách hợp lý.
8. Thực hành: Bài tập mẫu để rèn luyện
Bài 1: Tách ngày, tháng, năm từ định dạng dd/mm/yyyy
Dữ liệu: 24/06/2026
Yêu cầu: Tạo 3 cột: Ngày, Tháng, Năm.
Công thức:
=TEXTBEFORE(A2, "/") // Ngày
=TEXTBETWEEN(A2, "/", "/", 2) // Tháng (hoặc dùng MID)
=TEXTAFTER(A2, "/", 2) // Năm
Bài 2: Chuẩn hoá số điện thoại quốc tế
Dữ liệu: +84-912-345-678, +1 (202) 555-0173, 0912.345.678
Yêu cầu: Đưa về dạng +CC NNN NNN NNN.
Công thức mẫu (sử dụng SUBSTITUTE và TRIM):
=LET( txt, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "-", " "), "(", ""), ")", ""), txt2, SUBSTITUTE(txt, ".", " "), parts, TEXTSPLIT(TRIM(txt2), " "), "+" & INDEX(parts, 1) & " " & INDEX(parts, 2) & " " & INDEX(parts, 3) & " " & INDEX(parts, 4)
)
Bài 3: Tách Mã Sản Phẩm và Tên Sản Phẩm
Dữ liệu: SP001 - Máy tính xách tay, SP002 - Màn hình 24".
Công thức:
=TEXTBEFORE(A2, " -") // Mã SP
=TEXTAFTER(A2, " -") // Tên SP
Bài 4: Xử lý dữ liệu nhập sai – loại bỏ ký tự không phải số
Dữ liệu: SĐT: 0912-345-678, Phone: (202)5550173.
Công thức dùng hàm FILTERXML (đối với Excel 365) để giữ lại chữ số:
=TEXTJOIN("", TRUE, FILTERXML("<t><s>" & SUBSTITUTE(SUBSTITUTE(A2, "-", ""), " ", "") & "</s></t>", "//snot()"))
Kết quả: 0912345678 và 2025550173.
9. Khi nào nên dùng Power Query thay vì hàm Excel
Power Query (Get & Transform) là công cụ ETL mạnh mẽ tích hợp trong Excel, cho phép:
- Xử lý dữ liệu hàng triệu dòng nhanh hơn nhờ engine riêng.
- Thực hiện các bước chuyển đổi (cắt chuỗi, thay thế, tách cột) bằng giao diện kéo thả và M code.
- Lưu lại lịch sử các bước để tái sử dụng.
Nếu bạn thường xuyên nhập dữ liệu từ file CSV, database, hoặc cần chuẩn hoá dữ liệu trước khi đưa vào bảng tính, hãy cân nhắc dùng Power Query:
- Import dữ liệu →
Data > Get Data > From Text/CSV. - Chọn cột chứa chuỗi, Split Column → By Delimiter → Chọn delimiter (
-, space,@, …) → Advanced options để split into rows hoặc columns. - Dùng Transform > Extract > Text Before/After Delimiter để lấy phần mong muốn.
- Áp dụng Replace Values để loại bỏ ký tự không cần.
Sau khi hoàn thiện, nhấn Close & Load để đưa dữ liệu đã xử lý vào bảng tính.
10. Tổng kết
Việc cắt chuỗi trong Excel không chỉ là một kỹ năng cơ bản mà còn là nền tảng để xử lý dữ liệu thực tiễn trong môi trường kinh doanh, tài chính, marketing hay quản trị nhân sự. Từ các hàm truyền thống LEFT, RIGHT, MID tới các hàm hiện đại TEXTBEFORE, TEXTAFTER, TEXTSPLIT, cùng với khả năng mở rộng bằng VBA và Power Query, người dùng Excel có thể:
- Tiết kiệm thời gian bằng cách tự động hoá các quy trình làm sạch dữ liệu.
- Giảm lỗi nhập liệu bằng cách chuẩn hoá và kiểm tra định dạng.
- Tăng tính linh hoạt trong việc trích xuất thông tin quan trọng từ chuỗi phức tạp.
- Xây dựng quy trình tái sử dụng (template, macro, Power Query) cho các dự án lặp lại.
Hãy lựa chọn công cụ phù hợp với môi trường và phiên bản Excel của bạn, kết hợp các mẹo và ví dụ thực tiễn đã trình bày để nâng cao hiệu suất làm việc. Chúc bạn thành công trong việc khai thác và biến đổi dữ liệu bằng các lệnh cắt chuỗi trong 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ẻ.