Cách Nối (Concatenate) trong Excel – Hướng Dẫn Đầy Đủ

Trong bài hướng dẫn này, chúng ta sẽ khám phá các cách để nối (concatenate) các ô, cột và vùng dữ liệu trong Excel. Vì vậy, không chần chừ nữa, hãy cùng bắt đầu.

Nối (Concatenation) là gì?

Nối trong Excel có nghĩa là kết hợp dữ liệu từ nhiều cột trên cùng một hàng thành một ô duy nhất. Hãy cùng hiểu khái niệm này qua một ví dụ đơn giản.

Như trong ảnh trên, chúng ta có một bảng tính với các cột Họ (Surname) và Tên (Given name). Giả sử chúng ta muốn tạo thêm một cột Họ và Tên (Full Name). Để tạo dữ liệu cho cột này, chúng ta có thể dùng hàm nối chuỗi như CONCATENATE để kết hợp cột Họ và Tên, sau đó hiển thị kết quả ở cột Họ và Tên.

Ngoài hàm CONCATENATE, Excel còn cung cấp một vài hàm và toán tử tích hợp khác để nối dữ liệu. Trong bài viết này, chúng ta sẽ xem qua tất cả các hàm và toán tử đó và cố gắng hiểu cách chúng hoạt động.

Nhưng trước hết, hãy cùng phân biệt nối (concatenation) và gộp ô (merging).

Cách Nối (Concatenation) Khác Với Gộp Ô (Merging Cells)

Mặc dù cả hai thao tác (nối ô và gộp ô) trông có vẻ giống nhau, nhưng kết quả thu được lại rất khác nhau.

• Gộp ô không đồng nghĩa với việc kết hợp dữ liệu, mà chỉ là gộp các ô lại thành một ô lớn hơn. Khi gộp, dữ liệu được giữ lại chỉ từ ô trên‑trái.

Hãy hiểu qua một ví dụ: như trong ảnh, khi gộp các ô, chỉ giá trị của ô trên‑trái được giữ lại, các giá trị còn lại sẽ bị loại bỏ.

Như bạn thấy ở trên, khi gộp ô Họ và Tên, ta chỉ còn lại giá trị Lionel (tức là Họ) trong ô sau khi gộp, trong khi giá trị Messi (Tên) bị bỏ qua. Ngược lại, nối sẽ lấy dữ liệu từ các ô được chọn và kết hợp chúng trong một ô riêng biệt (nơi dùng hàm hoặc toán tử nối).

Các hàm CONCAT và CONCATENATE trong Excel

CONCAT và CONCATENATE là hai hàm có sẵn trong Excel để thực hiện việc nối. CONCAT là hàm mới, đã thay thế hàm CONCATENATE từ Excel 2019 trở lên.

Mặc dù hàm CONCATENATE đã được xem là lạc hậu, nhưng nó vẫn tồn tại trong Excel 2019 và Office 365 để đảm bảo tính tương thích ngược.

Cú pháp của hàm CONCAT

“`excel

=CONCAT(text1, [text2], ...)

“`

Tham số:

• text1 – có thể là tham chiếu ô, phạm vi ô hoặc một giá trị văn bản.
• text2 – là tham số tùy chọn, cũng có thể là tham chiếu ô, phạm vi ô hoặc một giá trị văn bản.

Cú pháp của hàm CONCATENATE

“`excel

=CONCATENATE(text1, text2, [text3], ...)

“`

Tham số:

• text1 – có thể là tham chiếu ô hoặc một giá trị văn bản.
• text2 – có thể là tham chiếu ô hoặc một giá trị văn bản.
• text3 – là tham số tùy chọn, có thể là tham chiếu ô hoặc một giá trị văn bản.

Ví dụ đơn giản

“`excel

=CONCATENATE(A2," ",B2)   // Nối bằng CONCATENATE

=CONCAT(A2," ",B2)        // Nối bằng CONCAT

“`

