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"

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
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
  - SergeiBaklan
    Diamond Contributor
    Apr 20, 2020
    DougB77
    
    As variant
    
    =LEFT( CONCAT(IF(INDEX(J16:W16,1,{1,4,6,8,10,12,14})="","", INDEX(J16:W16,1,{1,4,6,8,10,12,14})&"-PHYSICIAN, ")), LEN( CONCAT(IF(INDEX(J16:W16,1,{1,4,6,8,10,12,14})="","", INDEX(J16:W16,1,{1,4,6,8,10,12,14})&"-PHYSICIAN, ")) )-2)
    
    entered with Ctrl+Shift+Enter

Resources