Bảng nhân sự thường tách họ, tên đệm và tên ra 3 cột, còn bảng công nợ cần một câu nhắc nợ có đủ tên khách, số tiền và hạn trả. Bài viết này hướng dẫn bạn nối chuỗi trong Excel, tức là ghép chữ từ nhiều ô thành một ô, bằng dấu &, hàm CONCATENATE, CONCAT và TEXTJOIN.
Để nối họ ở ô A2, tên đệm ở ô B2 và tên ở ô C2 thành "Nguyễn Văn An", bạn gõ =A2&" "&B2&" "&C2 vào ô D2. Nếu có người không có tên đệm, bạn dùng =TEXTJOIN(" ",TRUE,A2:C2) để Excel bỏ qua ô trống, nhờ vậy "Lê Hoa" chỉ có một dấu cách ở giữa. Khi ghép số tiền ở ô B2 của bảng công nợ vào câu, bạn bọc ô đó bằng TEXT(B2,"#,##0") để câu hiện 12,500,000 thay vì 12500000.
Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh. Các ô màu vàng nhạt là nơi bạn gõ công thức. Excel 365 dùng trong bài cài dấu phẩy ngăn cách hàng nghìn và ngày dạng ngày/tháng/năm. Nếu Excel của bạn báo lỗi khi gõ công thức có dấu phẩy, bạn thay dấu phẩy ngăn tham số bằng dấu chấm phẩy, ví dụ =TEXTJOIN(" ";TRUE;A2:C2).
Bốn cách nối chuỗi trong Excel khác nhau ở điểm nào?
Cách nối | Phiên bản Excel | Nối cả vùng | Tự thêm dấu phân cách | Bỏ qua ô trống |
|---|---|---|---|---|
Dấu | Mọi phiên bản | Không | Không | Không |
Hàm CONCATENATE | Mọi phiên bản | Không | Không | Không |
Hàm CONCAT | Excel 2019 trở lên, Microsoft 365 | Có | Không | Không |
Hàm TEXTJOIN | Excel 2019 trở lên, Microsoft 365 | Có | Có | Có |
Dấu & và hàm CONCATENATE cho kết quả giống nhau, nên bạn chọn cách nào gõ nhanh hơn. Hàm CONCAT là bản mới của CONCATENATE và nhận được cả một vùng ô. Hàm TEXTJOIN làm được nhiều việc nhất, vì hàm tự đặt dấu phân cách giữa các phần và bỏ qua ô trống. Excel 2016 trở về trước không có CONCAT và TEXTJOIN, trừ khi bạn dùng Excel qua gói Microsoft 365.
Bạn nối họ, tên đệm và tên bằng dấu & như thế nào?
Bảng ví dụ có họ ở cột A, tên đệm ở cột B và tên ở cột C. Hai người ở dòng 4 và dòng 6 không có tên đệm.
Bạn chọn ô D2, gõ công thức sau rồi nhấn Enter:
=A2&" "&B2&" "&C2

Dấu & nối các phần theo đúng thứ tự bạn viết. Chữ hoặc dấu cách bạn tự thêm phải nằm trong ngoặc kép, còn địa chỉ ô thì viết trực tiếp. Nếu bạn viết =A2&B2&C2, kết quả là "NguyễnVănAn" vì công thức không có dấu cách nào.
Tiếp theo, bạn đưa chuột tới chấm vuông ở góc dưới bên phải ô D2, khi con trỏ thành dấu cộng đen mảnh thì kéo xuống ô D6.