Cả hai công thức đều có ba tham số – A2 là ô chứa giá trị đầu tiên, ” “ (dấu cách) là tham số thứ hai để chèn khoảng trắng giữa hai chuỗi, và B2 là ô chứa giá trị thứ hai.

Bạn cũng có thể truyền trực tiếp các giá trị văn bản thay vì tham chiếu ô, kết quả vẫn sẽ như sau:

“`excel

=CONCATENATE("Lionel"," ","Messi")   // Kết quả: Lionel Messi

=CONCAT("Lionel"," ","Messi")        // Kết quả: Lionel Messi

“`

Toán tử Nối (&) trong Excel

Bên cạnh hai hàm trên, Excel còn cho phép dùng toán tử & để nối các chuỗi. Toán tử này thực hiện mọi việc mà hàm CONCATENATE làm được.

Phương pháp này rất tiện lợi khi chỉ cần nối một vài chuỗi, nhưng nếu muốn nối nhiều chuỗi, việc sử dụng toán tử & sẽ trở nên khó khăn vì không có tính năng tự động như hàm CONCATENATE.

Hãy tái tạo lại ví dụ trên bằng toán tử &.

Kết quả thu được giống như khi dùng hàm CONCATENATE, chỉ khác về cú pháp.

Cú pháp của toán tử nối (&)

“`excel

=text1&text2&text3&…&textN

“`

Tham số:

• text1, text2, text3, … – có thể là tham chiếu ô hoặc một giá trị văn bản.

Ví dụ:

“`excel

="Lionel"&" "&"Messi"   // Kết quả: Lionel Messi

“`

So sánh CONCATENATE, CONCAT và toán tử &

Ba phương pháp trên đều cho ra kết quả tương tự, nhưng có một số điểm khác biệt quan trọng:

• CONCAT có thể xử lý phạm vi ô (range), trong khi CONCATENATE không thể.

• Toán tử & cũng không hỗ trợ phạm vi ô.
• Hàm CONCATENATE chỉ cho phép nối tối đa 255 chuỗi, còn toán tử & không có giới hạn như vậy.

Thực tế, 255 chuỗi là một con số lớn và hầu hết người dùng không cần nối tới mức đó, vì vậy sự khác biệt thực chất chỉ còn lại độ tiện dụng. Ngoài ra, cần lưu ý rằng hàm CONCATENATE đã bị đánh dấu là lỗi thời và có thể bị gỡ bỏ trong các phiên bản Excel tương lai.

Nối trong Excel bằng hàm TEXTJOIN

Hàm TEXTJOIN xuất hiện từ Excel 2019, cung cấp một cách khác để kết hợp văn bản từ nhiều ô thành một ô duy nhất. TEXTJOIN cho phép truyền phạm vi ô và có tham số ignore_empty để bỏ qua các ô trống.

Một điểm đặc biệt của TEXTJOIN là khả năng truyền delimiter (ký tự phân tách) ngay trong hàm.

Cú pháp của hàm TEXTJOIN

“`excel

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

“`

Tham số:

• delimiter – ký tự hoặc chuỗi dùng làm dấu phân cách (có thể là tham chiếu ô).
• ignore_empty – TRUE hoặc FALSE, chỉ định có bỏ qua các ô trống hay không.
• text1, text2, … – có thể là tham chiếu ô, phạm vi ô hoặc một giá trị văn bản.

Ví dụ

Trong ảnh, hàm TEXTJOIN được dùng để nối giá trị của ô A2 và B2. Đầu vào đầu tiên là dấu phân cách – ở đây là một khoảng trắng ” “. Tham số thứ hai được đặt là TRUE, nghĩa là bỏ qua các ô trống. Các tham số tiếp theo là các tham chiếu ô A2 và B2. Vì TEXTJOIN cũng chấp nhận phạm vi, nên thay vì liệt kê từng ô, chúng ta có thể dùng A2:B2 và vẫn nhận được kết quả giống nhau.

