+ Reply to Thread
Results 1 to 8 of 8

Formula/Concatenate to ignore a cell if value is blank

  1. #1
    Registered User
    Join Date
    08-02-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    94

    Question Formula/Concatenate to ignore a cell if value is blank

    Hi Guys,

    I have a table with First Name, Middle Name, and Last Name. I have another cell that concatenates these results and includes "," so that the results appear as such: John, Smith, M. This works fine until someone does not have a middle name and the following result is displayed: John, Smith, - In this instance I need the last "," after "Smith" to not be there. Any ideas of how to do this?

    I attach an example that shows how I have it set up. By all means, feel free to tell me any better ways to do this.

    Thanks,
    Attached Files Attached Files

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: Formula/Concatenate to ignore a cell if value is blank

    How about something like this in cell C10:

    =IF(ISBLANK(C6),CONCATENATE(C5,", ",C7),CONCATENATE(C5,", ",C7,", ",D6))

    - Moo

  3. #3
    Registered User
    Join Date
    08-02-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    94

    Re: Formula/Concatenate to ignore a cell if value is blank

    Moo indeed! Solved!! lol Thx

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,125

    Re: Formula/Concatenate to ignore a cell if value is blank

    This looks like it'll work too...
    =C5&" ,"&C7&IF(D6<>"",", "&D6,"")

    EDIT: wasn't marked as solved when I hit post.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: Formula/Concatenate to ignore a cell if value is blank

    Glad to help, karimel_romeo!

    Sambo kid, it looks like yours should work as well - always good to keep things neat and tidy. =)

    - Moo

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Formula/Concatenate to ignore a cell if value is blank

    Quote Originally Posted by Sambo kid View Post
    EDIT: wasn't marked as solved when I hit post.
    There's no rule to my knowledge prohibiting further responses to threads marked as solved.
    There's always more than one way to skin a cat. The more options available, the better.

    Here's another way
    =SUBSTITUTE(TRIM(C5&" "&C7&" "&D6)," ",", ")

  7. #7
    Registered User
    Join Date
    08-02-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    94

    Re: Formula/Concatenate to ignore a cell if value is blank

    All good...thanks to you all....always good to know more than 1 way to get something done! thanks guys :-)

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula/Concatenate to ignore a cell if value is blank

    why dont you incorporate everything in one formula and do away with the hidden column
    =SUBSTITUTE(TRIM(C5&" "&C7&" "&LEFT(C6,1))," ",", ")
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Concatenate cells with 5 characters each column but ignore if blank
    By ghiegomez in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 06-25-2020, 12:09 AM
  2. Ignore Cell if it is blank (but still has formula)
    By thelegazy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-25-2013, 12:30 PM
  3. [SOLVED] An INDEX MATCH formula has blank cell - need to ignore or delete
    By rls231 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-26-2013, 11:13 PM
  4. Formula (SUM & IF) to ignore a blank cell?
    By DORourke in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-24-2013, 06:48 PM
  5. How? Ignore cell if blank in an IF formula.
    By n2lectual in forum Excel General
    Replies: 7
    Last Post: 03-26-2008, 03:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1