Trong hướng dẫn này, Học Excel Online và bạn sẽ cùng học các cách khác nhau để nối các chuỗi văn bản, ô, vùng, cột và hàng trong Excel bằng cách sử dụng hàm CONCATENATE trong Excel và toán tử “&”. 

Trong cửa sổ làm việc Excel của bạn, dữ liệu không phải lúc nào cũng được cấu trúc theo nhu cầu của bạn. Thông thường, bạn có thể muốn chia nội dung của một ô thành các ô riêng lẻ hoặc làm ngược lại – kết hợp dữ liệu từ hai hoặc nhiều cột thành một cột duy nhất. Các ví dụ phổ biến yêu cầu nối trong Excel là nối tên và các phần địa chỉ, kết hợp văn bản với giá trị theo hướng công thức, hiển thị ngày và giờ ở định dạng mong muốn, để đặt tên cho một số. 

Trong hướng dẫn này, chúng ta sẽ khám phá các kỹ thuật nối chuỗi khác nhau trong Excel để bạn có thể chọn phương pháp phù hợp nhất cho trang tính của mình.

“Concatenate” trong Excel là gì?  

Để có thể dễ dàng tiếp cận hơn mình xin mời các bạn đọc bài viết về Hàm Concatenate mà Học Excel Online đã biến soạn chi tiết và đầy đủ về khái niệm cũng như các ví dụ ứng dụng thực tiễn của hàm này trong Excel.
Về bản chất, có hai cách để kết hợp dữ liệu trong bảng tính Excel: 

• Hợp nhất ô 
• Ghép các giá trị của ô 

Khi bạn hợp nhất ô, bạn “thực thể” hợp nhất hai hoặc nhiều ô thành một ô duy nhất. Kết quả là bạn có một ô lớn hơn được hiển thị trên nhiều hàng và / hoặc cột trong trang tính của bạn. 

Khi bạn nối các ô trong Excel, bạn chỉ kết hợp nội dung của các ô đó. Nói cách khác, nối trong Excel là quá trình nối hai hoặc nhiều giá trị lại với nhau. Phương pháp này thường được sử dụng để kết hợp một vài đoạn văn bản nằm trong các ô khác nhau (về mặt kỹ thuật, chúng được gọi là chuỗi văn bản hoặc đơn giản là chuỗi) hoặc chèn một giá trị được tính bằng công thức vào giữa một số văn bản. Hình sau đây cho thấy sự khác biệt giữa hai phương pháp này:

Hợp nhất các ô trong Excel là chủ đề của bài viết tiếp theo của chúng ta và trong hướng dẫn này, chúng ta sẽ đề cập đến hai cách cơ bản để nối các chuỗi trong Excel – bằng cách sử dụng hàm CONCATENATE và toán tử & Excel.

Hàm CONCATENATE trong Excel 

Hàm CONCATENATE trong Excel được thiết kế để nối các đoạn văn bản khác nhau lại với nhau hoặc kết hợp các giá trị từ một số ô thành một ô.  

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

CONCATENATE(text1, [text2], …)

Trong đó văn bản là một chuỗi văn bản, tham chiếu ô hoặc giá trị theo hướng công thức. 

Dưới đây, bạn sẽ tìm thấy một vài ví dụ về việc sử dụng hàm CONCATENATE trong Excel  Lưu ý: Trong Excel 2016, Excel 2019, Excel Online và Excel Mobile, hàm CONCATENATE được thay thế bằng hàm CONCAT, có cùng cú pháp. Mặc dù hàm CONCATENATE được giữ lại để tương thích ngược, nhưng bạn nên sử dụng hàm CONCAT thay thế vì Microsoft không đưa ra bất kỳ lời hứa nào rằng hàm CONCATENATE sẽ khả dụng trong các phiên bản Excel trong tương lai. 

Nối các giá trị của một số ô 

Công thức CONCATENATE đơn giản nhất để kết hợp các giá trị của ô A1 và B1 như sau: 

=CONCATENATE(A1, B1)

Xin lưu ý rằng các giá trị sẽ được đan với nhau mà không có bất kỳ dấu phân cách nào, như ở hàng 2 trong hình  phía dưới. 

Để phân tách các giá trị bằng dấu cách, hãy nhập “” vào đối số thứ hai, như ở hàng 3 trong hình bên dưới.  

