Trong môi trường làm việc văn phòng hiện đại, Excel luôn là công cụ không thể thiếu để xử lý dữ liệu, thực hiện các phép tính, và tạo ra các báo cáo chuyên nghiệp. Một trong những thao tác thường gặp nhưng lại vô cùng hữu ích là ghép (concatenate) các giá trị trong các ô lại với nhau, đặc biệt là khi cần chèn dấu phân cách (comma, “,”) giữa các phần tử. Việc này giúp bạn tạo danh sách, địa chỉ email, mô tả sản phẩm, hoặc bất kỳ chuỗi ký tự nào theo định dạng mong muốn chỉ trong vài giây.
Bài viết dưới đây sẽ cung cấp hướng dẫn chi tiết, đầy đủ và thực tế về cách thực hiện việc concatenate trong Excel với dấu phẩy, bao gồm:
- Giới thiệu tổng quan về hàm CONCATENATE và các hàm thay thế mới.
- Cách sử dụng hàm
CONCATENATE,&vàTEXTJOINđể ghép chuỗi có dấu phẩy. - Các ví dụ thực tế trong công việc hàng ngày.
- Xử lý các trường hợp đặc biệt: bỏ qua ô trống, loại bỏ dấu phẩy thừa, ghép nhiều cột, ghép dữ liệu từ nhiều sheet.
- Tối ưu hoá công thức bằng VBA (Macro) cho khối lượng dữ liệu lớn.
- Những lỗi thường gặp và cách khắc phục.
- Mẹo và thủ thuật nâng cao để tăng tốc công việc.
Lưu ý: Bài viết được viết bằng tiếng Việt, hướng tới người dùng ở mọi cấp độ – từ người mới bắt đầu tới các chuyên gia phân tích dữ liệu. Các ví dụ sẽ sử dụng các phiên bản Excel phổ biến (Excel 2026, 2026, Office 365) và cung cấp hướng dẫn cho cả Google Sheets nếu bạn làm việc trên môi trường đám mây.
Có thể bạn quan tâm: Cách Tạo Và Sử Dụng Biểu Đồ Cột Được Nhóm Và Xếp Chồng (clustered Stacked Column Chart) Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z
1. Tổng Quan Về CONCATENATE và Các Hàm Thay Thế
1.1. Hàm CONCATENATE (cũ)
Trước khi Excel 2026 ra mắt, hàm CONCATENATE là cách duy nhất để ghép các chuỗi lại với nhau. Cú pháp đơn giản:
=CONCATENATE(text1, text2, …)
- text1, text2, … là các chuỗi hoặc tham chiếu ô cần ghép.
- Không có giới hạn số lượng đối số (tối đa 255 đối số).
Ví dụ:
| A | B | C |
|---|---|---|
| John | Doe | =CONCATENATE(A2, ” “, B2) → “John Doe” |
1.2. Dấu & – Cách Viết Ngắn Gọn
Thay vì dùng hàm CONCATENATE, bạn có thể dùng toán tử & để ghép chuỗi:
=A2 & " " & B2
Cú pháp ngắn gọn, dễ đọc và linh hoạt hơn, đặc biệt khi bạn cần chèn dấu phân cách.
1.3. Hàm CONCAT (Excel 2026+)
Excel 2026 giới thiệu hàm CONCAT để thay thế CONCATENATE. Cú pháp:
=CONCAT(text1, text2, …)
Giống như CONCATENATE nhưng hỗ trợ đối tượng mảng (array) nên bạn có thể ghép toàn bộ một cột mà không cần lặp lại từng ô.
1.4. Hàm TEXTJOIN – Giải Pháp Hoàn Hảo Cho Dấu Phân Cách
TEXTJOIN là hàm mạnh mẽ nhất hiện nay để ghép chuỗi có dấu phân cách, đồng thời có tùy chọn bỏ qua ô trống. Cú pháp:
=TEXTJOIN(delimiter, ignore_empty, text1, text2, …)
- delimiter: Dấu phân cách (ví dụ: “, ”).
- ignore_empty: TRUE để bỏ qua ô trống, FALSE để giữ lại.
- text1, text2,…: Các chuỗi hoặc phạm vi cần ghép.
Ví dụ:
=TEXTJOIN(", ", TRUE, A2:C2)
Nếu A2 = “Apple”, B2 trống, C2 = “Banana”, kết quả sẽ là “Apple, Banana” (không có dấu phẩy thừa).
Có thể bạn quan tâm: Chức Năng Freeze Panes Trong Excel: Hướng Dẫn Toàn Diện Và Ứng Dụng Thực Tiễn
2. CÁCH SỬ DỤNG CÁC HÀM ĐỂ CONCATENATE VỚI DẤU PHÂN CÁM
2.1. Dùng CONCATENATE + Dấu Phẩy
=CONCATENATE(A2, ", ", B2, ", ", C2)
Nhược điểm: Phải liệt kê từng ô, không tự động bỏ qua ô trống.
2.2. Dùng & + Dấu Phẩy
=A2 & ", " & B2 & ", " & C2
Cũng tương tự CONCATENATE, nhanh hơn khi có ít ô.
2.3. Dùng TEXTJOIN – Giải Pháp Tối Ưu
=TEXTJOIN(", ", TRUE, A2:C2)
- TRUE → bỏ qua ô trống.
- A2:C2 → phạm vi ô cần ghép.
Nếu bạn muốn không bỏ qua ô trống (giữ dấu phẩy thừa), dùng FALSE:
=TEXTJOIN(", ", FALSE, A2:C2)
2.4. Ghép Nhiều Cột, Nhiều Dòng
Giả sử bạn có dữ liệu ở cột A, B, C và muốn tạo một chuỗi duy nhất cho mỗi hàng:
=TEXTJOIN(", ", TRUE, A2:C2)
Sau đó kéo công thức xuống để áp dụng cho các hàng khác.
Nếu muốn gộp toàn bộ cột A, có thể dùng:
=TEXTJOIN(", ", TRUE, A:A)
Lưu ý: Khi ghép toàn bộ cột, tránh dùng tới hàng cuối cùng (ví dụ A:A) nếu có hàng trống quá nhiều, vì Excel sẽ xử lý toàn bộ 1.048.576 ô, gây chậm. Thay vào đó, dùng phạm vi thực tế như
A2:A5000.
2.5. Ghép Dữ Liệu Từ Nhiều Sheet
Giả sử Sheet1 có cột A, Sheet2 có cột B, bạn muốn tạo danh sách chung:
=TEXTJOIN(", ", TRUE, Sheet1!A2:A100, Sheet2!B2:B100)
Bạn có thể thêm bao nhiêu phạm vi tùy ý, mỗi phạm vi cách nhau bằng dấu phẩy trong hàm.
Có thể bạn quan tâm: Chọn Ngẫu Nhiên Trong Excel: Hướng Dẫn Chi Tiết Và Ứng Dụng Thực Tế
3. ỨNG DỤNG THỰC TẾ TRONG CÔNG VIỆC
3.1. Tạo Danh Sách Email Từ Các Cột Họ, Tên, Domain
| A (Họ) | B (Tên) | C (Domain) |
|---|---|---|
| Nguyen | Anh | example.com |
| Trần | Bình | example.org |
Công thức:
=TEXTJOIN("@", FALSE, A2, B2) & "." & C2
Kết quả: Nguyen.Anh@example.com, Trần.Bình@example.org.
3.2. Ghép Địa Chỉ Giao Hàng
Có thể bạn quan tâm: Hướng Dẫn Chi Tiết Tạo Mẫu Hóa Đơn Thương Mại (commercial Invoice) Chuẩn Trong Excel
| A (Số nhà) | B (Đường) | C (Quận) | D (Thành phố) |
|---|---|---|---|
| 123 | Lê Lợi | 1 | Hà Nội |
| 45 | Nguyễn Trãi | 3 | TP.HCM |
Công thức:
=TEXTJOIN(", ", TRUE, A2, B2, C2, D2)
Kết quả: 123, Lê Lợi, 1, Hà Nội.
3.3. Tạo Mô Tả Sản Phẩm Từ Nhiều Thuộc Tính
| A (Tên) | B (Màu) | C (Kích thước) | D (Giá) |
|---|---|---|---|
| Áo | Đỏ | L | 250,000 |
| Quần | Xanh | M | 180,000 |
Công thức:
=TEXTJOIN(" - ", TRUE, A2, B2, C2, "Giá: " & D2 & " VND")
Kết quả: Áo - Đỏ - L - Giá: 250,000 VND.
4. XỬ LÝ TRƯỜNG HỢP ĐẶC BIỆT
4.1. Bỏ Qua Ô Trống (IGNORE_EMPTY)
TEXTJOIN cho phép bỏ qua ô trống bằng cách đặt tham số TRUE. Nếu bạn dùng CONCATENATE hoặc &, bạn cần điều kiện IF:
=IF(A2="", "", A2 & ", ") & IF(B2="", "", B2 & ", ") & IF(C2="", "", C2)
Nhưng công thức này dễ gây lỗi và khó bảo trì.
4.2. Loại Bỏ Dấu Phẩy Thừa Ở Đầu Hoặc Cuối
Khi dữ liệu có ô trống và bạn không dùng ignore_empty, có thể xuất hiện dấu phẩy thừa. Cách xử lý:
=TRIM(MID(SUBSTITUTE(TEXTJOIN(", ", FALSE, A2:C2), ", ", REPT(" ", 100)), 100, 1000))
Tuy nhiên, cách này phức tạp. Giải pháp tốt nhất là luôn dùng TEXTJOIN với TRUE.
4.3. Ghép Nhiều Cột Không Kế Tiếp
Nếu muốn ghép cột A và C (bỏ qua B), dùng:
=TEXTJOIN(", ", TRUE, CHOOSE({1,2}, A2:A10, C2:C10))
Hoặc dùng hàm FILTER (Office 365) để lọc:
=TEXTJOIN(", ", TRUE, FILTER(A2:C10, MOD(COLUMN(A2:C10),2)=1))
4.4. Ghép Dữ Liệu Động Với OFFSET
Nếu bạn có danh sách thay đổi kích thước, dùng OFFSET để tạo phạm vi động:
=TEXTJOIN(", ", TRUE, OFFSET(A2,0,0,COUNTA(A:A)-1,1))
4.5. Ghép Dữ Liệu Từ Bảng (Table)
Nếu dữ liệu được định dạng dưới dạng Table (Insert > Table), bạn có thể dùng structured references:
=TEXTJOIN(", ", TRUE, Table1Họ, Table1Tên)
Công thức sẽ tự cập nhật khi bảng mở rộng.
5. TỐI ƯU HÓA VỚI VBA (Macro) CHO DỮ LIỆU LỚN
Khi làm việc với hàng ngàn hay hàng triệu bản ghi, việc dùng công thức TEXTJOIN trên toàn cột có thể làm chậm workbook. Đối với trường hợp này, VBA là lựa chọn tốt hơn.
5.1. Macro Cơ Bản – Ghép Dòng Thành Chuỗi Với Dấu Phẩy
Sub ConcatenateWithComma() Dim ws As Worksheet Dim rng As Range, cell As Range Dim result As String Dim lastRow As Long Set ws = ThisWorkbook.Sheets("Sheet1") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'Giả sử dữ liệu ở cột A For Each cell In ws.Range("A2:A" & lastRow) If Len(cell.Value) > 0 Then result = result & cell.Value & ", " End If Next cell 'Xóa dấu phẩy cuối cùng If Len(result) > 0 Then result = Left(result, Len(result) - 2) ws.Range("B2").Value = result 'Kết quả ghi vào B2
End Sub
5.2. Macro Nâng Cao – Nối Nhiều Cột, Bỏ Qua Ô Trống
Sub ConcatenateMultiColumns() Dim ws As Worksheet Dim rng As Range, r As Long, c As Long Dim result As String Dim lastRow As Long, lastCol As Long Set ws = ThisWorkbook.Sheets("Data") lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column For r = 2 To lastRow result = "" For c = 1 To lastCol If Len(ws.Cells(r, c).Value) > 0 Then result = result & ws.Cells(r, c).Value & ", " End If Next c If Len(result) > 0 Then result = Left(result, Len(result) - 2) 'Bỏ dấu phẩy cuối End If ws.Cells(r, lastCol + 1).Value = result 'Ghi vào cột mới Next r
End Sub
Lưu ý: Khi chạy macro, luôn sao lưu file trước để tránh mất dữ liệu.
5.3. Tối Ưu Hóa Với Mảng (Array) – Nhanh Hơn 10 Lần
Sub FastConcatenate() Dim ws As Worksheet, outArr() As Variant Dim dataArr As Variant Dim r As Long, c As Long, lastRow As Long, lastCol As Long Dim tmp As String Set ws = ThisWorkbook.Sheets("Data") lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column dataArr = ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, lastCol)).Value ReDim outArr(1 To UBound(dataArr, 1), 1 To 1) For r = 1 To UBound(dataArr, 1) tmp = "" For c = 1 To UBound(dataArr, 2) If Len(dataArr(r, c)) > 0 Then tmp = tmp & dataArr(r, c) & ", " End If Next c If Len(tmp) > 0 Then tmp = Left(tmp, Len(tmp) - 2) outArr(r, 1) = tmp Next r ws.Range("Z2").Resize(UBound(outArr, 1), 1).Value = outArr 'Ghi kết quả cột Z
End Sub
Với cách này, đọc và ghi dữ liệu chỉ diễn ra một lần, giảm thiểu thời gian xử lý.
6. LỖI THƯỜNG GẶP KHI CONCATENATE VỚI DẤU PHÂN CÁM VÀ CÁCH KHẮC PHỤC
| Lỗi | Nguyên nhân | Cách khắc phục |
|---|---|---|
| #VALUE! | Tham chiếu tới ô chứa lỗi (ví dụ: #DIV/0!) | Kiểm tra và xử lý lỗi bằng IFERROR hoặc IFNA. |
| Dấu phẩy thừa ở đầu/cuối | Sử dụng CONCATENATE/& mà không bỏ qua ô trống | Dùng TEXTJOIN với TRUE hoặc thêm điều kiện IF(LEN(A2)>0, A2 & ", ", ""). |
| Công thức chậm | Áp dụng TEXTJOIN trên toàn cột (A:A) với hàng triệu ô | Giới hạn phạm vi, hoặc dùng VBA. |
| Kết quả tròn | Các ô chứa số và định dạng số | Sử dụng TEXT để ép kiểu: TEXT(A2, "0.00"). |
| Không nhận dạng dấu phẩy (đối với ngôn ngữ dùng dấu chấm) | Cài đặt ngôn ngữ/định dạng hệ thống | Thay dấu “,” bằng “;” trong công thức (ví dụ: TEXTJOIN("; ", TRUE, …) tùy locale). |
Ví dụ sử dụng IFERROR:
=IFERROR(TEXTJOIN(", ", TRUE, A2:C2), "")
Nếu có lỗi trong bất kỳ ô nào, công thức trả về chuỗi rỗng thay vì lỗi.
7. MẸO VÀ THỦ THUẬT NÂNG CAO
Sử dụng
TRIMđể loại bỏ khoảng trắng thừaexcel =TRIM(TEXTJOIN(", ", TRUE, A2:C2))Ghép dữ liệu với ký tự đặc biệt (ví dụ: dấu ngoặc <>):
excel =TEXTJOIN(", ", TRUE, "<" & A2 & ">", "<" & B2 & ">")Tạo danh sách dropdown tự động từ chuỗi đã ghép:
Dùng
Data Validation→List→ nhập công thức=TEXTSPLIT(F2, ", ")(Excel 365) để tách lại thành danh sách.Xây dựng công thức đa ngôn ngữ: nếu môi trường sử dụng dấu chấm phẩy
;làm phân cách, thay,trong công thức bằng;.excel =TEXTJOIN("; ", TRUE, A2:C2)Ghép dữ liệu trong Google Sheets: hàm tương đương là
JOINhoặcTEXTJOIN.gs =JOIN(", ", FILTER(A2:C2, LEN(A2:C2)>0))Sử dụng
LET(Excel 365) để giảm tính toán lặp:excel =LET(rng, A2:C2, TEXTJOIN(", ", TRUE, rng))Đánh dấu các phần tử trùng lặp trước khi ghép:
excel =TEXTJOIN(", ", TRUE, UNIQUE(FILTER(A2:A100, A2:A100<>"")))Ghép dữ liệu từ nhiều sheet dựa trên tiêu chí (ví dụ: chỉ ghép những ô có giá trị >0):
excel =TEXTJOIN(", ", TRUE, IF(Sheet1!A2:A50>0, Sheet1!A2:A50, ""), IF(Sheet2!B2:B50>0, Sheet2!B2:B50, ""))Sử dụng
LAMBDAđể tạo hàm tùy chỉnh (Excel 365):excel =LAMBDA(rng, delim, TEXTJOIN(delim, TRUE, rng))
Lưu lại dưới tênMyJoin, sau đó dùng:=MyJoin(A2:C2, ", ").
8. KẾT LUẬN
Việc concatenate trong Excel với dấu phẩy không chỉ là một thao tác đơn giản mà còn là công cụ mạnh mẽ giúp bạn tự động hoá quy trình xử lý dữ liệu, tạo báo cáo, và chuẩn hoá thông tin. Từ các hàm cơ bản như CONCATENATE và & cho tới TEXTJOIN hiện đại, và thậm chí là VBA cho khối lượng dữ liệu lớn, bạn luôn có giải pháp phù hợp.
Những điểm quan trọng cần nhớ:
- Sử dụng
TEXTJOINkhi có dấu phân cách và cần bỏ qua ô trống – đây là cách nhanh nhất và sạch sẽ nhất. - Tránh ghép toàn cột nếu không cần thiết; luôn giới hạn phạm vi để tối ưu hiệu suất.
- Khi dữ liệu quá lớn, hãy cân nhắc dùng macro VBA để giảm thời gian tính toán.
- Kiểm tra lỗi bằng
IFERRORhoặcIFNAđể tránh làm hỏng báo cáo. - Áp dụng các mẹo nâng cao như
LET,LAMBDA,UNIQUE,FILTERđể làm cho công thức ngắn gọn, mạnh mẽ và dễ bảo trì.
Hy vọng bài viết đã cung cấp cho bạn toàn bộ kiến thức cần thiết để thực hiện mọi yêu cầu ghép chuỗi có dấu phẩy trong Excel, bất kể quy mô hay độ phức tạp. Hãy thử áp dụng ngay vào công việc của mình và cảm nhận sự tiết kiệm thời gian cùng độ chính xác mà Excel mang lại. Chúc bạn thành công!
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ẻ.