Kết hợp các ô trong Excel hay sử dụng hàm nối chuỗi chính là chủ đề của bài viết tiếp theo của chúng ta. Trong bài viết này, 123job.vn sẽ cung cấp cho bạn các cách giải quyết để nối các chuỗi trong Excel – bằng cách sử dụng hàm nối chuỗi CONCATENATE.

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

I. Khái niệm “Concatenate” trong Excel là gì?

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

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

=CONCATENATE (text1, [text2], …)

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

II. Hàm CONCATENATE

1. Kết nối các giá trị của một số ô

Công thức hàm nối chuỗi trong Excel CONCATENATE đơn giản nhất để có thể 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ị được cho trước sẽ được đan lại với nhau mà không có dấu phân tách, hiển thị như hàng 2 trong hình bên dưới.

Để có thể tách các giá trị khác nhau với khoảng trắng, hãy nhập “” vào đối số thứ hai, như phần dữ liệu ở dòng 3 trong ảnh chụp màn hình dưới đây.

=CONCATENATE(A1, ” “, B1)

Kết nối các giá trị của một số ôKết nối các giá trị của một số ô

2. Kết nối chuỗi văn bản và giá trị di động bằng hàm nối chuỗi

Không có điều gì hạn chế hàm nối chuỗi - hàm CONCATENATE chỉ nối giá trị của ô. Bạn có thể sử dụng nó để thực hiện nối các chuỗi văn bản khác nhau cho trước để làm cho kết quả có ý nghĩa hơn. Ví dụ:

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

Công thức hàm nối chuỗi trên cho người dùng biết rằng công việc nào đó đã hoàn thành, như được hiển thị ở hàng hai trong hình dưới đây. Xin lưu ý rằng chúng ta có thể thêm một dấu cách trước phần chữ “completed” để tách các chuỗi văn bản được nối.

Đương nhiên, bạn có thể thực hiện thêm một chuỗi văn bản ngay từ đầu hoặc ở giữa công thức hàm nối chuỗi - hàm Concatenate của bạn:

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

Một khoảng trống (“”) được thêm vào ở phần giữa các giá trị kết hợp, để kết quả hiển thị của hàm nối chuỗi 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 bằng hàm nối chuỗiKết nối chuỗi văn bản và giá trị di động bằng hàm nối chuỗi

3. Kết nối một chuỗi văn bản xác định và một giá trị từ trong công thức tính toán

Để làm cho kết quả mà hàm nối chuỗi trả lại bằng một số công thức dễ hiểu hơn cho người dùng, bạn có thể thực hiện 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 hàm nối chuỗi 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 xác định và một giá trị từ trong công thức tính toánKết nối một chuỗi văn bản xác định và một giá trị từ trong công thức tính toán

III. Những điều cần nhớ khi sử dụng hàm nối chuỗi CONCATENATE trong Excel

1. Dùng ký tự “&” để kết nối các chuỗi trong Excel

Trong Microsoft Excel, toán tử &  là một cách đặc biệt khác để nối các ô. Phương pháp này thực sự có ích trong nhiều trường hợp bởi vì việc gõ (&) nhanh hơn nhiều so với thao tác gõ “concatenate”.

Tương tự với hàm CONCATENATE, bạn có thể sử dụng “&” trong Excel thay cho hàm nối chuỗi để có thể 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ề ngay sau đó bởi các hàm khác.

2. Ví dụ về “&”

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

Kết nối các giá trị được cho trong A1 và B1:

=A1&B1

Kết nối các giá trị được cho trong A1 và B1 với khoảng trống:

=A1&” “&B1

Kết nối các giá trị được cho trong 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ả trực tiếp của hàm TEXT/TODAY:

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

Như đã trình bày trong ảnh chụp màn hình của chúng ta bên dưới, hàm CONCATENATE và toán tử “&” có thể trả kết quả giống hệt nhau:

Ví dụ về “&”Ví dụ về “&”

3. “&” với hàm CONCATENATE - hàm nối chuỗi trong Excel