Ô D4 là "Lê Hoa" với 2 dấu cách, vì công thức vẫn thêm dấu cách cho ô B4 dù ô này trống. Bạn kiểm tra bằng hàm LEN, là hàm đếm số ký tự: bạn gõ =LEN(D4) vào ô F4 thì kết quả là 7, trong khi "Lê Hoa" chỉ có 6 ký tự. Dấu cách thừa làm hàm tra cứu như VLOOKUP hoặc COUNTIF không khớp với "Lê Hoa" ở bảng khác.
Hàm CONCATENATE cho kết quả giống dấu &. Nếu bạn gõ =CONCATENATE(A2," ",B2," ",C2) vào ô F2, ô này cũng trả về "Nguyễn Văn An".
Bạn dùng TEXTJOIN để nối và bỏ qua ô trống như thế nào?
Hàm TEXTJOIN nối cả một vùng ô và tự đặt dấu phân cách giữa các phần.
=TEXTJOIN(delimiter, ignore_empty, text1, ...)
Tham số | Ý nghĩa |
|---|---|
delimiter | Tham số này là dấu phân cách đặt giữa các phần, viết trong ngoặc kép, ví dụ |
ignore_empty | Tham số này bằng TRUE thì hàm bỏ qua ô trống, bằng FALSE thì hàm vẫn đặt dấu phân cách cho ô trống. |
text1, ... | Tham số này là ô hoặc vùng cần nối. Bạn đưa vào được tối đa 252 ô hoặc vùng. |
Bạn chọn ô E2, gõ công thức sau rồi nhấn Enter:
=TEXTJOIN(" ",TRUE,A2:C2)

Sau đó, bạn kéo công thức từ ô E2 xuống ô E6.

Lúc này, nếu bạn gõ =LEN(E4) vào ô G4, kết quả là 6. Nếu bạn đổi TRUE thành FALSE trong công thức ở cột E, ô E4 cũng ra "Lê Hoa" có 2 dấu cách như dấu &.
Nếu Excel của bạn không có TEXTJOIN, bạn bọc công thức dấu & bằng hàm TRIM, là hàm xóa dấu cách thừa. Bạn gõ =TRIM(A2&" "&B2&" "&C2) vào ô E2 thay cho công thức TEXTJOIN rồi kéo xuống ô E6, và ô E4 cũng trả về "Lê Hoa".
Bạn nối cả một vùng bằng hàm CONCAT như thế nào?
Hàm CONCAT có cú pháp =CONCAT(text1, ...) và nhận cả một vùng ô, nên bạn không cần gõ từng ô như CONCATENATE. Hàm này không thêm dấu phân cách, nên CONCAT hợp với việc ghép mã từ nhiều phần.
Bảng ví dụ có chi nhánh ở cột A, nhóm hàng ở cột B và số thứ tự ở cột C. Bạn cần ghép thành mã hàng như "HNLAP0125".
Bạn chọn ô D2, gõ công thức sau rồi nhấn Enter:
=CONCAT(A2:C2)


Kết quả là "HNLAP125" chứ không phải "HNLAP0125". Ô C2 thật ra chứa số 125, còn định dạng ô 0000 chỉ làm số hiện thành 0125. Khi nối chuỗi, Excel lấy giá trị thật của ô và bỏ qua định dạng.
Để giữ đủ 4 chữ số, bạn dùng hàm TEXT đổi số thành chữ theo mẫu "0000". Bạn chọn lại ô D2, gõ công thức sau rồi nhấn Enter:
=CONCAT(A2:B2,TEXT(C2,"0000"))

Sau đó, bạn kéo công thức từ ô D2 xuống ô D4.

Nếu muốn mã có dấu gạch ngang như "HN-LAP-0125", bạn gõ =TEXTJOIN("-",TRUE,A2:B2,TEXT(C2,"0000")) vào ô E2.
Bạn lưu ý rằng CONCATENATE không nối được vùng. Nếu bạn gõ =CONCATENATE(A2:C2) vào ô F2 trên Excel 365, hàm trả về 3 giá trị riêng HN, LAP và 125 ở 3 ô F2, G2 và H2. Khi một trong các ô này đã có dữ liệu, Excel báo lỗi #SPILL!.
Bạn ghép số tiền hoặc ngày vào câu như thế nào?
Bảng công nợ có tên khách ở cột A, số tiền ở cột B và hạn thanh toán ở cột C. Bạn cần viết câu nhắc nợ ở cột D.
Bạn chọn ô D2, gõ công thức sau rồi nhấn Enter:
=A2&" còn nợ "&B2&" đồng, hạn "&C2


