Forum Discussion

Copper Contributor

Apr 20, 2020

Solved

Excel formula needs IF statement

I currently have the below formula but I am getting the -PHYSICAIN when a cell is blank and I am not sure how to add an IF statement to the formula or if there is a better way to produce the output n...

Formulas and Functions

DhaniCole
Apr 20, 2020
DougB77
OK I'm glad it worked. You would have to delete the space after each comma:
=IF(ISBLANK(J17),"",J17&"-PHYSICIAN")&IF(ISBLANK(M17),"",","&M17&"-PHYSICIAN")&IF(ISBLANK(O17),"",","&O17&"-PHYSICIAN")&IF(ISBLANK(Q17),"",","&Q17&"-PHYSICIAN")&IF(ISBLANK(S17),"",","&S17&"-PHYSICIAN")&IF(ISBLANK(U17),"",","&U17&"-PHYSICIAN")&IF(ISBLANK(W17),"",","&W17&"-PHYSICIAN")

Brass Contributor

Apr 20, 2020

Try this:
=TEXTJOIN("-PHYSICIAN, ",TRUE,J16,L16,N16,P16,R16,T16,V16)&"-PHYSICIAN"

DougB77
Copper Contributor
Apr 20, 2020
Thank you for the reply but it returns #NAME? it also isn't going to add the "-PHYSICIAN". or should your formula somehow be added to my formula?
DhaniCole
- DhaniCole
  Brass Contributor
  Apr 20, 2020
  DougB77
  Modified (I skipped one column each, but your first column skipped two, so that may be why the first attempt didn't work):
  =TEXTJOIN("-PHYSICIAN, ",TRUE,J16,M16,O16,Q16,S16,U16,W16)&"-PHYSICIAN"
  
  If that doesn't work, this should (one row down though):
  =IF(ISBLANK(J17),"",J17&"-PHYSICIAN")&IF(ISBLANK(M17),"",", "&M17&"-PHYSICIAN")&IF(ISBLANK(O17),"",", "&O17&"-PHYSICIAN")&IF(ISBLANK(Q17),"",", "&Q17&"-PHYSICIAN")&IF(ISBLANK(S17),"",", "&S17&"-PHYSICIAN")&IF(ISBLANK(U17),"",", "&U17&"-PHYSICIAN")&IF(ISBLANK(W17),"",", "&W17&"-PHYSICIAN")
  - DougB77
    Copper Contributor
    Apr 20, 2020
    The second one appears to work correctly, but it needs to not have a space after the comma. before I break anything I want to confirm what part of this I would remove to eliminate that space.
    =IF(ISBLANK(J2),"",J2&"-PHYSICIAN")&IF(ISBLANK(M2),"",", "&M2&"-PHYSICIAN"
    
    I really appreciate your help with this
    
    DhaniCole

Resources