Nhiều người dùng tự hỏi rằng liệu có tồn tại cách nào hiệu quả hơn để nối các chuỗi trong hàm nối chuỗi trong Excel - hàm CONCATENATE hoặc toán tử “&” hay không.

Sự khác biệt duy nhất mà chúng ta cần biết giữa hàm CONCATENATE và “&” là hàm CONCATENATE có giới hạn 255 ký tự còn & thì không có. Ngoài ra, không có gì khác nhau giữa hai phương pháp ghép nối ký tự này, cũng không có bất kỳ sự khác biệt về hiệu lực giữa các công thức hàm nối chuỗi CONCATENATE và “&”.

Vì 255 ký tự là cũng một con số thực sự lớn và trong thực tế, có thể không bao giờ chúng ta dùng hết khi kết hợp nhiều chuỗi bằng hàm nối chuỗi, sự khác biệt còn lại có thể 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 nối chuỗi CONCATENATE dễ đọc hơn, tuy vậy cũng không ít người thích sử dụng phương pháp “&”. Vì vậy, chỉ cần tùy ý dùng kỹ thuật nối mà bạn cảm thấy thoải mái hơn.

4. Kết nối các ô bằng dấu cách, dấu phẩy và các ký tự khác bằng hàm nối chuỗi

Trong bảng tính Microsoft Excel của bạn, bạn có thể thường phải nhập các giá trị cụ thể có dấu phẩy, dấu cách, dấu chấm câu hoặc có thể là các ký tự khác như dấu gạch ngang hoặc dấu gạch chéo. Để làm điều này, chỉ cần nhập vào hàm nối chuỗi dòng ký tự bao gồm ký tự mà bạn muốn dùng trong công thức nối của bạn. Hãy nhớ kèm theo ký tự đặc biệt đó trong dấu ngoặc kép, như trong các ví dụ sau đây.

Kết nối hai ô với một khoảng trắng bằng hàm nối chuỗi:

=CONCATENATE(A1, ” “, B1) hoặc là =A1 & ” ” & B1

Kết nối hai ô với dấu phẩy bằng hàm nối chuỗi:

=CONCATENATE(A1, “, “, B1) hoặc là =A1 & “, ” & B1

Kết nối hai ô với một dấu nối bằng hàm nối chuỗi:

=CONCATENATE(A1, “-“, B1) hoặc là =A1 & “-” & B1

Hình sau đây thể hiện kết quả trả về của hàm nối chuỗi trên sẽ như thế nào:

Kết nối các ô bằng dấu cách, dấu phẩy và các ký tự khác bằng hàm nối chuỗiKết nối các ô bằng dấu cách, dấu phẩy và các ký tự khác bằng hàm nối chuỗi

5. Kết nối các chuỗi văn bản với các xuống dòng bằng hàm nối chuỗi

Thông thường, bạn sẽ thực hiện tách các chuỗi văn bản được nối với các dấu chấm câu và dấu cách, như đã được thể hiện trong ví dụ trước. Tuy vậy, trong một số trường hợp đặc biệt, có thể cần phải tách các giá trị bằng một ngắt dòng. Một ví dụ cực kỳ phổ biến là phải hợp nhất địa chỉ gửi thư từ dữ liệu trong các cột được cho sẵn riêng biệt.

Một vấn đề xảy ra là bạn không thể đơn giản thao tác chỉ gõ một ngắt dòng trong công thức như một ký tự được cho sẵn bình thường và do đó cần một hàm nối chuỗi CHAR với mã ASCII tương ứng cho công thức nối:

  • Trên Windows cá nhân, sử dụng công cụ CHAR (10) trong đó 10 là mã ASCII để xuống dòng.
  • Trên hệ thống Mac của mình, sử dụng CHAR (13) trong đó phần số 13 là mã ASCII .

