Trong một bảng tính Excel, dữ liệu không phải lúc nào được cấu trúc theo nhu cầu của bạn. Trong đó hàm CONCATENATE sẽ giúp bạn thức hiện ghép nối chuỗi trong Excel. Bài viết dưới đây sẽ hướng dẫn bạn cách ghép nối chuỗi trong Excel.
Video hướng dẫn cách ghép nối trong Excel – Hàm CONCATENATE
Hàm CONCATENATE:
Hàm CONCATENATE trong Excel được sử dụng để nối các đoạn văn bản khác nhau lại hoặc kết hợp các giá trị từ một vài ô vào một ô.
Công thức của CONCATENATE như sau:
=CONCATENATE (text1, [text2], …)
Trong đó tham số text là một chuỗi văn bản, tham chiếu ô hoặc giá trị trong công thức.
Kết nối chuỗi văn bản và giá trị di động:
Bạn hoàn toàn có thể sử dụng nó để nối các chuỗi văn bản khác nhau để làm cho kết quả có ý nghĩa hơn. Ví dụ:
=CONCATENATE(A1, ” “, B1, ” completed”)
Công thức trên cho người dùng biết rằng công việc nào đó đã hoàn thành, như trong hàng hai trong hình dưới đây. Xin lưu ý rằng chúng ta thêm một dấu cách trước chữ ” completed ” để tách các chuỗi văn bản được nối.
Đương nhiên, bạn có thể thêm một chuỗi văn bản ngay từ đầu hoặc ở giữa công thức Concatenate của bạn:
=CONCATENATE(“See “, A1, ” “, B1)
Một khoảng trống (“”) được thêm vào giữa các giá trị kết hợp, để kết quả hiển thị như là “Project1 ” chứ không phải là “Project1 “.
Kết nối chuỗi văn bản và giá trị di động
Kết nối một chuỗi văn bản và một giá trị từ trong công thức tính toán
Để làm cho kết quả trả lại bằng một số công thức dễ hiểu hơn cho người dùng, bạn hoàn toàn có thể nối nó với một chuỗi văn bản để giải thích giá trị thực sự là gì.
Ví dụ, bạn hoàn toàn có thể sử dụng công thức sau đây để trả lại ngày hiện tại:
=CONCATENATE(“Today is “,TEXT(TODAY(), “dd-mmm-yy”))
Kết nối một chuỗi văn bản và một giá trị từ trong công thức tính toán
Những điều cần nhớ khi sử dụng CONCATENATE trong EXCEL
Để đảm bảo rằng các công thức của hàm CONCATENATE luôn mang lại kết quả chính xác nhất, bạn hãy nhớ các quy tắc đơn giản sau đây:
- Hàm CONCATENATE yêu cầu ít nhất một “text” đối số để làm việc.
- Trong một công thức của hàm CONCATENATE, bạn hoàn toàn có thể nối ghép lên tới 255 chuỗi, 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, ngay cả khi tất cả các giá trị nguồn là các con số.
- Hàm CONCATENATE không nhận biết các mảng. Mỗi tham chiếu ô phải được liệt kê riêng. 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 tham số của hàm CONCATENATE không hợp lệ, công thức sẽ tự động trả về lỗi #VALUE!.
Dùng “&” để kết nối các chuỗi trong EXCEL:
Trong Excel, toán tử “&” là một cách khác để thực hiện nối các ô. Phương pháp này rất có ích trong nhiều trường hợp bởi vì việc gõ (&) sẽ nhanh hơn nhiều so với gõ hàm “concatenate”.
Tương tự với hàm CONCATENATE, bạn hoàn toàn có thể sử dụng “&” trong Excel để kết hợp các chuỗi văn bản khác nhau, giá trị của ô và kết quả trả về bởi các hàm khác.
VÍ DỤ VỀ “&”.
Để xem toán tử nối đang hoạt động, bạn hãy viết lại các công thức CONCATENATE đã thảo luận ở trên:
Kết nối các giá trị trong ô A1 và ô B1:
=A1&B1
Kết nối các giá trị trong ô A1 và ô B1 với khoảng trống:
=A1&” “&B1
Kết nối các giá trị trong các ô A1, B1 và một chuỗi văn bản:
=A1 & B1 & ” completed”
Kết nối một chuỗi và kết quả của hàm TEXT / TODAY:
=”Today is ” & TEXT(TODAY(), “dd-mmm-yy”)
Như đã trình bày trong hình ảnh bên dưới, hàm CONCATENATE và toán tử “&” sẽ trả kết quả giống hệt nhau:
Dùng “&” để kết nối các chuỗi trong EXCEL
“&” Với hàm CONCATENATE trong EXCEL
Nhiều người dùng tự hỏi rằng liệu có cách nào sử dụng hiệu quả hơn để nối các chuỗi trong hàm CONCATENATE hoặc toán tử “&” hay không.
Sự khác biệt duy nhất cần thiết giữa hàm CONCATENATE và toán tử “&” là hàm CONCATENATE có giới hạn 255 còn & thì không có giới hạn nào. Ngoài ra, không có gì khác nhau giữa hai phương pháp nối này, cũng không có sự khác biệt về hiệu lực giữa các công thức hàm CONCATENATE và toàn tử “&”.
Vì 255 là cũng một con số thực sự lớn và trong thực tế, bạn sẽ không bao giờ dùng hết khi kết hợp nhiều chuỗi, sự khác biệt còn phải kể tới lại là sự thoải mái và tiện dụng hơn. Một số người dùng thấy công thức hàm CONCATENATE dễ đọc hơn, cá nhân tôi thích sử dụng phương pháp “&”. Vì vậy, bạn chỉ cần dùng kỹ thuật nối mà bạn cảm thấy thoải mái hơn.
Kết nối các chuỗi văn bản với các xuống dòng
Thông thường, bạn sẽ tách các chuỗi văn bản được nối với nhau bằng các dấu chấm câu và dấu cách, như thể hiện trong ví dụ trước. Tuy vậy, trong một số trường hợp, bạn hoàn toàn có thể cần phải tách các giá trị bằng một ngắt dòng. Một ví dụ phổ biến là bạn phải thực hiện hợp nhất địa chỉ gửi thư từ dữ liệu trong các cột riêng biệt.
Một vấn đề trong thực tế xảy ra là bạn không thể đơn giản chỉ gõ một ngắt dòng trong công thức như một ký tự bình thường và do đó bạn cần một hàm CHAR với mã ASCII tương ứng cho công thức nối:
- Trên hệ điều hành Windows, bạn sử dụng CHAR (10) trong đó 10 là mã ASCII để xuống dòng .
- Trên hệ thống Mac, bạn phải sử dụng CHAR (13) trong đó 13 là mã ASCII .
Trong ví dụ này, bạn có các phần địa chỉ trong các cột A đến F, và bạn thực hiện ghép chúng lại với nhau trong cột G bằng cách sử dụng toán tử nối “&”. Các giá trị được hợp nhất được phân cách bằng một dấu phẩy (“,”), dấu cách (“”) và dấu xuống dòng CHAR (10):
=A2 & ” ” & B2 & CHAR(10) & C2 & CHAR(10) & D2 & “, ” & E2 & ” ” & F2
Kết nối các chuỗi văn bản với các xuống dòng
Chú ý: Khi bạn sử dụng ngắt dòng để tách các giá trị được nối, bạn phải có tùy chọn “Wrap text ” cho phép kết quả hiển thị chính xác nhất. Để thực hiện việc này, bạn hãy nhấn Ctrl + 1 để mở hộp thoại Format Cells, chuyển sang tab Alignment và kiểm tra lại hộp Wrap text.
Theo cách tương tự, bạn hoàn toàn có thể tách biệt chuỗi nối với các ký tự khác như:
- 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.
Mặc dù thế, có một cách dễ dàng hơn để bạn thực hiện chèn các ký tự có thể in được trong công thức nối là chỉ cần gõ chúng vào dấu ngoặc kép như bạn đã làm trong ví dụ trước .
Dù bạn thực hiện bằng cách nào, tất cả bốn công thức dưới đây mang lại kết quả giống hệt nhau:
=A1 & CHAR(47) & B1
=A1 & “/” & B1
=CONCATENATE(A1, CHAR(47), B1)
=CONCATENATE(A1, “/”, B1)
Kết nối các chuỗi văn bản với các xuống dòng
