Hàm CONCATENATE trong Excel là một hàm hữu ích giúp kết hợp các chuỗi ký tự từ nhiều ô khác nhau thành một chuỗi duy nhất, giúp cho việc tổng hợp và hiển thị dữ liệu trở nên hiệu quả hơn. Nếu bạn chưa có đầy đủ kiến thức về hàm CONCATENATE, hãy cùng tìm hiểu công thức và cách sử dụng hàm này trong bài viết sau đây!
- Hướng dẫn dưới đây được thực hiện trên laptop hệ điều hành Windows, phiên bản Excel 2016. Bạn có thể thực hiện tương tự trên các phiên bản Excel 2007, 2010, 2013 và 2019.
- Bạn có thể tải một số ví dụ về hàm CONCATENATE trong Excel để làm quen nhé!
Công thức hàm CONCATENATE trong Excel
Hàm CONCATENATE (hoặc CONCAT) là một trong những hàm văn bản quan trọng, được sử dụng để ghép nối các ký tự hoặc đoạn văn bản khác nhau vào một ô duy nhất.
Cú pháp của hàm CONCAT/CONCATENATE:
=CONCATENATE(văn_bản1, văn_bản2, …)
Trong đó văn_bản1, văn_bản2 là các chuỗi văn bản, có thể là tham chiếu tới ô hoặc giá trị trong công thức.
Lưu ý:
- Sử dụng dấu phẩy (,) để ngăn cách giữa các text1, text2,…
- Trong đối số text, cần bao quanh chuỗi ký tự mong muốn bằng dấu ngoặc kép (“), nếu không hàm sẽ trả về lỗi #NAME?
- Hàm CONCATENATE cho phép nối tối đa 255 chuỗi, tương đương với 8.192 ký tự trong một công thức.
- Kết quả trả về của hàm luôn là một chuỗi văn bản, ngay cả khi hiển thị dưới dạng con số.
- CONCATENATE không thể nhận biết các mảng, do đó bạn cần liệt kê từng đối số và phân cách bằng dấu phẩy.
Ví dụ về hàm CONCATENATE trong Excel
Dưới đây là một số ứng dụng phổ biến của hàm CONCATENATE:
Nối từ, bổ sung ký tự
- Ví dụ 1: Thêm thêm từ hoặc ký tự vào chuỗi.
- Ví dụ 2: Sử dụng hàm TEXT để chèn ngày và tháng vào chuỗi ký tự. Bạn có thể tìm hiểu thêm về cách sử dụng công thức và hàm TEXT tại đây.
- Ví dụ 3: Tạo khoảng trắng giữa họ và tên.
- Ví dụ 4: Gộp số vào trong chuỗi ký tự.
Xin mời bạn xem bảng dưới đây để có thể hình dung rõ hơn!
Nối dữ liệu từ 2 hoặc nhiều cột lại với nhau
Để kết hợp dữ liệu từ hai cột, bạn cần nhập công thức kết hợp vào ô đầu tiên của cột mà bạn muốn lấy dữ liệu. Ví dụ, để kết hợp dữ liệu từ cột Họ và cột Tên thành cột Họ và tên, bạn cần thêm một khoảng trắng giữa họ và tên để phân tách chúng.
Tại ô C2, bạn có thể nhập:
=CONCATENATE(A2,” “,B2)
Hoặc:
=A2&” “&B2
Sau khi nhập công thức, nhấn Enter. Sau đó, bạn có thể sao chép công thức bằng cách di chuyển con trỏ chuột vào góc dưới bên phải của ô chứa công thức đã chọn và kéo thả chuột xuống các ô còn lại trong cột.
Kết quả sẽ hiển thị như sau:
Bạn có thể sử dụng bảng tương tự như sau:
Nối dữ liệu trong một mảng
Dưới đây là một số gợi ý để bạn có thể nối dữ liệu trong một mảng bất kỳ:
Cách 1: Liệt kê/chọn từng đối số text:
Đầu tiên, bạn nhập công thức hàm CONCATENATE, sau đó tới mục liệt kê các đối số bạn cần nhấn và giữ phím Ctrl và nhấp vào từng ô mà bạn muốn ghép lại (Lưu ý nhấp theo thứ tự mà bạn muốn các ký tự này hiển thị). Sau khi chọn hết, thả phím Ctrl và đóng ngoặc cho công thức.
Cách 2: Sử dụng hàm TRANSPOSE kết hợp với phím F9 để chuyển đổi trước và sau đó sử dụng hàm CONCATENATE:
Lưu ý: Cách này chỉ áp dụng khi các chuỗi cần nối đã được xếp thành một cột theo thứ tự.
Hàm TRANSPOSE có tác dụng chuyển hàng thành cột hoặc ngược lại. Ta có thể sử dụng hàm này để lấy công thức mảng, sau đó sử dụng phím F9 để thay thế công thức bằng các giá trị tính toán và cuối cùng sử dụng hàm CONCATENATE để nối chuỗi.
Công thức hàm TRANSPOSE:
=TRANSPOSE(array) với array là mảng chứa chuỗi cần nối.
Bạn có thể thực hiện theo các bước sau:
Bước 1: Nhập công thức =TRANSPOSE(B1:B3), sau đó nhấn phím F9 (để thay thế công thức bằng các giá trị tính toán).
Bước 2: Sau khi nhấn F9, bạn sẽ thấy công thức hàm TRANSPOSE sẽ không còn xuất hiện mà thay vào đó là các chuỗi ký tự hiển thị tại các ô trong mảng được chọn, được phân cách bởi dấu phẩy và được bao quanh bởi dấu ngoặc nhọn. Bạn cần loại bỏ dấu ngoặc nhọn để xem các giá trị trong các ô muốn nối thành chuỗi.
Bước 3: Các giá trị thu được sau khi thực hiện các thao tác trên sẽ trở thành các đối số text của hàm CONCATENATE. Lúc này, bạn đặt con trỏ chuột vào đầu công thức, trước các giá trị và nhập tên hàm CONCATENATE để áp dụng hàm này nối chuỗi như bình thường. Bạn có thể chỉnh sửa công thức để thêm hoặc bớt các ký tự để đạt được kết quả hiển thị mong muốn.
Cách kết hợp chuỗi có xuống dòng
Trong một số trường hợp, bạn có thể cần kết hợp các chuỗi và định dạng chúng bằng cách ngắt dòng để hiển thị một cách đẹp mắt hơn. Điều này thường xảy ra khi bạn có dữ liệu gồm tên, địa chỉ và số điện thoại trong các thư từ.
Để làm được điều này, bạn cần sử dụng hàm CHAR (trả về ký tự được xác định bằng số mã) với mã ASCII tương ứng để dữ liệu có thể xuống dòng.
Cú pháp của hàm CHAR:
=CHAR(number) với number là số nguyên từ 1 đến 255.
Bạn cần nhớ mã ASCII của ký tự xuống dòng, ví dụ:
- Trên Windows, mã ASCII cho ký tự xuống dòng là 10, do đó bạn có thể sử dụng hàm CHAR (10).
- Trên máy Mac, mã ASCII cho ký tự xuống dòng là 13, do đó bạn có thể sử dụng hàm CHAR (13).
Lưu ý: Để hiển thị chuỗi có xuống dòng trong ô, bạn cần định dạng Wrap text bằng cách vào thẻ Home > Wrap text (Excel 2016) hoặc nhấn tổ hợp phím Ctrl + 1 và chọn Wrap text tại thẻ Alignment.
Khi thực hiện nhập công thức, bạn có thể lựa chọn dùng hàm CONCATENATE hoặc dùng công thức chứa ký tự nối. 2 cách này đều cho ra cùng kết quả nếu bạn nhập đúng.
Ví dụ, để có được kết quả như hình dưới, bạn có thể sử dụng hai công thức sau:
Cách 1: Sử dụng hàm CONCATENATE:
Gõ: =CONCATENATE(A2,CHAR(10),B2,”, “,C2,CHAR(10),D2)
Cách 2: Sử dụng ký tự nối “&”:
Gõ: =A2&CHAR(10)&B2&”, “&C2&CHAR(10)&D2
Để tạo bảng tổng hợp, bạn chỉ cần nhập một trong hai công thức trên vào ô đầu tiên của mảng dữ liệu và nhấn Enter.
Sau đó, bạn có thể sao chép công thức này sang các ô còn lại trong cột.
Cuối cùng, bạn sẽ có được bảng tổng hợp như sau:
Những sai lầm thường gặp khi sử dụng CONCATENATE
Dưới đây là những sai sót thường gặp khi sử dụng hàm CONCATENATE:
Lỗi #NAME?
– Nguyên nhân: Nếu đối số text là một chuỗi văn bản không có dấu ngoặc kép (“”), hàm sẽ trả về lỗi #NAME?.
Ví dụ, nếu bạn nhập vào một ô bất kỳ với công thức: =CONCATENATE(a,v), ngay lập tức hàm sẽ trả về lỗi #NAME?
– Cách khắc phục: Thêm dấu ngoặc kép vào trước và sau các ký tự đầu và cuối của chuỗi văn bản trong đối số text.
Văn bản không hiển thị như mong đợi
– Nguyên nhân: Khi thực hiện việc ghép chuỗi, có thể bạn sẽ gặp phải tình trạng thiếu các dấu cách, dấu chấm, dấu phẩy,… trong kết quả hiển thị, làm cho văn bản sau khi nối không đạt được tính thẩm mỹ. Điều này xảy ra do việc không thêm đầy đủ các ký tự cần thiết vào vị trí tương ứng trong công thức.
– Cách khắc phục: Bạn cần kiểm tra lại công thức và bổ sung các ký tự còn thiếu vào đối số text của hàm.
Xem thêm:
- Cách gộp nhiều ô trong Excel không mất dữ liệu chi tiết
- Cách sử dụng hàm MID trong Excel để cắt chuỗi
- Cách dùng hàm VLOOKUP trong Excel
Trên đây là hướng dẫn cách sử dụng hàm CONCATENATE để ghép chuỗi trong Excel. Hy vọng bài viết sẽ hữu ích cho bạn trong quá trình làm việc với bảng tính!