=CONCATENATE(A1, ” “, B1)

Để phân tách các giá trị được nối bằng các dấu phân cách khác như dấu phẩy, dấu cách hoặc dấu gạch chéo, vui lòng xem công thức CONCATENATE trong Excel với các ký tự đặc biệt.  

Nối chuỗi văn bản và giá trị ô 

Không có lý do gì để hàm CONCATENATE trong Excel bị giới hạn ở việc chỉ nối các giá trị của ô. Bạn cũng 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 thông báo cho người dùng rằng một dự án nhất định đã hoàn thành, như ở hàng 2 trong hình bên dưới. Xin lưu ý rằng chúng tôi thêm một khoảng trắng trước từ “completed” để phân tách các chuỗi văn bản được nối. 

Đương nhiên, bạn cũng có thể thêm một chuỗi văn bản vào đầu hoặc giữa công thức Concatenate của mình: 

=CONCATENATE(“See “, A1, ” “, B1)

Một khoảng trắng (“”) được thêm vào giữa các giá trị được kết hợp, để kết quả hiển thị là “Project 1” chứ không phải “Project1“.

Nối một chuỗi văn bản và một giá trị được tính theo công thức 

Để làm cho kết quả được trả về bởi một số công thức dễ hiểu hơn đối với người dùng của bạn, bạ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 có thể sử dụng công thức sau để trả về ngày hiện tại: 

=CONCATENATE(“Today is “,TEXT(TODAY(), “dd-mmm-yy”))

Mẹo: Nếu bạn muốn xóa dữ liệu nguồn mà không ảnh hưởng đến chuỗi văn bản kết quả, hãy sử dụng tùy chọn “Paste special – values only” để chuyển đổi công thức thành giá trị của chúng. Bạn có thể tìm thấy hướng dẫn chi tiết trong Cách thay thế công thức bằng giá trị trong Excel.

Sử dụng hàm CONCATENATE trong Excel – những điều cần nhớ 

Để đảm bảo rằng công thức CONCATENATE của bạn luôn cung cấp kết quả chính xác, hãy nhớ các quy tắc đơn giản sau: 

• Hàm CONCATENATE trong Excel yêu cầu ít nhất một tham số “text” để hoạt động. 
• Trong một công thức CONCATENATE duy nhất, bạn có thể nối tối đa 255 chuỗi, tổng cộng 8.192 ký tự.  
• Kết quả của hàm CONCATENATE luôn là một chuỗi văn bản, ngay cả khi tất cả các giá trị nguồn đều là số.  
• Excel CONCATENATE không nhận dạng được mảng.  
• Mỗi tham chiếu ô phải được liệt kê riêng biệt.  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 trả về lỗi #VALUE!.

Toán tử “&” để nối các chuỗi trong Excel 

Trong Microsoft Excel, toán tử & là một cách khác để nối các ô. Phương pháp này rất tiện dụng trong nhiều trường hợp vì gõ dấu và (&) nhanh hơn nhiều so với gõ từ “concatenate” 🙂

Tương tự với hàm CONCATENATE, bạn có thể sử dụng “&” trong Excel để kết hợp các chuỗi văn bản, ô, giá trị và kết quả trả về bởi các hàm khác.  

Ví dụ về công thức “&” trong Excel 

Để xem toán tử hoạt động, hãy viết lại công thức CONCATENATE đã thảo luận ở trên: 

Nối các giá trị trong A1 và B1: 

=A1&B1

Nối các giá trị trong A1 và B1 được phân tách bằng dấu cách: 

=A1&” “&B1

Nối các giá trị trong A1, B1 và ​​chuỗi văn bản: 

=A1 & B1 & “completed”

Nối một chuỗi và kết quả của hàm TEXT / TODAY: 

=”Today is ” & TEXT(TODAY(), “dd-mmm-yy”)

Như được minh họa trong hình bên dưới, hàm CONCATENATE và toán tử “&” trả về kết quả giống hệt nhau:

Toán tử “&” trong Excel so với hàm CONCATENATE 

Nhiều người dùng tự hỏi cách nào hiệu quả hơn để nối các chuỗi trong Excel – hàm CONCATENATE hoặc toán tử “&”. 

