Forum Discussion
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 needed.
You can see if this example what it does when there are blank cells.
=CONCATENATE(J16,"-PHYSICIAN",",",M16,"-PHYSICIAN",",",O16,"-PHYSICIAN",",",Q16,"-PHYSICIAN",",",S16,"-PHYSICIAN",",",U16,"-PHYSICIAN",",",W16,"-PHYSICIAN")
| Mercy General Hospital-PHYSICIAN,Mercy San Juan Medical Center-PHYSICIAN,-PHYSICIAN,-PHYSICIAN,-PHYSICIAN,-PHYSICIAN,-PHYSICIAN |
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")
6 Replies
- Try this:
=TEXTJOIN("-PHYSICIAN, ",TRUE,J16,L16,N16,P16,R16,T16,V16)&"-PHYSICIAN"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")