Excel lưu ngày dưới dạng số thứ tự tính từ ngày 1/1/1900, và ngày 31/10/2026 là số 46326. Ô C2 hiện 31/10/2026 nhờ định dạng ô, nhưng dấu & lấy giá trị thật là 46326. Ô số tiền cũng vậy, nên dấu phân cách hàng nghìn biến mất.
Bạn bọc ô số tiền và ô ngày bằng hàm TEXT để giữ cách hiển thị. Bạn chọn lại ô D2, gõ công thức sau rồi nhấn Enter:
=A2&" còn nợ "&TEXT(B2,"#,##0")&" đồng, hạn "&TEXT(C2,"dd/mm/yyyy")

Sau đó, bạn kéo công thức từ ô D2 xuống ô D4.

Mẫu "#,##0" viết theo máy dùng dấu phẩy ngăn cách hàng nghìn. Nếu máy của bạn dùng dấu chấm ngăn cách hàng nghìn, bạn viết TEXT(B2,"#.##0") thì mới ra 12.500.000. Bài chuyển số thành text trong Excel bằng hàm TEXT giải thích kỹ các mẫu định dạng số và ngày.
Bạn xuống dòng trong chuỗi ghép bằng CHAR(10) như thế nào?
Hàm CHAR(10) trả về ký tự xuống dòng. Bạn đặt CHAR(10) vào giữa các phần khi muốn mỗi phần nằm trên một dòng trong cùng một ô, ví dụ nhãn giao hàng có tên, số điện thoại và địa chỉ.
Bảng ví dụ có người nhận ở cột A, số điện thoại ở cột B và địa chỉ ở cột C. Bạn chọn ô D2, gõ công thức sau rồi nhấn Enter:
="Người nhận: "&A2&CHAR(10)&"SĐT: "&B2&CHAR(10)&"Địa chỉ: "&C2

Sau đó, bạn kéo công thức từ ô D2 xuống ô D4.

Công thức đã chèn ký tự xuống dòng, nhưng Excel chỉ hiện nhiều dòng khi ô bật Wrap Text, tức chế độ cho phép chữ xuống dòng trong ô. Bạn quét chọn D2:D4, vào thẻ Home rồi bấm nút Wrap Text trong nhóm Alignment. Khi cửa sổ Excel hẹp, nút này chỉ hiện biểu tượng chữ "ab" có mũi tên vòng xuống, và Excel hiện tên Wrap Text khi bạn rê chuột vào.


Nếu bạn cần nối cả vùng và bỏ qua ô trống, bạn dùng CHAR(10) làm dấu phân cách của TEXTJOIN. Bạn gõ =TEXTJOIN(CHAR(10),TRUE,A2:C2) vào ô E2 rồi bật Wrap Text cho ô này, và tên, số điện thoại, địa chỉ mỗi phần bắt đầu ở một dòng mới. Bài cách xuống dòng trong 1 ô Excel hướng dẫn thêm Alt + Enter và cách bỏ ký tự xuống dòng.
Bạn gộp danh sách theo điều kiện bằng TEXTJOIN như thế nào?
Bảng nhân viên có họ tên ở vùng A2:A10 và chi nhánh ở vùng B2:B10. Bạn cần liệt kê nhân viên của từng chi nhánh vào một ô, cách nhau bằng dấu phẩy. Cách này chỉ chạy trên Excel 2019 trở lên và Microsoft 365.
Bạn chọn ô E2, gõ công thức sau rồi nhấn Enter:
=TEXTJOIN(", ",TRUE,IF($B$2:$B$10=D2,$A$2:$A$10,""))

