Apr 20 2020 08:53 AM
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 |
Apr 20 2020 09:40 AM
Apr 20 2020 09:46 AM
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?
Apr 20 2020 10:00 AM
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")
Apr 20 2020 10:18 AM
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
Apr 20 2020 10:45 AM
SolutionOK 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")
Apr 20 2020 11:04 AM
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
Apr 20 2020 10:45 AM
SolutionOK 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")