Sự khác biệt cơ bản duy nhất giữa toán tử CONCATENATE và “&” là giới hạn 255 chuỗi của hàm CONCATENATE trong Excel và không có giới hạn nào như vậy khi sử dụng dấu và. Ngoài ra, không có sự khác biệt giữa hai phương pháp nối này, cũng như không có bất kỳ sự khác biệt nào về tốc độ giữa công thức CONCATENATE và “&”.  

Và vì 255 là một con số thực sự lớn và trong các tác vụ thực tế, ai đó sẽ hiếm khi cần kết hợp nhiều chuỗi đó, nên sự khác biệt sẽ mang lại sự thoải mái và dễ sử dụng. Một số người dùng thấy công thức CONCATENATE dễ đọc hơn, cá nhân tôi thích sử dụng phương thức “&” hơn. Vì vậy, chỉ cần tuân thủ kỹ thuật nối mà bạn cảm thấy thoải mái hơn.

Nối các ô bằng dấu cách, dấu phẩy và các ký tự khác 

Trong trang tính của mình, bạn có thể cần nối các giá trị theo cách bao gồm dấu phẩy, khoảng trắng, các dấu câu khác nhau hoặc các ký tự khác như dấu gạch ngang hoặc dấu gạch chéo. Để thực hiện việc này, chỉ cần đưa ký tự bạn muốn vào công thức nối. Hãy nhớ đặt ký tự đó trong dấu ngoặc kép, như được minh họa trong các ví dụ sau. Nối hai ô bằng dấu cách: 

=CONCATENATE(A1, ” “, B1) or =A1 & ” ” & B1 

Nối hai ô bằng dấu phẩy: 

=CONCATENATE(A1, “, “, B1) or =A1 & “, ” & B1 

Nối hai ô bằng dấu gạch ngang: 

=CONCATENATE(A1, “-“, B1) or =A1 & “-” & B

Hình sau đây cho thấy kết quả có thể trông như thế nào:

Nối các chuỗi văn bản bằng dấu ngắt dòng 

Thông thường, bạn sẽ phân tách các chuỗi văn bản được nối bằng dấu chấm câu và dấu cách, như thể hiện trong ví dụ trước. Tuy nhiên, trong một số trường hợp, có thể cần phải phân tách các giá trị bằng dấu ngắt dòng hoặc dấu xuống dòng. Một ví dụ phổ biến là hợp nhất các mailing addresses từ dữ liệu trong các cột riêng biệt. 

Một vấn đề là bạn không thể chỉ cần nhập một dấu ngắt dòng trong công thức như một ký tự thông thường, và do đó, một hàm CHAR đặc biệt là cần thiết để cung cấp mã ASCII tương ứng cho công thức nối: 

• Trên Windows, hãy sử dụng CHAR(10) trong đó 10 là  Mã ASCII cho Nguồn cấp dữ liệu dòng.  
• Trên hệ thống Mac, hãy sử dụng CHAR(13) trong đó 13 là mã ASCII cho việc vận chuyển trở lại.  

Trong ví dụ này, chúng ta có các phần địa chỉ trong các cột từ A đến F và chúng ta đang 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ị hợp nhất được phân tách bằng dấu phẩy (“,”), dấu cách (“”) và ngắt dòng CHAR 10):

=A2 & ” ” & B2 & CHAR(10) & C2 & CHAR(10) & D2 & “, ” & E2 & ” ” & F2

Lưu ý: Khi sử dụng ngắt dòng để phân tách các giá trị được nối, bạn phải bật tùy chọn “Wrap text” để kết quả hiển thị chính xác. Để thực hiện việc này, hãy nhấn Ctrl + 1 để mở hộp thoại Format Cells, chuyển sang tab Alignment và chọn Wrap text box. 

Theo cách tương tự, bạn có thể tách các chuỗi được nối bằng các ký tự khác như: 

• Dấu ngoặc kép (“) – CHAR(34) 
• Dấu gạch chéo (/) – CHAR(47) 
• Dấu hoa thị (*) – CHAR(42) 

Danh sách đầy đủ của ASCII  mã có sẵn Mặc dù vậy, một cách dễ dàng hơn để đưa các ký tự có thể in vào công thức nối là chỉ cần nhập chúng trong dấu ngoặc kép như chúng ta đã làm trong ví dụ trước. 

Dù bằng cách nào, cả bốn công thức dưới đây đều 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)

