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

Posted on 2011-05-11
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
    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
    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

    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,"")

    LVL 10

    Expert Comment

    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

    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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now