Cách nối chuỗi dữ liệu với hàm CONCATENATE trong Excel chi tiết

Hàm CONCATENATE trong Excel thường được dùng để nối các chuỗi ký tự khác nhau thành một chuỗi duy nhất vào cùng một ô. Vậy hàm CONCATENATE là gì, cú pháp và cách dùng hàm này trong Excel như thế nào. Hãy cùng theo dõi bài viết sau để nắm rõ hơn nhé!

Hàm CONCATENATE là gì?

Hàm CONCATENATE là hàm được dùng để nối các đoạn văn bản, ký tự hay giá trị từ nhiều ô khác nhau vào cùng một ô.

Hàm CONCATENATE trong Excel được ứng dụng để:

• Nối các chuỗi cơ bản như từ, ký tự, dấu câu, văn bản hoặc kết hợp các giá trị từ nhiều ô vào cùng một ô.
• Kết hợp với nhiều hàm khác như If, Vlookup để nối các chuỗi lại dựa trên một điều kiện nhất định nhằm hỗ trợ nhanh trong công việc.

Từ phiên bản Excel 2016 trở đi, hàm này đã được thay thế thành hàm CONCAT. Cú pháp và cách sử dụng cũng thực hiện tương tự như hàm CONCATENATE.

Công thức hàm CONCATENATE là gì?

Hàm CONCATENATE trong Excel có cú pháp sau: =CONCATENATE (text1, [text2], …)

Trong đó:

• Text 1: Là một chuỗi văn bản, giá trị hoặc tham chiếu ô cần ghép nối.
• Text 2: Là một chuỗi văn bản bổ sung cần ghép nối (tùy chọn, không bắt buộc). Giá trị của text 2 có thể tối đa 255 mục và phải có dấu phẩy giữa các chuỗi.

Lưu ý khi dùng hàm CONCATENATE có điều kiện trong Excel

Hiểu được hàm CONCATENATE là hàm gì thì chưa đủ, nếu muốn sử dụng, bạn cần chú ý một số điều sau:

• Giữa các chuỗi cần được ngăn cách bằng dấu phẩy.
• Nếu text đầu vào là chuỗi văn bản, cần đưa chuỗi vào dấu ngoặc kép, nếu không hàm sẽ trả lỗi #NAME?
• Khi trong chuỗi cần ghép đôi có khoảng cách, cần thêm đối số ' ' vào giữa các chuỗi.
• Hàm CONCATENATE được ghép nối tối đa 255 chuỗi,tương đương với 8192 ký tự.
• Khi các giá trị đều là dạng số, thì CONCATENATE luôn trả về kết quả là chuỗi văn bản.
• Hàm CONCATENATE không nhận biết được các tham số là mảng. Do đó, khi ghép đôi bạn cần phải liệt kê các tham số riêng, và ngăn cách bằng dấu phẩy.

Hướng dẫn dùng hàm CONCATENATE cơ bản trong Excel

Hàm CONCATENATE được sử dụng rộng rãi trong nhiều trường hợp khác nhau. Để hiểu rõ hơn về hàm này, bạn có thể tham khảo các ví dụ sau đây.

Trường hợp 1: Nối từ cơ bản

Hàm CONCATENATE có chức năng chính là nối nhiều chuỗi với nhau. Trong đó, đơn giản nhất là nối các từ cơ bản ở nhiều ô trong bảng dữ liệu Excel hiển thị vào cùng một ô.

Ví dụ: Bảng dữ liệu sau có 2 cột Họ tên và Số điện thoại. Hãy sử dụng hàm CONCATENATE để 2 cột trên vào cùng một ô.

Bạn sẽ áp dụng công thức hàm CONCATENATE thực hiện như sau:

Bước 1: Trong bảng dữ liệu Excel, bạn nhập công thức hàm =CONCATENATE(B4,C4) vào ô tham chiếu hiển thị kết quả.

Bước 2: Nhấn phím Enter để trả kết quả.

Bạn sẽ nhận được kết quả như hình nếu thao tác đùng.

Trường hợp 2: Nối từ bằng ký tự đặc biệt

Bên cạnh việc nối các từ cơ bản, hàm CONCATENATE còn được dùng để nối các chuỗi với khoảng trắng hoặc ký tự đặc biệt ở giữa. Cùng xem thử ví dụ sau để hiểu rõ hơn về hai trường hợp trên.