Cách nối các cột trong Excel

Để nối hai hoặc nhiều cột trong Excel, bạn chỉ cần nhập một công thức nối thông thường vào ô đầu tiên, sau đó sao chép nó xuống các ô khác bằng cách kéo fill handle (hình vuông nhỏ xuất hiện trong  góc dưới bên phải của ô đã chọn).  

Ví dụ: để nối hai cột (cột A và B) ngăn cách các giá trị bằng khoảng trắng, bạn nhập công thức sau vào ô C2, rồi sao chép công thức đó sang các ô khác. Khi bạn đang kéo fill handle để sao chép công thức, con trỏ chuột sẽ thay đổi thành dấu thập, như thể hiện trong hình bên dưới:

Mẹo: Một cách nhanh chóng để sao chép công thức xuống các ô khác trong cột là chọn ô có công thức và bấm đúp vào fill handle.  

Xin lưu ý rằng Microsoft Excel xác định khoảng cách để sao chép ô sau khi bấm đúp vào ô fill handle dựa trên các ô được tham chiếu bởi công thức của bạn. Nếu có ô trống trong bảng của bạn, giả sử ô A6 và B6 trống trong ví dụ này, công thức sẽ chỉ được sao chép vào hàng 5. Trong trường hợp này, bạn sẽ cần phải kéo chốt điền xuống theo cách thủ công để nối toàn bộ các cột.  

Một cách thay thế để nối các cột trong Excel là sử dụng tùy chọn tương ứng của phần bổ trợ Hợp nhất Ô.

Cách nối một dải ô trong Excel 

Việc kết hợp các giá trị từ nhiều ô có thể tốn một chút công sức vì hàm CONCATENATE trong Excel không chấp nhận mảng và yêu cầu một tham chiếu ô duy nhất trong mỗi tham số. 

Để nối một số ô, chẳng hạn A1 với A4, bạn cần một trong các công thức sau: 

=CONCATENATE(A1, A2, A3, A4) 

hoặc 

=A1 & A2 & A3 & A4 

Khi nối một vùng khá nhỏ, không có vấn đề gì lớn với nhập tất cả các tham chiếu vào thanh công thức. Một vùng lớn sẽ rất tẻ nhạt khi thêm, nhập từng tham chiếu ô theo cách thủ công. Dưới đây, bạn sẽ tìm thấy 3 phương pháp nối vùng nhanh chóng trong Excel.  

Phương pháp 1: Nhấn CTRL để chọn nhiều ô được nối 

Để chọn nhanh một số ô, bạn có thể nhấn phím CTRL và nhấp vào từng ô bạn muốn đưa vào công thức CONCATENATE. Dưới đây là các bước chi tiết: 

1. Chọn một ô mà bạn muốn nhập công thức.  
2. Gõ = CONCATENATE (trong ô đó hoặc trong thanh công thức). 
3. Nhấn và giữ Ctrl và nhấp vào từng ô bạn muốn nối. 
4. Nhả nút Ctrl, gõ dấu ngoặc đóng vào thanh công thức và nhấn Enter.

   

Lưu ý: Khi sử dụng phương pháp này, bạn phải nhấp vào từng ô riêng lẻ. Chọn một vùng bằng chuột sẽ thêm một mảng vào công thức, mà hàm CONCATENATE không chấp nhận.  

Phương pháp 2: Sử dụng hàm TRANSPOSE để lấy vùng 

Khi bạn cần nối một phạm vi khổng lồ bao gồm hàng chục hoặc hàng trăm ô, phương pháp trước đây không đủ nhanh vì nó yêu cầu nhấp vào từng ô. Trong trường hợp này, cách tốt hơn là sử dụng hàm TRANSPOSE để trả về một mảng, sau đó thay thế nó bằng các tham chiếu ô riêng lẻ trong một ô. 

1. Trong ô mà bạn muốn xuất dải ô được nối, hãy nhập công thức TRANSPOSE, ví dụ: =TRANSPOSE(A1:A10) 
2. Trong thanh công thức, nhấn F9 để thay thế công thức bằng các giá trị được tính toán. Kết quả là bạn sẽ có một mảng số được nối với nhau.  
3. Xóa các dấu ngoặc nhọn bao quanh các giá trị mảng.

   

