Establishing a text string based on the content of several cells and the conditions filled or empty

Dear Experts
please see formula below:
="Full Name: "&A4&" / "&"Phone: "&B4&" / "&"Mobile:  "&C4&" / "&"Extension: "&D4&" / "&"Skype: "&E4&" / "&"E-mail: "&F4&" / "&"Dept.: "&G4&" / "&"Company: "&H4

The formula works but if a cell is empty I anyway have the text entry but without value. I need to adapt the formula in such a way that I have the string only with text for that cells which have a value.
So if A4 is Nils and B4 is empty and C4 is 333 and all other cells are empty as well the result should be just: Nils / Mobile: 333

thanks Nils
Petersburg1Asked:
Who is Participating?
 
RunriggerCommented:
Try the following

=IF(A4<>"","Full Name: "&A4,"")&If(B4<>""," / "&"Phone: "&B4,"")&IF(C4<>""," / "&"Mobile:  "&C4,"")&IF(D4<>""," / "&"Extension: "&D4,"")&IF(E4<>""," / "&"Skype: "&E4,"")&IF(F4<>""," / "&"E-mail: "&F4,"")&IF(G4<>""," / "&"Dept.: "&G4,"")&IF(H4<>""," / "&"Company: "&H4,"")

0
 
Rory ArchibaldCommented:
You need to test each cell:

=IF(Len(A4),"Full Name: "&A4,"")&if(len(B4)," / "&"Phone: "&B4,"")&if(len(C4)," / "&"Mobile:  "&C4,"")&if(len(D4)," / "&"Extension: "&D4,"")&IF(LEN(E4)," / "&"Skype: "&E4,"")&IF(LEN(F4)," / "&"E-mail: "&F4,"")&IF(LEN(G4)," / "&"Dept.: "&G4,"")&IF(LEN(H4)," / "&"Company: "&H4,"")

I've assumed the name will always be present.
0
 
Rory ArchibaldCommented:
Actually, I only half-assumed it. For a full assumption:
="Full Name: "&A4&if(len(B4)," / "&"Phone: "&B4,"")&if(len(C4)," / "&"Mobile:  "&C4,"")&if(len(D4)," / "&"Extension: "&D4,"")&IF(LEN(E4)," / "&"Skype: "&E4,"")&IF(LEN(F4)," / "&"E-mail: "&F4,"")&IF(LEN(G4)," / "&"Dept.: "&G4,"")&IF(LEN(H4)," / "&"Company: "&H4,"")
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
gavsmithCommented:
If it's possible that the full name could be blank try using:

=SUBSTITUTE((IF(LEN(A4)," / Full Name: "&A4,"")&IF(LEN(B4)," / "&"Phone: "&B4,"")&IF(LEN(C4)," / "&"Mobile:  "&C4,"")&IF(LEN(D4)," / "&"Extension: "&D4,"")&IF(LEN(E4)," / "&"Skype: "&E4,"")&IF(LEN(F4)," / "&"E-mail: "&F4,"")&IF(LEN(G4)," / "&"Dept.: "&G4,"")&IF(LEN(H4)," / "&"Company: "&H4,""))," / ","",1)
0
 
gavsmithCommented:
Same again but removed un-needed &'s:

=SUBSTITUTE((IF(LEN(A4)," / Full Name: "&A4,"")&IF(LEN(B4)," / Phone: "&B4,"")&IF(LEN(C4)," / Mobile:  "&C4,"")&IF(LEN(D4)," / Extension: "&D4,"")&IF(LEN(E4)," / Skype: "&E4,"")&IF(LEN(F4)," / E-mail: "&F4,"")&IF(LEN(G4)," / Dept.: "&G4,"")&IF(LEN(H4)," / Company: "&H4,""))," / ","",1)
0
 
Petersburg1Author Commented:
Thanks a lot.
this solution works right away and 100% as I need.
thanks
Nils
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.