Ví dụ: Trong bảng dữ liệu có 2 cột Họ, Tên nằm riêng biệt. Hãy sử dụng hàm CONCATENATE để nối Họ và Tên vào cùng một ô.

Theo đó, text 1 là giá trị ở cột Họ và text 2 là giá trị ở cột Tên.

Bạn có thể áp dụng các bước sau để nối từ ở 2 cột trên:

Bước 1: Trong bảng dữ liệu Excel, bạn nhập hàm =CONCATENATE(A2, B2) vào ô muốn hiển thị kết quả.

Lưu ý: Ở bước nhập hàm, bạn cần thêm đối số ' ' vào giữa để tạo khoảng cách giữa 2 giá trị ghép đôi.

Bước 2: Nhấn phím Enter để hoàn tất và hiển thị kết quả.

Như vậy, với hàm CONCATENATE bạn đã xử lý bảng dữ liệu bao gồm cột Họ và Tên một cách nhanh gọn.

Trường hợp 3: Nối thêm ký tự vào văn bản

Hàm CONCATENATE ngoài ghép các ô nội dung có sẵn trong bảng dữ liệu, còn cho phép bạn nối thêm các ký tự vào giữa văn bản. Sau đây là ví dụ cơ bản cho chức năng nối thêm ký tự của hàm CONCATENATE:

Ví dụ: Trong bảng dữ liệu Excel, có hai cột giá trị Họ tên và Quê quán của học sinh. Bạn cần ghép 2 thông tin trên và thêm từ “ở” vào cùng một ô.

Thử sử dụng hàm CONCATENATE và thực hiện theo các bước sau:

Bước 1: Tại bảng tính Excel, bạn nhập hàm =CONCATENATE(A2,' ở ', B2) vào ô muốn hiển thị kết quả. Khi đó, ký tự bạn thêm vào cần bỏ trong dấu ngoặc kép.

Bước 2: Nhấn phím Enter để hiển thị kết quả.

Với việc thêm ký tự vào giữa chuỗi văn bản, bạn cần thêm khoảng trắng ở 2 bên để tạo sự đẹp mắt cho đoạn văn.

Trường hợp 4: Nối số và ngày tháng ở các định dạng khác nhau

Ở trường hợp này, bạn có thể sử dụng hàm CONCATENATE để chèn số và ngày tháng vào chuỗi ký tự.

Ví dụ: Hãy sử dụng hàm CONCATENATE để nối số và ngày tháng hôm nay trong bảng dữ liệu Excel. Cùng thực hiện các thao tác sau đây vào bảng tính:

Bước 1: Trong bảng dữ liệu Excel, nhập hàm =CONCATENATE('TPHCM, ngày',' ',TEXT(TODAY(),'dd/mm/yyyy')) vào ô muốn hiển thị kết quả.

Trong đó:

