Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2011-05-11
Medium Priority
Last Modified: 2012-06-27
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
Question by:Petersburg1
LVL 85

Expert Comment

by:Rory Archibald
ID: 35737670
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.
LVL 85

Expert Comment

by:Rory Archibald
ID: 35737680
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,"")
LVL 11

Accepted Solution

Runrigger earned 1000 total points
ID: 35737698
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,"")

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 10

Expert Comment

ID: 35737925
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)
LVL 10

Expert Comment

ID: 35737976
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)

Author Closing Comment

ID: 35744083
Thanks a lot.
this solution works right away and 100% as I need.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question