Công thức này chạy theo 2 bước:
Hàm IF so từng ô trong
$B$2:$B$10với chi nhánh ở ôD2. Dòng nào khớp thì hàm lấy họ tên ở cột A, dòng nào không khớp thì hàm trả về chuỗi rỗng"".Hàm TEXTJOIN nối các kết quả bằng dấu phẩy và dấu cách. Tham số TRUE bỏ qua các chuỗi rỗng, nên danh sách không có dấu phẩy thừa.
Dấu $ giữ cố định 2 vùng khi bạn kéo công thức từ ô E2 xuống ô E5.

Nếu chỉ cần một chi nhánh, bạn gõ thẳng tên chi nhánh vào công thức. Ví dụ, bạn gõ =TEXTJOIN(", ",TRUE,IF(B2:B10="Hà Nội",A2:A10,"")) vào ô G2, và ô này trả về "Nguyễn Văn An, Lê Hoa, Đỗ Khang, Hoàng Lan". Phép so sánh không phân biệt chữ hoa và chữ thường, nên ô ghi "hà nội" vẫn được tính.
Trên Excel 2019, bạn phải nhấn Ctrl + Shift + Enter thay vì Enter để công thức chạy như công thức mảng. Nếu chỉ nhấn Enter, ô E2 trả về cả 9 nhân viên thay vì 4 người ở Hà Nội. Trên Microsoft 365, bạn chỉ cần nhấn Enter và có thể dùng hàm FILTER thay cho IF. Bạn gõ =TEXTJOIN(", ",TRUE,FILTER($A$2:$A$10,$B$2:$B$10=D2,"")) vào ô E2 thay cho công thức cũ rồi kéo xuống ô E5, và kết quả giống hệt cách dùng IF. Tham số cuối "" của FILTER giúp ô Cần Thơ để trống thay vì báo lỗi #CALC!.
Bạn hay gặp lỗi gì khi nối chuỗi trong Excel?
Lỗi | Nguyên nhân | Cách sửa |
|---|---|---|
Kết quả dính liền như "NguyễnVănAn" | Công thức không có dấu cách giữa các phần, ví dụ | Bạn thêm |
Có 2 dấu cách giữa họ và tên | Ô tên đệm trống nhưng công thức dấu & vẫn thêm dấu cách cho ô đó. | Bạn dùng |
Ngày thành 46326, số tiền mất dấu phân cách, mã mất số 0 ở đầu | Phép nối lấy giá trị thật của ô và bỏ định dạng ô. | Bạn bọc ô bằng TEXT, ví dụ |
Hàm SUM cộng cột kết quả ra 0 | Kết quả nối luôn là chữ, kể cả khi trông như số. Ví dụ, bạn gõ | Bạn giữ số ở một cột riêng để tính toán, hoặc gõ |
Công thức có CHAR(10) vẫn hiện trên một dòng | Ô chưa bật Wrap Text. | Bạn chọn ô rồi bấm Home > Wrap Text. |
Ô báo lỗi | Excel 2016 trở về trước không có hàm CONCAT và TEXTJOIN. | Bạn dùng dấu & hoặc CONCATENATE. |
TEXTJOIN báo lỗi | Kết quả dài hơn 32.767 ký tự, là giới hạn số ký tự của một ô. Trên Excel 365, hàm báo lỗi | Bạn chia vùng cần nối thành nhiều ô kết quả. |
Danh sách theo chi nhánh thiếu người | Ô chi nhánh có dấu cách thừa, ví dụ "Hà Nội " có dấu cách ở cuối, nên không khớp với "Hà Nội". | Bạn dùng |
Phép nhân vẫn tính được trên kết quả nối. Ví dụ, ô G2 đang chứa chữ "1025", và bạn gõ =G2*2 vào ô I2 thì kết quả là 2050, vì Excel tự đổi chữ thành số trong phép tính. Hàm SUM thì không tự đổi, nên bạn dễ nhầm khi cộng cả cột.
Nếu bạn muốn tìm hiểu thêm các hàm cắt, tìm và làm sạch chữ, bạn có thể đọc bài 9 hàm xử lý chuỗi trong Excel.
Bạn muốn học Excel bài bản hơn?
Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.
Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.