This formula displays 0 when there is no value in the Cell

Hello @SACHIN B,  

Thank you for posting your valuable question on Microsoft Q&A forum.   

Based on your formula and the issue you’re facing, here’s what I understand: 

• You’re trying to combine values from AD2 and AD3, remove duplicates, and ignore blanks. 
• The current formula using FILTERXML is returning unexpected results like 0,8 or 0,2 because empty <b> tags are created when a cell is blank. Excel interprets these empty nodes as 0. 

Please let me know if I misunderstood any part of your issue or you have any additional information to share. 

Here are a few options you may want to try:  

Option 1: Keep XML but ignore empty tags 

Add an XPath condition to filter out empty <b> nodes: 

=TEXTJOIN(", ", TRUE, 

  UNIQUE( 

    FILTERXML( 

      "<a><b>" & SUBSTITUTE(AD2 & "," & AD3, ",", "</b><b>") & "</b></a>", 

      "//b[normalize-space()!='']" ))) 

This ensures blanks are ignored. 

Option 2: Use modern Excel functions (simpler) 

If you have Excel 365 or Excel 2021+, you can avoid XML entirely: 

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(AD2:AD3, AD2:AD3 <> ""))) 

• FILTER removes blanks. 
• UNIQUE removes duplicates. 
• TEXTJOIN combines values. 

Please let me know if this meets your expectations. Thank you for reaching out!

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.