Chris có một trang tính có tên khách hàng trong các cột từ A đến F. Trong cột G, anh ấy muốn bao gồm một công thức sẽ lấy tất cả các tên từ sáu cột tên và nối chúng thành một chuỗi dài, với các ký tự // giữa mỗi tên . Có thể không có tên trong tất cả sáu cột và không được có // dấu phân cách đầu hoặc cuối không liên quan.

Nối văn bản trong Excel rất dễ dàng. Ví dụ: nếu bạn có một cái gì đó trong ô A2 và bạn muốn nối nó với những gì trong ô B2, bạn có thể làm như vậy với công thức sau:

=A2 & B2

Bạn có thể thêm // dấu phân cách giữa hai giá trị bằng cách thêm chúng vào vị trí thích hợp:

=A2 & "//" & B2

Điều này là khá dễ dàng. Sử dụng phương pháp này, bạn có thể ghép tất cả sáu tên bằng công thức sau:

=A2 & "//" & B2 & "//" & C2 & "//" & D2 & "//" & E2 & "//" & F2

Khi mọi thứ trở nên khó khăn là khi bạn nhận ra rằng một số ô đó có thể không có gì trong đó. Do đó, công thức sẽ dẫn đến dấu phân cách sau hoặc kết thúc // hoặc trong dấu phân cách kép (////)

ở giữa kết quả.

Giải pháp rõ ràng là sử dụng câu lệnh IF để kiểm tra nội dung của các ô tên trước khi nối chúng. Tuy nhiên, điều này có thể dẫn đến một số công thức dài đáng kinh ngạc. Ví dụ, công thức sau sẽ thực hiện chính xác việc kiểm tra và nối:

=IF(RIGHT(CONCATENATE(IF(A3="","",CONCATENATE(A3,"//")), IF(B3="","",CONCATENATE(B3,"//")),IF(C3="","",CONCATENATE(C3,"//")), IF(D3="","",CONCATENATE(D3,"//")),IF(E3="","",CONCATENATE(E3,"//")), IF(F3="","",F3)),2)="//",LEFT(CONCATENATE(IF(A3="","", CONCATENATE(A3,"//")),IF(B3="","",CONCATENATE(B3,"//")), IF(C3="","",CONCATENATE(C3,"//")),IF(D3="","",CONCATENATE(D3,"//")), IF(E3="","",CONCATENATE(E3,"//")),IF(F3="","",F3)), LEN(CONCATENATE(IF(A3="","",CONCATENATE(A3,"//")), IF(B3="","",CONCATENATE(B3,"//")),IF(C3="","",CONCATENATE(C3,"//")), IF(D3="","",CONCATENATE(D3,"//")),IF(E3="","",CONCATENATE(E3,"//")), IF(F3="","",F3)))-2),CONCATENATE(IF(A3="","",CONCATENATE(A3,"//")), IF(B3="","",CONCATENATE(B3,"//")),IF(C3="","",CONCATENATE(C3,"//")), IF(D3="","",CONCATENATE(D3,"//")),IF(E3="","",CONCATENATE(E3,"//")), IF(F3="","",F3)))

Có, đây là công thức một dòng. (Chà!) Công thức này sử dụng phương pháp lồng các câu lệnh IF để đạt được kết quả mong muốn. Điều này có thể hoạt động trong trường hợp cụ thể này, nhưng công thức chạy rất gần với giới hạn của Excel là chỉ cho phép các câu lệnh IF được lồng vào nhau sâu đến bảy cấp.

Giải pháp cho vấn đề các mức lồng nhau tiềm ẩn là không lồng các câu lệnh IF. Thay vào đó, bạn có thể đánh giá từng ô riêng lẻ và nối bất kỳ ô nào được trả về.

=MID(IF(ISTEXT(A3),"//"&A3,"") & IF(ISTEXT(B3),"//"&B3,"")

& IF(ISTEXT(C3),"//"&C3,"") & IF(ISTEXT(D3),"//"&D3,"") & IF(ISTEXT(E3),"//"&E3,"") & IF(ISTEXT(F3),"//"&F3,""),3,2000)

Lưu ý rằng công thức này ngắn hơn nhiều. Bạn có thể thấy rõ hơn nó đang làm gì nếu bạn nhìn vào công thức được “chia nhỏ” thành nhiều dòng:

=MID( IF(ISTEXT(A3),"//"&A3,"") & IF(ISTEXT(B3),"//"&B3,"") & IF(ISTEXT(C3),"//"&C3,"") & IF(ISTEXT(D3),"//"&D3,"") & IF(ISTEXT(E3),"//"&E3,"") & IF(ISTEXT(F3),"//"&F3,""),3,2000)

Mỗi câu lệnh IF riêng lẻ trong công thức sẽ đánh giá một ô tên và không trả về gì (“”) nếu ô đó không chứa văn bản hoặc trả về dấu phân tách (//) theo sau tên. Sau đó, toàn bộ công thức được đặt trong câu lệnh MID để cắt bỏ // dấu phân cách đầu tiên trong chuỗi.

Công thức này có thể được rút ngắn hơn nữa nếu thay vì sử dụng hàm ISTEXT để đánh giá các ô, bạn chỉ cần thực hiện so sánh Boolean để tìm xem có văn bản nào trong ô hay không, như sau:

=MID(IF(A3>"","//"&A3,"") & IF(B3>"","//"&B3,"") & IF(C3>"","//"& C3,"") & IF(D3>"","//"&D3,"") & IF(E3>"","//"&E3,"") & IF(F3>"","//"&F3,""),3,2000)

Đây là kỹ thuật hoàn toàn tương tự, chỉ ngắn hơn một chút. (Và ngắn hơn một chút so với công thức gốc.)

Công thức này sẽ hoạt động tốt, miễn là các giá trị trong ô tên là văn bản. Nếu các cột tên của bạn có giá trị số trong đó vì lý do nào đó, bạn có thể dễ dàng sửa đổi công thức để sử dụng ISBLANK thay vì ISTEXT, như được hiển thị ở đây:

=MID(IF(ISBLANK(A3),"","//"&A3) & IF(ISBLANK(B3),"","//"&B3)

& IF(ISBLANK(C3),"","//"& C3) & IF(ISBLANK(D3),"","//"&D3)

& IF(ISBLANK(E3),"","//"&E3) & IF(ISBLANK(F3),"","//"&F3),3,2000)

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (2173) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003.