+ Reply to Thread
Results 1 to 3 of 3

Concatenate : Leave blank if cell = blank

  1. 07-19-2018, 12:43 AM #1
    cyee
    cyee is offline
    Forum Contributor
    Join Date
    09-02-2010
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    283

    Concatenate : Leave blank if cell = blank

    Hi All,

    I'm using following formula. However the end result is " 24RACKS , 30BOXES , 00 , 00 ".
    How can I modify the formula to have end result as "24RACKS, 30 BOXES" without showing 0 if column is blank? 

    Please Login or Register  to view this content.
    Untitled.jpg
    Last edited by cyee; 07-19-2018 at 12:53 AM. Reason: EDIT FORMULA
    Reply With QuoteRegister To Reply
  2. 07-19-2018, 01:18 AM #2
    cyee
    cyee is offline
    Forum Contributor
    Join Date
    09-02-2010
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    283

    Re: Concatenate : Leave blank if cell = blank

    Hi All,

    Maybe i should change my question to:-

    I am wondering how I can concatenate following with comma separated values but exclude empty cells.
    The result will be "24 RACKS, 30 BOXES, 10 CARTONS"

    untitled.jpg
    Last edited by cyee; 07-19-2018 at 01:20 AM. Reason: UPLOAD IMAGE
    Reply With QuoteRegister To Reply
  3. 07-19-2018, 05:15 PM #3
    jtakw
    jtakw is offline
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: Concatenate : Leave blank if cell = blank

    Hi,

    I think you just need the TRIM after the SUBSTITUTE:

    Excel 2016 (Windows) 64 bit
    A
    B
    C
    D
    1
    24
    		 RACKS 24 RACKS, 30 BOXES, 10 CARTONS
    2
    30
    		 BOXES
    3
    4
    10
    		 CARTONS
    Sheet: Sheet8

    Excel 2016 (Windows) 64 bit
    D
    1
    		 =TRIM(SUBSTITUTE(A1&" "&B1&", "&A2&" "&B2&", "&A3&" "&B3&", "&A4&" "&B4," , ",""))
    Sheet: Sheet8
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula question IF cell is blank, leave blank if not D6-E6
    By krisryan in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-24-2018, 10:56 PM
  2. [SOLVED] Concatenate If Blank - remove blank line if first cell is blank
    By ker9 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-27-2014, 02:14 PM
  3. IF Statement to leave cell blank if multiple cells are all blank
    By sweeteri in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-14-2014, 12:02 PM
  4. [SOLVED] help on a simple if text says this input that, if cell is blank leave it blank
    By Rec1ne in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-02-2013, 10:43 AM
  5. Create Quarter/Year Field, but if blank, either populate cell with ERROR or leave blank
    By maryren in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-20-2013, 01:43 PM
  6. [SOLVED] Simple pull exact text from cell, if blank it pulls up a 0. How to leave it as blank?
    By sharpmel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-30-2012, 02:20 PM
  7. Excel 2007 : unable to leave cells blank if reference cell is blank
    By FluidicAztec in forum Excel General
    Replies: 4
    Last Post: 07-15-2008, 01:42 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1