Excel多条件IF空值处理：带分号的单元格内容合并需求

Hey there, let's get that cell merging sorted out properly—your current formula adds unnecessary semicolons when there are empty cells, but we can fix that with two great options depending on your Excel version.

Best Option (Excel 2019/365 or Later) #

Use the TEXTJOIN function—it's designed exactly for this kind of job, handling delimiters and empty cells automatically:

=TEXTJOIN(";", TRUE, P1, R1)

Here's how it covers all your requirements:

• Both P1 and R1 empty: Returns an empty cell (no extra characters)
• P1 empty, R1 non-empty: Shows only R1's value (no leading semicolon)
• Both cells have values: Merges them with a clean semicolon separator (e.g., ValueP;ValueR)
• P1 non-empty, R1 empty: Shows only P1's value (no trailing semicolon)

Alternative for Older Excel Versions (Pre-2019) #

If you don't have access to TEXTJOIN, use a nested IF formula to explicitly handle each case:

=IF(AND(P1="", R1=""), "", IF(P1="", R1, IF(R1="", P1, P1&";"&R1)))

Let's break this down:

1. First check if both cells are empty—if yes, return nothing
2. If only P1 is empty, return R1's value
3. If only R1 is empty, return P1's value
4. If both have values, concatenate them with a semicolon

Either formula will meet all your requirements without those annoying extra semicolons!

内容的提问来源于stack exchange，提问作者James