Trong ví dụ này, chúng ta có lần lượt các phần địa chỉ trong các cột A đến F, và thực hiện ghép chúng lại với nhau trong cột G bằng cách sử dụng đúng các toán tử nối “&”. Các giá trị được thực hiện hợp nhất được phân cách bằng dấu phẩy (“,”), dấu cách (“”) và bao gồm cả 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 bằng hàm nối chuỗiKết nối các chuỗi văn bản với các xuống dòng bằng hàm nối chuỗi

Chú ý. Khi chúng ta sử dụng công cụ ngắt dòng để thực hiện tách các giá trị được nối, bạn phải thực hiện tùy chọn “Wrap text ” cho phép kết quả hiển thị chính xác. Để thực hiện việc này, hãy cùng lúc nhấn Ctrl + 1 để mở hộp thoại Format Cells, sau đó chuyển sang tab Alignment và thực hiện kiểm tra hộp Wrap text.

Theo cách tương tự, bạn có thể trực tiếp 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)

Mặc dù thế, có một cách khá là dễ dàng hơn để thực hiện hàm nối chuỗi, chèn các ký tự có thể in được trong công thức nối là chúng ta chỉ cần gõ chúng vào dấu ngoặc kép như đã thực hiện trong ví dụ trước.

Dù bằng cách nào, tất cả bốn công thức dưới đây đều sẽ mang lại cho bạn kết quả giống hệt nhau:

=A1 & CHAR(47) & B1

=A1 & “/” & B1

=CONCATENATE(A1, CHAR(47), B1)

=CONCATENATE(A1, “/”, B1)

kết quảKết quả

IV. Làm thế nào để nối các cột trong Excel

Để thực hiện hàm nối chuỗi nối hai cột trở lên trong Excel, bạn chỉ cần nhập công thức hàm nối chuỗi bình thường vào ô đầu tiên và sau đó thực hiện sao chép nó xuống các ô khác, bằng cách kéo và thả dấu cộng (hình vuông nhỏ xuất hiện ở vị trí góc dưới bên phải của ô Chọn).

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

Làm thế nào để nối các cột trong ExcelLàm thế nào để nối các cột trong Excel

V. Làm thế nào để thực hiện hàm nối chuỗi một mảng ô trong Excel

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

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

  • Chọn một ô nơi trong Microsoft Exce mà bạn muốn nhập công thức.
  • Nhập = CONCATENATE (trong ô đó hoặc có thể trong thanh công thức)
  • Nhấn và giữ CTRL, rồi đồng thời nhấp vào từng ô mà bạn muốn ghép lại.
  • Nhả nút CTRL, tiếp tục gõ dấu ngoặc để đóng trong thanh công thức và nhấn Enter.

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

2. Phương pháp 2. Sử dụng hàm TRANSPOSE để lấy phạm vi

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

Chọn ô mà bạn có nhu cầu xuất ra dải ô nối.

Nhập công thức của hàm TRANSPOSE trong ô đó, ví dụ 

=TRANSPOSE(A1:A10).

Trong thanh công thức bên trên, nhấn vào phím F9 để thay thế công thức với các giá trị xác định được tính toán.

Xoá dấu ngoặc nhọn và thực hiện biến một công thức Excel thông thường thành công thức mảng. Kết quả là, bạn sẽ có tất cả các ô tham chiếu bạn cần để thực hiện nối trong công thức hàm nối chuỗi của bạn.

Phương pháp 2. Sử dụng hàm TRANSPOSE để lấy phạm viPhương pháp 2. Sử dụng hàm TRANSPOSE để lấy phạm vi

Gõ = CONCATENATE (ở phía trước của các tham chiếu ô hiện lên trong thanh công thức, gõ dấu ngoặc đóng và cuối cùng nhấn vào Enter).

Gõ = CONCATENATEGõ = CONCATENATE

3. Concatenate với số và ngày tháng ở các định dạng khác nhau

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

Hàm TEXT (value, format_text) lần lượt có hai đối số:

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

Hãy chú ý rằng khi kết hợp một chuỗi văn bản và phần dữ liệu ngày tháng, bạn phải sử dụng tới ứng dụng của hàm TEXT để hiển thị ngày được cho trước ở định dạng mong muốn. Ví dụ có thể kết hợp trong hàm nối chuỗi như sau:

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