• TPHCM, ngày: Text 1 của hàm CONCATENATE là chuỗi văn bản.
• Đối số ' ' để tạo khoảng cách giữa các chuỗi.
• TEXT(TODAY(),'dd/mm/yyyy”: Công thức của hàm Text, sẽ lấy dữ liệu ngày hôm nay có định dạng là DD/MM/YY

Bước 2: Nhấn phím Enter sẽ hiển thị kết quả như hình bên dưới:

Khi nối chuỗi văn bản và ngày tháng, bạn phải sử dụng hàm TEXT để hiển thị ngày ở định dạng mong muốn.

Hàm AND trong Excel là gì? Cách dùng hàm AND cực đơn giản và chi tiết, xem ngay!

Hướng dẫn nối vùng dữ liệu phức tạp trong Excel

Hàm CONCATENATE còn được dùng để nối các vùng dữ liệu phức tạp trong Excel như: nối giá trị giữa các cột hay trong một mảng,... Cùng tìm hiểu chi tiết hơn qua một số ví dụ dưới đây:

Cách dùng hàm CONCATENATE để nối dữ liệu các cột với nhau

Để nối dữ liệu từ hai cột trở lên trong bảng Excel, bạn có thể nhập công thức hàm CONCATENATE để lấy giá trị ở các cột nối với nhau.

Ví dụ: Khi muốn nối hai cột Họ và Tên trong bảng dữ liệu (cột A và B), bạn hãy sử dụng hàm CONCATENATE nối các giá trị trong cột A và B với nhau để được kết quả hiển thị ở cột C. Cùng thực hiện các bước sau:

Bước 1: Tại ô C2, bạn hãy nhập cú pháp =CONCATENATE(A2,' ',B2), để nối giá trị trong ô A2 với khoảng trống và giá trị ô B2.

Bước 2: Nhấn Enter để ra kết quả ở ô C2.

Bước 3: Sau đó, ở góc dưới bên phải của ô C2 xuất hiện dấu cộng, bạn hãy nhấp chuột kéo thẳng xuống các ô khác để được kết quả cả cột.

Ngoài ra, tại ô có công thức bạn có thể nhấp đúp vào dấu cộng màu đen, để sao chép nhanh chóng công thức xuống các ô khác.

Cách dùng hàm CONCATENATE để nối dữ liệu trong một mảng

Khi bạn muốn nối các giá trị trong cùng một mảng, bạn cũng có thể dùng công thức CONCATENATE. Dưới đây là 2 cách giúp bạn ghép nối nhanh hơn:

Cách 1: Nhấn phím Ctrl để chọn nhiều ô nối cùng lúc.

Để chọn đồng thời nhiều ô, bạn nhấn phím CTRL. Và nhấp vào từng ô mà bạn muốn nối trong công thức CONCATENATE.

Bước 1: Để ghép các ô từ B1 đến B3, bạn nhập công thức = CONCATENATE(B1, B2, B3) tại ô hiển thị kết quả. Khi nhập công thức, bạn nhấn giữ phím Ctrl và chọn từng ô trong mảng dữ liệu để thêm vào hàm.

Bước 2: Nhấn Enter sẽ nhận được kết quả như hình:

Với cách này, bạn phải nhấn chọn vào từng ô riêng lẻ chứ không thể kéo chọn một dải bằng chuột. Điều này sẽ làm phát sinh thêm một mảng vào công thức và hàm CONCATENATE không thực hiện được.

Cách 2: Sử dụng hàm TRANSPOSE để lấy phạm vi trước

Cách này được sử dụng khi bạn cần ghép một dải dữ liệu lớn với số lượng chục ô trở lên. Hàm TRANSPOSE được dùng để trả về một mảng. Sau đó thay thế nó bằng các tham chiếu ô riêng lẻ.

Ví dụ: Bạn cần nối các giá trị Thông tin sản phẩm trong bảng dữ liệu, hãy kết hợp hàm TRANSPOSE như sau:

Bước 1: Tại ô D1, nhập hàm =TRANSPOSE(B1:B3) và nhấn F9 để trả kết quả về một chuỗi ký tự dạng mảng nằm trong dấu ngoặc nhọn {}.

Bước 2: Nhập công thức hàm =CONCATENATE ở phía trước và xóa bỏ dấu ngoặc nhọn {} đi.

Bước 3: Cuối cùng, bạn nhấn Enter để hiển thị kết quả.

Dù bạn sử dụng cách nào thì giá trị nối trong ô kết quả vẫn là một chuỗi văn bản. Vì kết quả hàm CONCATENATE luôn trả về là một chuỗi văn bản bất kể loại dữ liệu nguồn.

Hướng dẫn cách dùng hàm CONCATENATE nối chuỗi có xuống dòng

Ngoài việc nối được nhiều dữ liệu trong một mảng. Hàm CONCATENATE còn được dùng kết hợp với một số hàm khác để nối chuỗi văn bản có xuống dòng. Để thực hiện phần này, bạn có thể kết hợp hàm CONCATENATE với CHAR (10) trên Windows, hoặc CHAR (13) trên Macbook.

Đối với Window, có các cột thông tin liên hệ của nhân sự trong bảng, bạn cần nối lại để hiển thị đầy đủ thông tin trong cùng một ô. Thử thực hiện theo các thao tác sau:

Bước 1: Đầu tiên, bấm vào ô hiển thị kết quả để tùy chỉnh định dạng. Đi đến mục Home, chọn Wrap Text.

Bước 2: Dùng hàm CONCATENATE kết hợp CHAR (10) để nối các chuỗi thông tin có xuống dòng. Nhập vào ô E2 công thức: =CONCATENATE(A2,CHAR(10),B2,CHAR(10)).

Trong đó: CHAR (10) là dấu ngắt để xuống dòng.

Bước 3: Sau khi nhập hàm xong, bạn sẽ nhận được kết quả trả về.

Lưu ý: Khi sử dụng nối chuỗi ngắt dòng để tách các giá trị. Bạn cần phải tùy chỉnh định dạng Wrap text trước để kết quả hiển thị chính xác.

Kết hợp hàm CONCATENATE với hàm VLOOKUP trong Excel

Hàm CONCATENATE được dùng để nối các chuỗi văn bản với nhau, còn VLOOKUP được sử dụng để tra cứu giá trị trong một bảng tính dữ liệu.

Khi kết hợp hai hàm này, bạn có thể nối các chuỗi văn bản với nhau dựa trên các giá trị có trong một bảng dữ liệu Excel.

Cú pháp của hàm CONCATENATE kết hợp hàm VLOOKUP như sau:

=CONCATENATE(Lookup_value, ', ', VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup))

