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
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
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,"")
="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,"")
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
=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)
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)
=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)
ASKER
Thanks a lot.
this solution works right away and 100% as I need.
thanks
Nils
this solution works right away and 100% as I need.
thanks
Nils
=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.