.st0{fill:#FFFFFF;}

Hướng dẫn cách dùng hàm CONCATENATE để nối chuỗi, nối ô trong Excel 

 Tháng Chín 19, 2021

By  Lê Đình Chi

Làm thế nào để nối văn bản ở nhiều ô vào thành 1 chuỗi văn bản trong 1 ô? Kỹ thuật nối văn bản như thế nào để kết quả đúng như ý muốn? Đó là những thắc mắc của rất nhiều người khi sử dụng Excel. Trong bài viết này chúng ta cùng Zik.vn tìm hiểu về hàm CONCATENATE, một hàm chuyên dùng để nối chuỗi trong Excel nhé.

Phân biệt giữa Trộn ô với Nối chuỗi

Thông thường trong bảng tính Excel có hai cách để kết hợp dữ liệu:

  • Hợp nhất các ô (hay còn gọi là trộn ô, gộp ô). Việc này là bạn sẽ biến nhiều ô riêng lẻ thành 1 ô duy nhất. Bạn có thể thực hiện điều này với chức năng Merge Cells.
  • Kết nối các giá trị của ô (hay còn gọi là nối chuỗi). Việc này là bạn sẽ nối giá trị (văn bản) tại nhiều ô thành 1 đoạn văn bản trên 1 ô duy nhất. Bạn có thể thực hiện điều này với các hàm nối chuỗi trong Excel.

Hình sau đây thể hiện sự khác biệt giữa hai phương pháp này:

phân biệt chức năng trộn ô với nối chuỗi

Trong bài viết này, chúng ta sẽ tìm hiểu rõ hơn về kỹ thuật nối chuỗi trong Excel sử dụng các hàm, các ký tự nối.

Hàm CONCATENATE để nối chuỗi, nối ô trong Excel

Trong các phiên bản Excel từ 2010 trở về trước, chúng ta có hàm CONCATENATE, còn ở các phiên bản mới hơn (từ 2013 trở đi), Excel đã cho phép bạn sử dụng 1 hàm tương tự với tên rút gọn là hàm CONCAT.

Mục đích của hàm này là dùng để nối các đoạn văn bản trong nhiều ô vào 1 ô duy nhất.

Cú pháp của hàm như sau:

=CONCATENATE(text1, [text2], …)

hoặc

=CONCAT(text1[text2], …)

Trong đó text1, text2, …  là các đoạn văn bản riêng lẻ trong nhiều ô, hoặc nhiều đoạn văn bản khác nhau cần được nối lại.

Để hiểu rõ hơn về cách sử dụng hàm nối chuỗi, chúng ta xét một số ví dụ sau đây:

Ví dụ 1: nối giá trị trong 2 cột họ và tên riêng lẻ thành 1 cột duy nhất

bài tập nối họ tên thành 1 cột duy nhất với hàm concat

Để làm được yêu cầu này, thông thường bạn sẽ nghĩ sử dụng hàm CONCAT (CONCATENATE) theo dạng:

=CONCAT(A2,B2)

Tức là chỉ dùng 2 ô A2 và B2 làm tham số cần nối. Nhưng như vậy kết quả sẽ là:

kết quả hàm concat nối họ tên chưa có dấu cách

Bạn sẽ thấy các đoạn text trong 2 ô A2 và B2 đã nối với nhau và cho ra kết quả như tại ô C2. Nhưng phần tên lại viết sát với tên đệm (chỗ mũi tên màu xanh đang chỉ vào). Điều này khiến cách viết Họ và tên chưa chính xác. Đúng ra là phải có thêm 1 dấu cách ở vị trí này để ngăn cách giữa phần Tên với Tên đệm.

Dấu cách không tự động sinh ra khi bạn dùng hàm CONCAT (CONCATENATE). Muốn có nó, bạn phải nối thêm dấu đó vào, coi nó như 1 tham số cần nối. Lưu ý đặt đúng vị trí ở giữa 2 ô A2, B2 như sau:

=CONCAT(A2,” “,B2)

Lúc này kết quả là:

kết quả hàm concat nối họ tên có dấu cách

Ví dụ 2: Nối kết quả của các công thức tính

Trong một số trường hợp giá trị bạn cần nối lại là kết quả của 1 công thức tính, vậy điều này sẽ thể hiện ra sao khi dùng hàm CONCAT? hãy cùng xem ví dụ dưới đây:

bài tập dùng hàm concat nối đoạn text với công thức

Trong yêu cầu trên chúng ta có:

  • Đầu tiên phải xác định ngày hết hạn dựa vào Loại hợp đồng và Ngày ký.
  • Sau đó sử dụng hàm nối chuỗi để nối kết quả ngày hết hạn với chữ Ngày ở trước

Các bước thực hiện như sau:

Bước 1: Xác định ngày hết hạn

Chúng ta có công thức xác định ngày hết hạn là:

Ngày hết hạn =DATE(Năm của ngày ký + thời hạn số năm, Tháng của ngày ký + thời hạn số tháng, Ngày ký + thời hạn số ngày)

Để viết công thức này dễ hơn, bạn có thể tạo thêm 3 cột thể hiện các giá trị số năm, số tháng, số ngày của loại hợp đồng. Khi đó ta có kết quả như hình sau:

công thức xác định ngày hết hạn hợp đồng trong excel

Hàm sử dụng là các hàm DATE, YEAR, MONTH, DAY:

  • Hàm YEAR, MONTH, DAY để tách ra các giá trị số năm, số tháng, số ngày từ giá trị ngày ký. Sau đó cộng thêm các thời hạn tương ứng theo Năm, tháng, ngày.
  • Hàm DATE tạo ra giá trị ngày tháng trong 1 ô dựa trên các tham số Năm, tháng, ngày (viết đúng thứ tự)

ô F2 =DATE(YEAR(E2)+B2, MONTH(E2)+C2, DAY(E2)+D2)

Tham khảo: Hướng dẫn cách xác định ngày hết hạn hợp đồng trên Excel

Bước 2: Sử dụng hàm CONCATENATE để nối thêm ký tự Ngày vào kết quả:

=CONCATENATE(“Ngày “, DATE(….))

Chữ Ngày và dấu cách hoàn toàn có thể viết liền nhau và phải đặt trong cặp dấu nháy kép. Đây là nguyên tắc của Excel khi bạn muốn viết trực tiếp một giá trị dạng văn bản trong hàm.

Kết quả là:

cách viết hàm concatenate nối đoạn text với công thức

Lúc này kết quả hàm DATE lại trả về dưới dạng một con số chứ không phải là dạng ngày/tháng/năm. Bởi khi nối 1 hàm với 1 đoạn text, kết quả sẽ trả về là kết quả của hàm nhưng không kèm theo định dạng (Số 43634 là định dạng gốc, dạng General của ngày 18/06/2019). Hàm CONCATENATE cho ta kết quả là dạng text, do đó nó không thể định dạng bằng Format Cells được.
Để giữ được định dạng ngày tháng trong đoạn văn bản này, bạn phải sử dụng hàm TEXT như sau:

=CONCATENATE(“Ngày “, TEXT(DATE(….),”dd/MM/yyyy”))

Kết quả thu được là:

cách viết hàm concatenate nối đoạn text với ngày giữ nguyên định dạng

Những chú ý khi dùng hàm CONCATENATE / CONCAT

Khi sử dụng hàm này, bạn hãy chú ý một số nội dung sau để giúp kết quả được chính xác hơn:

  • Hàm yêu cầu ít nhất có một tham số: ít nhất là 1 ô hoặc 1 đoạn text cụ thể
  • Bạn có thể ghép tối đa 255 tham số trong hàm, tổng cộng là 8,192 ký tự.
  • Kết quả của hàm CONCATENATE luôn luôn là một chuỗi văn bản (bản chất là dạng Text, không phải dạng Số hay ngày tháng, cho dù bạn nhìn bằng mắt thường có thể chứa toàn các con số hoặc hiển thị như 1 giá trị ngày)
  • Để thể hiện kết quả của hàm CONCATENATE sau khi nối có định dạng Số hoặc Ngày thì bạn cần kết hợp với hàm TEXT để thay đổi cách hiển thị.
  • Hàm này không cho phép viết dạng mảng, vùng ô, mà phải viết từng ô riêng, từng đoạn text riêng trong các tham số của hàm. Ví dụ, bạn nên viết =CONCATENATE(A1, A2, A3) thay vì =CONCATENATE(A1:A3).
  • Nếu ít nhất một trong các đối số của hàm CONCATENATE không hợp lệ, công thức sẽ trả về lỗi #VALUE!
  • Hàm CONCATENATE hoặc hàm CONCAT đều là một.

Để nối một số ký tự đặc biệt, bạn có thể sử dụng hàm CHAR với mã số tương ứng:

  • Dấu ngoặc kép (“) – CHAR (34)
  • Chéo chéo (/) – CHAR (47)
  • Dấu sao (*) – CHAR (42)
  • Danh sách đầy đủ các mã ASCII có thể dễ dàng tìm thấy.

Ví dụ:

=CONCATENATE(A1, CHAR(47), B1)

sẽ tương đương với

=CONCATENATE(A1, “https://zik.vn/”, B1)

cách viết hàm concat để nối thêm ký tự đặc biệt

Kết luận

Qua bài viết này, bạn đã có thể tự tin sử dụng các hàm nối chuỗi trong Excel chưa? Hy vọng những chia sẻ trong bài viết này sẽ hữu ích trong công việc của bạn. Nếu có bất kỳ thắc mắc gì bạn có thể góp ý, phàn hồi ngay dưới bài viết này để chúng tôi có thể giải quyết mọi thắc mắc của bạn một cách sớm nhất.

Những kiến thức bạn đang xem thuộc khóa học Excel từ cơ bản tới nâng cao của zik.vn. Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống về các hàm, các công cụ trong excel, ứng dụng excel trong công việc… Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký tham gia khóa học này.

Tải về file bài tập thực hành

Bạn có thể tải về file bài tập thực hành trong bài viết tại đường link bên dưới:


Đánh giá bài viết này

Tài liệu kèm theo bài viết

Lê Đình Chi


Your Signature

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Subscribe to our newsletter now!

DMCA.com Protection Status
>