Có thể bạn quan tâm: Cách Tính Căn Bậc 2 Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
Giới thiệu chung về hàm nối chuỗi
Trong môi trường làm việc với dữ liệu, Excel luôn là công cụ không thể thiếu đối với hầu hết các chuyên gia, nhân viên văn phòng và nhà phân tích. Một trong những thao tác thường gặp và quan trọng nhất là việc nối các đoạn văn bản, số liệu, hoặc ký tự lại với nhau để tạo ra một chuỗi thông tin hoàn chỉnh. Để thực hiện việc này, Excel cung cấp một loạt các hàm và phương pháp, trong đó hàm nối chuỗi (concatenation) đóng vai trò trung tâm.
Tại sao chúng ta cần nối chuỗi?
- Tổng hợp dữ liệu: Khi dữ liệu được lưu trữ ở nhiều cột khác nhau (ví dụ: họ, tên, địa chỉ), việc nối chuỗi giúp chúng ta tạo ra một cột duy nhất chứa đầy đủ thông tin.
- Tạo mã định danh: Nối các trường như năm, tháng, số thứ tự để tạo ra mã đơn hàng, mã khách hàng.
- Chuẩn hoá dữ liệu: Khi cần chuẩn hoá định dạng (ví dụ: thêm dấu gạch ngang vào số điện thoại), hàm nối chuỗi giúp thực hiện nhanh chóng.
- Tự động tạo email, URL: Dựa trên tên và họ, chúng ta có thể tự động tạo địa chỉ email hoặc liên kết website.
Trong phần tiếp theo, chúng ta sẽ đi sâu vào các hàm nối chuỗi trong Excel, cách sử dụng từng hàm, các lưu ý quan trọng và một số ví dụ thực tế.
Có thể bạn quan tâm: Chọn Vùng In Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
1. Các hàm nối chuỗi cơ bản trong Excel
1.1. Hàm CONCATENATE
Đây là hàm truyền thống, được giới thiệu từ các phiên bản Excel rất sớm. Cú pháp:
CONCATENATE(text1, text2, …)
- text1, text2, …: Các đối số có thể là chuỗi ký tự, tham chiếu ô, hoặc kết quả của các công thức khác.
- Hàm này cho phép nối tối đa 255 đối số, mỗi đối số có độ dài tối đa 32.767 ký tự.
Ví dụ:
| A | B | C |
|---|---|---|
| Nguyễn | Văn A | =CONCATENATE(A2, ” “, B2) → “Nguyễn Văn A” |
1.2. Toán tử &
Toán tử & là cách ngắn gọn, nhanh chóng nhất để nối chuỗi. Cú pháp:
text1 & text2 & text3 …
- Không giới hạn số lượng đối số, nhưng tổng độ dài vẫn bị giới hạn bởi Excel (32.767 ký tự).
Ví dụ:
=A2 & " " & B2
Kết quả giống như hàm CONCATENATE.
1.3. Hàm TEXTJOIN (Excel 2026 trở lên)
TEXTJOIN là hàm mạnh mẽ, cho phép tự động chèn ký tự phân cách và bỏ qua các ô trống. Cú pháp:
TEXTJOIN(delimiter, ignore_empty, text1, text2, …)
- delimiter: Ký tự hoặc chuỗi ký tự sẽ được chèn giữa các phần tử (ví dụ: “, “, ” – “).
- ignore_empty: TRUE để bỏ qua các ô trống, FALSE để giữ lại.
- text1, text2, …: Các chuỗi, ô tham chiếu, hoặc mảng.
Ví dụ:
=TEXTJOIN(", ", TRUE, A2:C2)
Nếu A2 = “Nguyễn”, B2 = “”, C2 = “A”, kết quả sẽ là “Nguyễn, A”.
1.4. Hàm CONCAT (Excel 2026 trở lên)
CONCAT là phiên bản rút gọn của CONCATENATE, hỗ trợ mảng và không yêu cầu liệt kê từng đối số. Cú pháp:
CONCAT(text1, text2, …)
- Khi truyền vào một dải ô,
CONCATsẽ tự động nối tất cả các giá trị trong dải, không chèn ký tự phân cách và không bỏ qua ô trống.
Ví dụ:
=CONCAT(A2:C2)
Nếu A2 = “Nguyễn”, B2 = “”, C2 = “A”, kết quả sẽ là “NguyễnA”.
Có thể bạn quan tâm: Cách Chèn File Pdf Vào Excel: Hướng Dẫn Chi Tiết Từ A Đến Z
2. Khi nào nên dùng hàm nào?
| Trường hợp | Hàm gợi ý | Lý do |
|---|---|---|
| Nối một vài ô đơn giản | & hoặc CONCATENATE | Ngắn gọn, dễ nhớ |
| Cần chèn ký tự phân cách (dấu phẩy, dấu gạch ngang…) | TEXTJOIN | Tự động chèn delimiter, bỏ qua ô trống |
| Nối toàn bộ dải ô mà không cần ký tự phân cách | CONCAT | Hỗ trợ mảng, nhanh hơn CONCATENATE |
| Phiên bản Excel cũ (trước 2026) | CONCATENATE hoặc & | Các hàm mới không khả dụng |
3. Các kỹ thuật nâng cao với hàm nối chuỗi
3.1. Kết hợp TEXTJOIN với các hàm mảng
Với Excel 365 hoặc Excel 2026, chúng ta có thể sử dụng công thức mảng động để thao tác trên nhiều hàng cùng lúc.
Ví dụ: Nối họ và tên cho toàn bộ danh sách trong cột A và B, bỏ qua những ô trống, và chèn dấu cách giữa chúng.
=TEXTJOIN(" ", TRUE, FILTER(A2:A100 & " " & B2:B100, (A2:A100<>"") + (B2:B100<>"")))
FILTERlọc ra những hàng có ít nhất một trong hai ô không trống.&nối họ và tên trong mỗi hàng.TEXTJOINcuối cùng gộp các chuỗi lại thành một danh sách dài, mỗi tên cách nhau bằng dấu cách.
3.2. Định dạng số và ngày tháng khi nối
Khi nối một giá trị số hoặc ngày tháng, Excel sẽ tự động chuyển chúng thành chuỗi, nhưng định dạng có thể không như mong muốn. Để kiểm soát, sử dụng hàm TEXT.
Cú pháp:
TEXT(value, format_text)
Ví dụ: Nối ngày sinh và tên để tạo tiêu đề báo cáo.
="Báo cáo ngày " & TEXT(C2, "dd/mm/yyyy") & " - " & A2 & " " & B2
Nếu C2 là ngày 01/01/2026, kết quả sẽ là: Báo cáo ngày 01/01/2026 - Nguyễn Văn A.
3.3. Xử lý ký tự đặc biệt và loại bỏ khoảng trắng thừa
Có thể bạn quan tâm: Cách Lọc Dữ Liệu Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
- Xóa khoảng trắng ở đầu và cuối:
TRIM(text). - Thay thế ký tự:
SUBSTITUTE(text, old_text, new_text, instance_num).
Ví dụ: Tạo mã khách hàng bằng cách nối tên, họ, và năm sinh, đồng thời loại bỏ dấu cách.
=SUBSTITUTE(TRIM(A2) & TRIM(B2) & TEXT(C2,"yyyy"), " ", "")
3.4. Tạo chuỗi động dựa trên điều kiện
Kết hợp IF và TEXTJOIN để chỉ nối các giá trị thỏa mãn điều kiện.
Ví dụ: Trong một bảng bán hàng, chỉ muốn liệt kê các sản phẩm đã bán (số lượng > 0) và ngăn cách bằng dấu phẩy.
=TEXTJOIN(", ", TRUE, IF(D2:D20>0, E2:E20, ""))
Khi nhập công thức này, nhấn Ctrl+Shift+Enter (trong Excel phiên bản không hỗ trợ mảng động) để biến thành công thức mảng.
4. Ứng dụng thực tế
4.1. Tạo địa chỉ email tự động
Giả sử cột A chứa họ, cột B chứa tên, và chúng ta muốn tạo email dạng ho.ten@congty.com.
=LOWER(CONCATENATE(A2, ".", B2, "@congty.com"))
Hoặc dùng TEXTJOIN:
=LOWER(TEXTJOIN(".", TRUE, A2, B2) & "@congty.com")
4.2. Tạo mã sản phẩm
Mã sản phẩm có cấu trúc: CATEGORY-YYMM-SEQ, trong đó:
CATEGORYlấy từ cột A.YYMMlà năm và tháng hiện tại (TEXT(NOW(),"yymm")).SEQlà số thứ tự trong danh sách (dòng hiện tại).
Công thức:
=CONCAT(A2, "-", TEXT(NOW(),"yymm"), "-", TEXT(ROW()-1,"000"))
Nếu A2 = “ELEC”, và công thức nằm ở dòng 2, kết quả sẽ là ELEC-2606-001 (giả sử ngày hiện tại là 2026-06).
4.3. Tổng hợp danh sách khách hàng trong một ô
Trong một báo cáo, chúng ta muốn liệt kê tất cả khách hàng trong một khu vực (cột D) vào một ô, mỗi tên cách nhau bằng dấu chấm phẩy.
=TEXTJOIN("; ", TRUE, IF(D2:D100="Hà Nội", A2:A100 & " " & B2:B100, ""))
Sau khi nhập công thức, nhấn Ctrl+Shift+Enter nếu Excel của bạn chưa hỗ trợ mảng động.
4.4. Định dạng số tiền khi nối
Khi muốn hiển thị số tiền kèm ký hiệu tiền tệ trong một câu:
="Doanh thu tháng này là " & TEXT(E2, "$#,##0.00")
Nếu E2 = 1234567, kết quả: Doanh thu tháng này là $1,234,567.00.
5. Những lưu ý và lỗi thường gặp
| Lỗi | Nguyên nhân | Cách khắc phục |
|---|---|---|
| #VALUE! | Khi một trong các đối số không phải là chuỗi, số, hoặc ngày hợp lệ (ví dụ: lỗi #N/A). | Kiểm tra dữ liệu, dùng IFERROR hoặc IFNA để xử lý lỗi trước khi nối. |
| Ký tự phân cách không xuất hiện | Dùng CONCAT hoặc & mà quên chèn delimiter. | Sử dụng TEXTJOIN nếu cần delimiter tự động. |
| Khoảng trắng thừa | Dữ liệu nguồn có khoảng trắng ở đầu/cuối. | Dùng TRIM để loại bỏ. |
| Độ dài chuỗi vượt quá 32.767 ký tự | Khi nối quá nhiều dữ liệu, độ dài tổng vượt giới hạn. | Không thể vượt qua giới hạn này; cần chia thành nhiều ô hoặc tóm tắt dữ liệu. |
| Không hiển thị ký tự Unicode | Phiên bản Excel cũ hoặc định dạng ô không hỗ trợ. | Cập nhật Excel, hoặc chuyển sang định dạng Unicode (UTF-8) nếu cần. |
6. Thủ thuật tối ưu hiệu suất
- Tránh lặp lại công thức trong hàng lớn: Khi cần áp dụng công thức cho hàng ngàn, hãy dùng định dạng có công thức (Table) và điền tự động để giảm tải tính toán.
- Sử dụng
LET(Excel 2026/365) để lưu trữ giá trị trung gian, giảm số lần tính toán lại:
=LET( ho, TRIM(A2), ten, TRIM(B2), email, LOWER(ho & "." & ten & "@congty.com"), email
)
- Giảm việc sử dụng công thức mảng vô hạn: Chỉ áp dụng mảng cho phạm vi cần thiết, tránh toàn bộ cột (A:A) vì sẽ làm chậm Excel.
7. Tổng kết
Hàm nối chuỗi trong Excel không chỉ là một công cụ đơn giản để ghép các đoạn văn bản lại với nhau, mà còn là nền tảng để xây dựng các giải pháp tự động hoá, chuẩn hoá dữ liệu và tạo ra các mã định danh phức tạp. Từ các hàm truyền thống như CONCATENATE và toán tử &, đến những công cụ mạnh mẽ hơn như TEXTJOIN và CONCAT, mỗi hàm đều có ưu và nhược điểm riêng, phù hợp với những tình huống khác nhau.
Việc nắm vững cách kết hợp các hàm phụ trợ như TEXT, TRIM, SUBSTITUTE, IF, FILTER, LET sẽ giúp bạn:
- Tiết kiệm thời gian: Tự động tạo email, mã sản phẩm, URL mà không cần nhập tay.
- Giảm lỗi: Xử lý dữ liệu không đồng nhất, loại bỏ khoảng trắng và ký tự không mong muốn.
- Nâng cao tính linh hoạt: Thay đổi nhanh định dạng ngày, số, hoặc ký tự phân cách mà không thay đổi cấu trúc chính.
Cuối cùng, hãy luôn nhớ rằng Excel không ngừng phát triển. Các phiên bản mới (Office 365, Excel 2026) liên tục bổ sung hàm mới và cải thiện khả năng xử lý mảng, giúp việc nối chuỗi trở nên nhanh chóng và thông minh hơn. Hãy áp dụng những kiến thức trên vào công việc thực tế của bạn và khám phá thêm nhiều cách sáng tạo để tối ưu hoá quy trình làm việc với dữ liệu. 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ẻ.