1. Gõ = CONCATENATE (trước giá trị đầu tiên, sau đó nhập dấu ngoặc đóng sau giá trị cuối cùng và nhấn Enter).

   

Lưu ý: Cho dù bạn sử dụng phương pháp nào, giá trị được nối trong C1 là một chuỗi văn bản (lưu ý căn lề trái của nó trong ô), mặc dù mỗi giá trị ban đầu là một số. Điều này là do hàm CONCATENATE luôn trả về một chuỗi văn bản bất kể kiểu dữ liệu nguồn là gì.  

Phương pháp 3: Sử dụng phần bổ trợ Merge Cells 

Một cách nhanh chóng và không cần công thức để nối bất kỳ phạm vi nào trong Excel là sử dụng phần bổ trợ Merge Cells trong Excel với tùy chọn “Merge all area in selection” bị tắt, như được minh họa trong  Kết hợp các giá trị của một số ô thành một ô.

Nối các số và ngày ở các định dạng khác nhau 

Khi bạn nối một chuỗi văn bản với một số hoặc ngày, bạn có thể muốn định dạng kết quả khác nhau tùy thuộc vào tập dữ liệu của mình. Để thực hiện việc này, hãy chèn hàm TEXT vào công thức nối Excel của bạn.  

Hàm TEXT (value,format_text) có hai tham số: 

• Trong tham số đầu tiên (value), bạn cung cấp một số hoặc ngày tháng sẽ được chuyển đổi thành văn bản hoặc tham chiếu đến ô chứa giá trị số.  
• Trong tham số thứ hai (format_text), bạn nhập định dạng mong muốn bằng cách sử dụng các mã mà hàm TEXT có thể hiểu được.  

Chúng ta đã thảo luận về một công thức như vậy trong phần đầu của hướng dẫn này để nối văn bản và ngày tháng. Tôi sẽ nhắc bạn rằng khi kết hợp chuỗi văn bản và ngày, bạn phải sử dụng chức năng TEXT để hiển thị ngày ở định dạng mong muốn. Ví dụ: 

=CONCATENATE(“Today is “, TEXT(TODAY(), “mm/dd/yy”)) 

hoặc 

=”Today is ” & TEXT(TODAY(), “mm/dd/yy”)  

ví dụ công thức nối giá trị văn bản và số theo sau: 

=A2 & ” ” & TEXT(B2, “$#,#0.00”) – hiển thị số có 2 chữ số thập phân và dấu $.  

=A2 & ” ” & TEXT(B2, “0.#”) – không hiển thị thêm các số 0 và dấu $.  

=A2 & ” ” & TEXT(B2, “# ?/???”) – hiển thị số dưới dạng phân số.

Cách chia ô (đối lập với CONCATENATE trong Excel) 

Nếu bạn đang tìm kiếm đối lập với CONCATENATE trong Excel, tức là bạn muốn chia một ô thành nhiều ô, một số tùy chọn có sẵn cho bạn: 

• Tính năng Text to Columns 
• Tùy chọn Flash Fill trong Excel 2013 và 2016 
• Hàm (hàm MID, RIGHT, LEFT) 

Bạn có thể tìm thấy các bước chi tiết được minh họa bằng ví dụ về công thức và hình trong hướng dẫn Cách tách ô trong Excel bằng cách xuống dòng.

Nối các hàng theo từng cột 

Để kết hợp dữ liệu trong từng hàng, từng cột riêng lẻ, bạn chọn hợp nhất các Rows, chọn dấu phân cách bạn muốn (ngắt dòng trong ví dụ này), định cấu hình các cài đặt khác theo cách bạn muốn và nhấn Nút Merge. Kết quả có thể giống như sau:

Để kiểm tra xem bổ trợ Merge Cells sẽ xử lý như thế nào với tập dữ liệu của bạn, bạn có thể sử dụng liên kết bên dưới để tải xuống phiên bản dùng thử đầy đủ chức năng của Ultimate Suite dành cho Excel của chúng ta. Và nếu bạn hài lng với kết quả, bạn có thể nhận được nó với mức giá rất đặc biệt.
 Bài hướng dẫn concatenate trong Excel để tách giá trị các chuỗi đã kết thúc rồi. Các bạn còn thắc mắc gì liên qua chủ đề này thì hãy comment xuống dưới để đực giải đáp miễn phí nhanh và chính xác nhất nhé!