Cách Nối Hai hoặc Nhiều Cột Có hoặc Không Có Dấu Phân Cách

Để nối dữ liệu từ nhiều cột và chèn dấu phân cách (dấu phẩy, khoảng trắng, ngắt dòng …), bạn có thể dùng bất kỳ hàm nào trong ba hàm trên cùng với toán tử &. Dưới đây là các ví dụ cho từng cách.

Nối Nhiều Cột với Dấu Phân Cách bằng CONCATENATE

“`excel

=CONCATENATE(A2,B2,C2,D2)                     // Không có dấu phân cách

=CONCATENATE(A2,",",B2,",",C2,",",D2)   // Dấu phẩy ","

=CONCATENATE(A3," ",B3," ",C3," ",D3)  // Khoảng trắng " "

=CONCATENATE(A4,CHAR(10),B4,CHAR(10),C4,CHAR(10),D4) // Ngắt dòng (linebreak)

“`

Lưu ý 1: Để chèn ký tự ngắt dòng, dùng `CHAR(10)` (line feed cho Windows) hoặc `CHAR(13)` (carriage return cho các hệ thống Mac cũ).

Lưu ý 2: Để hiển thị đúng ngắt dòng, bật tùy chọn Wrap text (định dạng ô → Alignment → Wrap text).

Nối Nhiều Cột với Dấu Phân Cách bằng Toán tử &

“`excel

=A2&B2&C2&D2                                 // Không có dấu phân cách

=A2&","&B2&","&C2&","&D2               // Dấu phẩy ","

=A3&" "&B3&" "&C3&" "&D3                 // Khoảng trắng " "

=A4&CHAR(10)&B4&CHAR(10)&C4&CHAR(10)&D4      // Ngắt dòng

“`

Nối Nhiều Cột với Dấu Phân Cách bằng CONCAT

“`excel

=CONCAT(A2,B2,C2,D2)                         // Không có dấu phân cách

=CONCAT(A2:D2)                                 // Không có dấu phân cách, chấp nhận phạm vi

=CONCAT(A2,",",B2,",",C2,",",D2)        // Dấu phẩy ","

=CONCAT(A3," ",B3," ",C3," ",D3)        // Khoảng trắng " "

=CONCAT(A4,CHAR(10),B4,CHAR(10),C4,CHAR(10),D4) // Ngắt dòng

“`

Nối Nhiều Cột với Dấu Phân Cách bằng TEXTJOIN (Yêu Thích Cá Nhân)

“`excel

=TEXTJOIN("",TRUE,A2:D2)   // Không có dấu phân cách

=TEXTJOIN(",",TRUE,A2:D2) // Dấu phẩy ","

=TEXTJOIN(" ",TRUE,A3:D3) // Khoảng trắng " "

=TEXTJOIN(CHAR(10),TRUE,A4:D4) // Ngắt dòng

“`

Vì TEXTJOIN chấp nhận phạm vi ô, nên không cần liệt kê từng ô, giúp công thức ngắn gọn, dễ đọc và debug.

Cách Nối Một Vùng Ô Có hoặc Không Có Dấu Phân Cách

Khi cần nối dữ liệu từ một vùng ô (range) thay vì các cột rời rạc, chúng ta có thể dùng CONCAT hoặc TEXTJOIN.

Hàm CONCAT với Vùng Ô

• CONCAT có thể làm việc với cả phạm vi ngang và phạm vi dọc, nhưng không cho phép chỉ định dấu phân cách.

“`excel

=CONCAT(A2:D2)   // Nối các ô ngang A2:D2

“`

“`excel

=CONCAT(A2:A5)   // Nối các ô dọc A2:A5

“`

Bạn cũng có thể nối hai phạm vi riêng biệt:

“`excel

=CONCAT(A2:D2, A5:A8) // Nối phạm vi ngang A2:D2 và dọc A5:A8

“`