hoặc là

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

Một vài ví dụ về công thức bổ sung nối tiếp một giá trị văn bản và số sau:

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

=A2 & ” ” & TEXT(B2, “0.#”) – không hiển thị số không trước dấu phẩy và dấu $.

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

Concatenate với số và ngày tháng ở các định dạng khác nhauConcatenate với số và ngày tháng ở các định dạng khác nhau

VI. 2 hàm nối chuỗi trong Excel thường được dùng khác

1. Hàm nối chuỗi trong Excel Concat

Nhìn mới qua tên hàm nối chuỗi này thì chúng ta thấy hàm có các chữ cái tên thuộc phần đầu của tên hàm Concatenate. Đúng như vậy, hàm nối chuỗi này có chức năng và quá trình thực hiện công việc giống như là hàm Concatenate, tuy nhiên hàm nối chuỗi mới này được tạo thành từ sự rút gọn độ dài của tên hàm và các đối số cụ thể của nó.

Mặc dù hàm nối chuỗi Concat thay thế cho hàm Concatenate nhưng nó vẫn tiếp tục được thiết lập có sẵn trong Office/Microsoft Excel mới nhất để có thể cung cấp khả năng tương thích với các phiên bản được cho trước.

Cú pháp của hàm nối chuỗi này cũng giống như cú pháp của hàm Concatenate:

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

  • Text1: Chỉ định mục nhập văn bản tương ứng của hàm CONCAT hoặc phạm vi ô sẽ được nối, trong hàm nối chuỗi thì đây là trường bắt buộc
  • Text2:  Điều này cho phép bạn có thể thực hiện chỉ định các phạm vi khác để hàm nối chuỗi CONCAT kết hợp các văn bản, nếu phạm vi cụ thể của chúng ta không liên tục. Đây là đối số tùy chọn trong hàm nối chuỗi này.

2. Hàm nối chuỗi trong Excel TEXTJOIN

Hàm Textjoin cũng là một hàm kết hợp văn bản - hàm nối chuỗi có sẵn trong Excel 2016. Hàm nối chuỗi này có thể dễ dàng được sử dụng bao gồm một dấu phân cách giữa các chuỗi văn bản và kết hợp dấu phân cách này với một chuỗi trống.

Hàm này cũng có khả năng bỏ qua lần lượt các ô trống trong một phạm vi nhất định.

Cú pháp của hàm nối chuỗi này như sau:

=TEXTJOIN(dấu để tách, ign_empty, text1, [text2], …)

Trong cú pháp trên:

  • Dấu cách trong hàm nối chuỗi có thể là bất cứ dấu ký tự nào hay dấu phẩy, dấu cách… được đặt trong dấu ngoặc kép hoặc cũng có thể được tham chiếu trực tiếp đến một nội dung ô hợp lệ. (Cần thiết).
  • Ign Empty: Nếu đối số này có dạng TRUE, thì các ô trống trong hàm nối chuỗi sẽ bị bỏ qua. Nếu nó là False, thì các ô trống tương ứng sẽ được đưa vào. (Cần thiết)
  • text1: Chỉ định phạm vi các ô trong hàm nối chuỗi sẽ được nối, không cần nhập từng ô một. (Cần thiết)
  • [text2]: Điều này cho phép bạn chỉ định các phạm vi nối chuỗi khác để kết hợp các văn bản, nếu phạm vi trong hàm này là không liên tục. (Đối số tùy chọn).

VII. Kết

Để có thể ứng dụng tốt Microsoft Excel vào trong công việc thường ngày, chúng ta không chỉ cần nắm vững được ứng dụng của các hàm nối chuỗi, mà còn phải đảm bảo thành thạo việc kết hợp với các công cụ khác của Excel. Hãy đón đọc những bài viết về Microsoft Excel tiếp theo của 123job.vn để tích lũy thêm cho mình những kinh nghiệm và kỹ năng bổ ích nhé!