Công thức chung
=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1)," ",", ")
Tóm lược
Để nối nhiều giá trị ô bằng dấu phẩy, bạn có thể sử dụng công thức dựa trên hàm SUBSTITUTE và TRIM. Bạn có thể sử dụng cùng một cách tiếp cận này để nối các giá trị trong ô với bất kỳ dấu phân cách nào bạn thích. Trong ví dụ được hiển thị, công thức trong G5 là:
=SUBSTITUTE(TRIM(B5&" "&C5&" "&D5&" "&E5&" "&F5)," ",", ")
Giải trình
Làm việc từ trong ra ngoài, đầu tiên công thức nối các giá trị của 5 ô bên trái bằng cách sử dụng toán tử nối (&) và một khoảng trắng duy nhất giữa mỗi giá trị:
B5&" "&C5&" "&D5&" "&E5&" "&F5
Phần này của công thức khá thủ công. Để tăng tốc độ, hãy sao chép & "" & vào khay nhớ tạm trước khi bạn bắt đầu. Sau đó làm theo mẫu sau:
(nhấp chuột) (dán) (nhấp chuột) (dán) (nhấp chuột) (dán)
cho đến khi bạn nhận được tham chiếu ô cuối cùng. Nó thực sự đã đi vào quá khứ.
Kết quả của việc nối này (trước khi TRIM và SUBSTITUTE chạy) là một chuỗi như sau:
"figs apples "
Tiếp theo, hàm TRIM mà chúng tôi sử dụng để "chuẩn hóa" tất cả các khoảng cách. TRIM tự động tách khoảng trắng ở đầu và cuối của một chuỗi nhất định và chỉ để lại một khoảng trắng giữa tất cả các từ bên trong chuỗi. Điều này sẽ xử lý các khoảng trống thừa do các ô trống gây ra.
"figs apples"
Cuối cùng, SUBSTITUTE được sử dụng để thay thế mỗi dấu cách ("") bằng dấu phẩy và dấu cách (","), trả về văn bản như sau:
"figs, apples"
Nối các ô với các dấu phân cách khác
Để nối các ô với một dấu phân tách (dấu phân tách) khác, chỉ cần điều chỉnh đối số "new_text" bên trong SUBSTITUTE. Ví dụ: để nối các ô với dấu gạch chéo lên, hãy sử dụng:
=SUBSTITUTE(TRIM(B7&" "&C7&" "&D7&" "&E7&" "&F7)," ","/")
Đầu ra sẽ như thế này:
limes/apricots/apricots/limes/figs
Hàm TEXTJOIN
Hàm TEXTJOIN là một hàm mới có sẵn trong Office 365 và Excel 2019. TEXTJOIN cho phép bạn nối một dải ô bằng dấu phân cách. Với TEXTJOIN, ví dụ trên sẽ giống như sau:
=TEXTJOIN(", ",TRUE,B5:F5)
Macro
Jon Acampora tại ExcelCampus có một macro tuyệt vời để nối một loạt các ô.
