Có thể bạn quan tâm: Học Excel Cho Dân Văn Phòng: Từ Cơ Bản Đến Thành Thạo Trong 30 Ngày
Giới thiệu chung
Trong môi trường làm việc với dữ liệu, Microsoft Excel luôn là công cụ không thể thiếu. Một trong những thao tác phổ biến nhưng lại gây ra không ít khó khăn cho người dùng là ghép (concatenate) các chuỗi ký tự với một ký tự ngăn cách (separator). Dù bạn đang xử lý danh sách khách hàng, mã sản phẩm, địa chỉ email, hay muốn tạo ra các mã định danh duy nhất, việc ghép chuỗi một cách linh hoạt, hiệu quả và có thể tùy chỉnh separator là rất quan trọng.
Bài viết này sẽ đi sâu vào mọi khía cạnh của việc ghép chuỗi trong Excel, từ các hàm truyền thống như CONCATENATE, & cho đến các hàm hiện đại như TEXTJOIN, CONCAT và các kỹ thuật sử dụng VBA. Chúng tôi sẽ cung cấp:
- Giải thích chi tiết cách hoạt động của từng hàm.
- Ví dụ thực tế minh hoạ từng trường hợp.
- Mẹo tối ưu để xử lý dữ liệu lớn, tránh lỗi trùng lặp hoặc khoảng trắng thừa.
- So sánh hiệu năng giữa các phương pháp.
- Cách áp dụng trong các tình huống thực tiễn như tạo email tự động, mã QR, hay chuẩn hoá dữ liệu.
Sau khi đọc xong, bạn sẽ nắm vững mọi kỹ thuật cần thiết để ghép chuỗi với separator trong Excel một cách nhanh chóng, chính xác và linh hoạt.
Có thể bạn quan tâm: Excel Bị Ẩn Dòng Đầu: Nguyên Nhân, Cách Khắc Phục Và Phòng Ngừa Hiệu Quả
1. Tổng quan về ghép chuỗi trong Excel
1.1. Tại sao cần separator?
Khi ghép nhiều giá trị lại với nhau, việc phân tách chúng bằng một ký tự đặc biệt (dấu phẩy, dấu chấm phẩy, dấu gạch ngang, v.v.) giúp:
- Đọc dễ dàng: Người dùng có thể nhanh chóng nhận ra các phần tử riêng biệt.
- Xử lý dữ liệu: Các công cụ khác (SQL, Python, Power Query) thường dựa vào separator để tách lại dữ liệu.
- Định dạng chuẩn: Ví dụ, danh sách địa chỉ email phải được ngăn cách bằng dấu chấm phẩy trong Outlook.
1.2. Các hàm ghép chuỗi truyền thống
| Hàm | Cú pháp | Đặc điểm |
|---|---|---|
CONCATENATE | =CONCATENATE(text1, text2, …) | Không hỗ trợ separator tự động, cần dùng & hoặc CHAR(10). |
& (Toán tử nối) | =A1 & B1 | Cú pháp ngắn gọn, nhưng không có tùy chọn bỏ qua ô trống. |
TEXT | =TEXT(value, format_text) | Dùng để định dạng số trước khi ghép. |
1.3. Hàm mới trong Office 365 / Excel 2026+
| Hàm | Cú pháp | Ưu điểm |
|---|---|---|
CONCAT | =CONCAT(text1, text2, …) | Thay thế CONCATENATE, hỗ trợ mảng. |
TEXTJOIN | =TEXTJOIN(delimiter, ignore_empty, text1, text2, …) | Cho phép đặt separator và bỏ qua ô trống. |
IFS | — | Không liên quan trực tiếp, nhưng hữu ích trong điều kiện ghép. |
Trong phần tiếp theo, chúng ta sẽ đánh giá chi tiết từng hàm, tập trung vào TEXTJOIN vì đây là hàm mạnh nhất cho yêu cầu “concat string with separator”.
Có thể bạn quan tâm: Excel Bị Tắt Đột Ngột: Nguyên Nhân, Hậu Quả Và Cách Khắc Phục Hiệu Quả
2. Sử dụng hàm TEXTJOIN – Giải pháp toàn diện
2.1. Cú pháp và các tham số
=TEXTJOIN(delimiter, ignore_empty, text1, text2, …)
- delimiter: Ký tự hoặc chuỗi ký tự dùng làm separator (ví dụ:
",","; "," - "). - ignore_empty:
TRUEđể bỏ qua các ô trống,FALSEđể giữ lại (sẽ tạo ra separator thừa nếu có ô trống). - text1, text2, …: Các giá trị hoặc phạm vi cần ghép.
Lưu ý:
TEXTJOINchấp nhận mảng nên bạn có thể truyền trực tiếp một phạm vi nhưA1:A10.
2.2. Ví dụ cơ bản
Giả sử cột A chứa tên, cột B chứa họ, cột C chứa tuổi. Bạn muốn tạo một chuỗi “Họ, Tên (Tuổi)”.
| A (Tên) | B (Họ) | C (Tuổi) |
|---|---|---|
| An | Nguyễn | 28 |
| Bình | Trần | 35 |
| Hồng | Lê |
Công thức:
=TEXTJOIN(" ", TRUE, B2, A2, "(" & C2 & ")")
Kết quả:
– Dòng 2: Nguyễn An (28)
– Dòng 3: Trần Bình (35)
– Dòng 4: Lê Hồng () → Nhờ ignore_empty = TRUE, không tạo dấu phẩy thừa.
2.3. Ghép danh sách nhiều hàng thành một chuỗi
Bạn muốn liệt kê tất cả email trong cột D, ngăn cách bằng dấu chấm phẩy (;). Dữ liệu:
| D (Email) |
|---|
| a@example.com |
| b@example.com |
| (ô trống) |
| c@example.com |
Công thức:
=TEXTJOIN("; ", TRUE, D2:D5)
Kết quả: a@example.com; b@example.com; c@example.com
2.4. Bỏ qua giá trị trùng lặp
Excel không có hàm tích hợp để loại bỏ trùng lặp trong TEXTJOIN, nhưng chúng ta có thể kết hợp với UNIQUE (có trong Office 365).
=TEXTJOIN(", ", TRUE, UNIQUE(D2:D10))
Kết quả sẽ là danh sách duy nhất, mỗi giá trị chỉ xuất hiện một lần.
2.5. Xử lý khoảng trắng thừa
Nếu dữ liệu có khoảng trắng ở đầu/cuối, TEXTJOIN sẽ giữ nguyên. Để làm sạch, dùng TRIM:
=TEXTJOIN(", ", TRUE, TRIM(A2:A10))
Nếu muốn áp dụng TRIM cho toàn bộ mảng, cần dùng hàm MAP (Excel 365):
=TEXTJOIN(", ", TRUE, MAP(A2:A10, LAMBDA(x, TRIM(x))))
2.6. Khi ignore_empty = FALSE
Nếu bạn muốn giữ lại vị trí trống (ví dụ, để tạo một định dạng cố định), đặt ignore_empty là FALSE:
=TEXTJOIN("|", FALSE, A2:A5)
Nếu A3 trống, kết quả sẽ có || ở vị trí tương ứng, hữu ích khi cần định dạng cố định cho các hệ thống nhập liệu.
2.7. Hiệu năng và giới hạn
- Số lượng đối số:
TEXTJOINcó thể nhận tới 255 đối số, hoặc một phạm vi mảng. - Độ dài chuỗi: Excel cho phép chuỗi tối đa 32.767 ký tự trong một ô. Khi ghép danh sách lớn, cần kiểm tra không vượt quá giới hạn này.
- Tốc độ:
TEXTJOINnhanh hơn việc lặp lại&trong các vòng lặp, đặc biệt khi xử lý hàng ngàn dòng.
3. Hàm CONCAT – Khi không cần separator
CONCAT là phiên bản nâng cấp của CONCATENATE, nhưng không hỗ trợ separator. Tuy nhiên, bạn có thể kết hợp với TEXTJOIN để tạo separator tùy chỉnh:
Có thể bạn quan tâm: Excel Cho Người Đi Làm: Bí Quyết Thành Thạo, Tăng Năng Suất Và Thăng Tiến Nhanh Chóng
=TEXTJOIN("-", TRUE, CONCAT(A2:C2))
Trong trường hợp dữ liệu đã được định dạng sẵn (không cần separator), CONCAT là lựa chọn ngắn gọn:
=CONCAT(A2, B2, C2)
4. Sử dụng toán tử & – Cách truyền thống nhưng linh hoạt
4.1. Cú pháp cơ bản
=A2 & ", " & B2 & " - " & C2
4.2. Kết hợp với IF để bỏ qua ô trống
=IF(A2="", "", A2 & ", ") & IF(B2="", "", B2 & " - ") & C2
Mặc dù độ phức tạp tăng lên khi có nhiều cột, nhưng đối với công thức ngắn và phiên bản Excel cũ, & vẫn hữu dụng.
4.3. Vấn đề khoảng trắng và lỗi
- Khi một trong các ô trống, bạn sẽ có separator thừa (ví dụ:
, ,). Cần dùngIFđể kiểm soát. &không tự động bỏ qua lỗi#N/A. DùngIFERRORđể bảo vệ:
=IFERROR(A2, "") & ", " & IFERROR(B2, "")
5. Ghép chuỗi bằng VBA – Khi công thức không đủ
5.1. Khi nào cần VBA?
- Xử lý hàng triệu dòng: Công thức có thể chậm, VBA chạy nhanh hơn khi sử dụng mảng.
- Yêu cầu tùy biến phức tạp: Ví dụ, ghép chuỗi dựa trên nhiều tiêu chí, hoặc tạo file CSV tự động.
- Tự động hóa: Khi muốn ghép và lưu kết quả vào file văn bản, email, hay cơ sở dữ liệu.
5.2. Mã VBA mẫu: CONCATENATE WITH SEPARATOR
Function JoinRange(rng As Range, _ Optional delimiter As String = ", ", _ Optional ignoreEmpty As Boolean = True) As String Dim arr As Variant Dim i As Long Dim result As String arr = rng.Value For i = LBound(arr, 1) To UBound(arr, 1) If Not ignoreEmpty Or Trim(arr(i, 1)) <> "" Then If result <> "" Then result = result & delimiter result = result & arr(i, 1) End If Next i JoinRange = result
End Function
Cách dùng trong ô Excel:
=JoinRange(A2:A10, "; ", TRUE)
5.3. Tối ưu tốc độ
- Đọc dữ liệu vào mảng (
arr = rng.Value) thay vì lặp qua từng ô. - Tránh
Select/Activate. - Sử dụng
Application.ScreenUpdating = Falsekhi chạy macro lớn.
5.4. Ghi kết quả ra file văn bản
Sub ExportJoinedString() Dim joined As String joined = JoinRange(Sheet1.Range("A2:A1000"), vbTab, True) Dim fso As Object, txt As Object Set fso = CreateObject("Scripting.FileSystemObject") Set txt = fso.CreateTextFile("C:\Temp\joined.txt", True, True) txt.Write joined txt.Close
End Sub
6. Ứng dụng thực tiễn
6.1. Tạo danh sách email cho Outlook
- Chuẩn bị: Cột A – Họ, B – Tên, C – Domain (ví dụ
@company.com). - Công thức:
=TEXTJOIN("; ", TRUE, A2:A100 & "." & B2:B100 & C2:C100)
- Kết quả:
nguyen.an@company.com; tran.binh@company.com; …
6.2. Tạo mã sản phẩm duy nhất
Giả sử bạn có mã nhóm ở cột A, số thứ tự ở cột B, và muốn ghép bằng dấu gạch ngang.
=TEXTJOIN("-", TRUE, A2, TEXT(B2, "0000"))
Kết quả: GRP-0012
6.3. Định dạng địa chỉ cho bản in
Cột A – Địa chỉ, B – Phường, C – Quận, D – Thành phố. Separator là dấu phẩy và khoảng trắng.
=TEXTJOIN(", ", TRUE, A2, B2, C2, D2)
6.4. Chuẩn hoá dữ liệu nhập khẩu
Khi nhận file CSV từ đối tác, các trường có thể được ngăn cách bằng |. Để đưa lại vào Excel dưới dạng cột riêng, dùng TEXTSPLIT (Excel 365) ngược lại: TEXTJOIN để tái hợp:
=TEXTJOIN("|", TRUE, FILTERXML("<t><s>" & SUBSTITUTE(A2, "|", "</s><s>") & "</s></t>", "//s"))
7. So sánh các phương pháp
| Phương pháp | Độ linh hoạt | Xử lý ô trống | Hiệu năng (hàng ngàn) | Hỗ trợ Excel cũ | Ghi chú |
|---|---|---|---|---|---|
TEXTJOIN | Rất cao (separator, ignore_empty) | Có tùy chọn | Nhanh (sử dụng mảng) | Không (Office 365/2026+) | Giải pháp mặc định hiện nay |
CONCAT | Trung bình (không separator) | Không | Nhanh | Không (Office 365/2026+) | Dùng khi không cần separator |
& + IF | Cao (có thể tùy chỉnh) | Phải tự lập trình | Chậm khi nhiều hàng | Có | Dành cho Excel 2003‑2026 |
VBA JoinRange | Rất cao (tùy chỉnh, ghi file) | Có | Rất nhanh (mảng) | Có (có macro) | Cần bật macro, bảo mật |
Power Query | Cao (xử lý dữ liệu lớn) | Có | Rất nhanh | Có (Excel 2026+) | Thích hợp ETL, không trực tiếp trong ô |
8. Các lỗi thường gặp và cách khắc phục
| Lỗi | Nguyên nhân | Giải pháp |
|---|---|---|
| #VALUE! | Dữ liệu không phải dạng văn bản (ví dụ: lỗi #N/A hoặc ngày) | Dùng IFERROR hoặc TEXT để chuyển đổi. |
| Chuỗi quá dài | Tổng ký tự > 32.767 | Giảm số lượng trường, hoặc chia thành nhiều ô. |
| Separator thừa | ignore_empty = FALSE và có ô trống | Đặt TRUE hoặc dùng IF để loại bỏ. |
| Khoảng trắng thừa | Dữ liệu có space ở đầu/cuối | Áp dụng TRIM hoặc CLEAN. |
| Không nhận hàm TEXTJOIN | Phiên bản Excel cũ | Sử dụng & hoặc viết VBA. |
9. Tips và thủ thuật nâng cao
- Ghép đa chiều: Khi cần ghép cả hàng và cột, dùng
TRANSPOSEkết hợpTEXTJOIN.
excel =TEXTJOIN(", ", TRUE, TRANSPOSE(A1:D1))
(Nhấn Ctrl+Shift+Enter trong Excel trước 365 để nhập công thức mảng.)
Ghép có định dạng màu: Excel không hỗ trợ định dạng trong công thức, nhưng bạn có thể tạo Conditional Formatting dựa trên nội dung đã ghép.
Sử dụng
LETđể giảm lặp (Excel 365):
excel =LET(rng, A2:A10, delim, "; ", TEXTJOIN(delim, TRUE, rng))
- Kết hợp với
FILTERđể ghép chỉ những giá trị thỏa mãn điều kiện:
excel =TEXTJOIN(", ", TRUE, FILTER(A2:A100, B2:B100="Active"))
- Tự động cập nhật khi dữ liệu thay đổi: Đặt công thức trong Table (Ctrl+T) để khi thêm hàng mới, công thức tự mở rộng.
10. Kết luận
Việc ghép chuỗi với separator trong Excel không còn là thách thức khó vượt. Từ các hàm cổ điển (CONCATENATE, &) tới các hàm hiện đại (TEXTJOIN, CONCAT) và giải pháp VBA, bạn có thể lựa chọn công cụ phù hợp nhất dựa trên:
- Phiên bản Excel đang sử dụng.
- Số lượng dữ liệu cần xử lý.
- Yêu cầu về tính năng (bỏ qua ô trống, loại bỏ trùng lặp, định dạng đặc biệt).
Hãy nhớ rằng:
TEXTJOINlà lựa chọn ưu tiên cho hầu hết các trường hợp hiện đại.&+IFlà giải pháp nhanh cho các tệp Excel cũ.- VBA mở ra khả năng tự động hoá và xử lý dữ liệu quy mô lớn.
- Power Query (Get & Transform) cung cấp một môi trường ETL mạnh mẽ khi làm việc với dữ liệu từ nhiều nguồn.
Hy vọng bài viết này đã cung cấp cho bạn toàn bộ kiến thức cần thiết để trở thành “bậc thầy” trong việc ghép chuỗi với separator trong Excel. Hãy thử áp dụng ngay vào dự án thực tế của mình và chia sẻ kết quả 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ẻ.