Trong đó:

• Lookup_value: Là ô chứa giá trị bạn cần tìm.
• Table_array: Là bảng dữ liệu chứa các giá trị cần tìm.
• Col_index_num: Là cột giá trị cần tìm trong bảng dữ liệu.
• Range_lookup: Độ chính xác hay tương đối cho kết quả tìm kiếm.

Công thức này sẽ nối chuỗi văn bản trong ô với chuỗi văn bản ', '. Sau đó nối chuỗi văn bản này với giá trị của hàm VLOOKUP được trả về.

Lỗi thường gặp khi sử dụng hàm CONCATENATE

Trong quá trình sử dụng hàm CONCATENATE có điều kiện, bạn có thể gặp một số lỗi sau:

• Lỗi #NAME?:

Nguyên nhân là khi nhập công thức hàm, nếu đối số text đầu vào là dạng chuỗi văn bản mà bị thiếu dấu ngoặc kép (''), thì hàm sẽ trả về lỗi #NAME? Bạn cần đưa đối số chuỗi văn bản vào dấu ngoặc kép để hàm CONCATENATE trả kết quả đúng.

• Văn bản hiển thị không như mong muốn:

Khi nối các chuỗi văn bản lại với nhau, bạn sẽ gặp trường hợp kết quả hiển thị bị thiếu khoảng cách, dấu phẩy. Khi đó, văn bản sẽ hiển thị không đẹp mắt. Nguyên nhân của lỗi là vì khi nhập công thức bạn đã không thêm các ký tự vào vị trí tương ứng.

Bạn cần kiểm tra lại công thức và bổ sung các ký tự cần thiết tương ứng vào vị trí giữa các đối số text của hàm CONCATENATE.

Những câu hỏi thường gặp về hàm CONCATENATE

Khi sử dụng hàm CONCATENATE, có một số câu hỏi người dùng thắc mắc rằng:

1. Nên dùng hàm CONCATENATE hay hàm Toán tử “&” ?

Hàm toán tử “&” cũng là một cách tiện lợi để bạn ghép nối các ô văn bản. Với hàm này bạn sẽ không cần dùng hàm CONCATENATE.

Còn hàm CONCATENATE được sử dụng để nối các chuỗi văn bản. Hay bạn cũng có thể nối nhiều ô giá trị trong bảng dữ liệu.

Như vậy, trong những trường hợp đơn giản để nhanh hơn, bạn có thể sử dụng hàm toán tử “&” .

2. Hàm CONCATENATE và Hàm CONCAT có gì khác nhau?

Hai hàm này đều là như nhau. Bắt đầu từ phiên bản Excel 2016, CONCATENATE đã được đổi tên thành CONCAT. Công thức và cách sử dụng hàm CONCAT vẫn thực hiện tương tự như hàm CONCATENATE.

Kết luận

Bài viết trên đã hướng dẫn chi tiết hàm CONCATENATE là hàm gì cùng cách dùng chúng trong Excel. Hy vọng bạn có thể áp dụng thành công hàm CONCATENATE khi nối các chuỗi văn bản trong Excel. Đừng quên chia sẻ bài viết hữu ích này của Điện Thoại Vui để mọi người nhé!