Nhược điểm duy nhất là không thể chèn dấu phân cách khi dùng CONCAT.

Hàm TEXTJOIN với Vùng Ô

TEXTJOIN hỗ trợ cả phạm vi ngang và phạm vi dọc, đồng thời cho phép chỉ định dấu phân cách.

• Không có dấu phân cách:

“`excel

=TEXTJOIN("",TRUE,A2:D2)

“`

• Có dấu phân cách (ví dụ: dấu gạch nối “-“):

“`excel

=TEXTJOIN("-",TRUE,A2:D2)

“`

• Phạm vi dọc:

“`excel

=TEXTJOIN("",TRUE,A2:A5)   // Không có dấu phân cách

=TEXTJOIN("-",TRUE,A2:A5)   // Dấu gạch nối "-"

“`

• Kết hợp nhiều phạm vi (nằm ngang và dọc) và có/không có dấu phân cách:

“`excel

=TEXTJOIN("",TRUE,A2:D2,A6:A9)   // Không có dấu phân cách

=TEXTJOIN("-",TRUE,A2:D2,A6:A9) // Dấu gạch nối "-"

“`

Sử Dụng TEXTJOIN trên Excel 2019 hoặc Các Phiên Bản Cũ hơn

Vì TEXTJOIN chỉ xuất hiện từ Excel 2019, người dùng phiên bản cũ hơn không thể dùng trực tiếp. Đối với họ, chúng ta có thể tạo UDF (User Defined Function) tùy chỉnh, mang lại chức năng tương tự TEXTJOIN.

Thêm UDF vào Sổ làm việc

• Trên Windows, nhấn ALT + F11; trên Mac, nhấn Option + F11 để mở Visual Basic for Applications (VBA) Editor.
• Trong VBA Editor, vào Insert → Module để chèn một mô-đun mới.
• Sao chép và dán đoạn mã dưới đây vào mô-đun, sau đó lưu lại.

'ConcatCellRange User Defined Function
'Created By - ExcelTrick.com
Function ConcatCellRange(delimiter As String, ignore_blanks As Boolean, text_range As Range) As String
Dim Cell As Range
Dim Result As String
For Each Cell In text_range
If Not (ignore_blanks = True And IsEmpty(Cell.Value)) Then
Result = Result & Cell.Value & delimiter
End If
Next Cell
If Right(Result, 1) = delimiter Then
ConcatCellRange = Left(Result, Len(Result) - 1)
Else
ConcatCellRange = Result
End If
End Function

Cách Dùng ConcatCellRange

Cú pháp:

“`excel

=ConcatCellRange(delimiter, ignore_blanks, text_range)

“`

Tham số:

• delimiter – ký tự hoặc chuỗi phân cách (có thể là tham chiếu ô).
• ignore_blanks – TRUE/FALSE, quyết định có bỏ qua ô trống hay không.
• text_range – phạm vi ô ngang hoặc dọc.

Ví dụ (phạm vi ngang):

• Trường hợp 1: không dùng dấu phân cách.
• Trường hợp 2: dùng dấu phân cách (ví dụ: dấu cách).
• Trường hợp 3: bỏ qua các ô trống.

Ví dụ (phạm vi dọc):

• Trường hợp 1: không dùng dấu phân cách.
• Trường hợp 2: dùng dấu gạch nối “-” làm dấu phân cách.

Tách Hoặc “Bỏ Nối” (Un‑Concatenate) Các Cột trong Excel

Bạn muốn thực hiện ngược lại – tách các ô đã nối thành các phần riêng? Dưới đây là một số cách:

• Flash Fill trong Excel
• Text to Columns
• Các công thức Excel như MID, LEFT, RIGHT

Đó là toàn bộ nội dung về cách nối (concatenate) các cột, hàng và vùng ô trong Excel. Nếu bạn có bất kỳ thủ thuật hoặc mẹo nào khác, vui lòng chia sẻ với chúng tôi.