+ Reply to Thread
Results 1 to 5 of 5

Help: concatenate, skip blanks, add line break

  1. #1
    Registered User
    Join Date
    05-09-2012
    Location
    Thunder Bay, Ontario
    MS-Off Ver
    Excel 2016
    Posts
    24

    Question Help: concatenate, skip blanks, add line break

    Hello,

    I am looking for help on writing a formula that will concatenate cells, ignore blanks, and add line breaks on those that do not have blanks.

    The data: I have a list of people and they have different meetings they need to attend. I want to concatenate all their meetings into one cell, with line breaks, but ignore the blank cells.

    Final result wanted in one cell:
    Meeting 1
    Meeting 3



    I can't seem to upload a file I keep receiving an error. Let me know if you need more info
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,046

    Re: Help: concatenate, skip blanks, add line break

    Please try

    =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(B2," ","_")&" "&SUBSTITUTE(C2," ","_")&" "&SUBSTITUTE(D2," ","_"))," ",CHAR(10)),"_"," ")
    Attached Files Attached Files

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,771

    Re: Help: concatenate, skip blanks, add line break

    Try this in E2:

    =SUBSTITUTE(IF(B2="","",B2&CHAR(10)) & IF(C2="","",C2&CHAR(10)) & IF(D2="","",D2&CHAR(10))&"$",CHAR(10)&"$","")

    then copy down. Note that Excel is not very good at maintaining the appropriate row heights, so you may have to adjust some manually.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    05-09-2012
    Location
    Thunder Bay, Ontario
    MS-Off Ver
    Excel 2016
    Posts
    24

    Re: Help: concatenate, skip blanks, add line break

    This is perfect! Thank you so much for the speedy response!

  5. #5
    Registered User
    Join Date
    05-09-2012
    Location
    Thunder Bay, Ontario
    MS-Off Ver
    Excel 2016
    Posts
    24

    Re: Help: concatenate, skip blanks, add line break

    Can you let me know which section of this formula gets duplicated to include more cells? My actual data runs from cell B2 to FZ2. This is over 180 cells I need to combine :/

+ 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. [SOLVED] How to tell this 1 line of code to skip blanks
    By 80Wil in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 04-26-2017, 01:56 PM
  2. [SOLVED] Concatenate with char(10) but skip blanks
    By chirag.patel22285 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-03-2014, 06:59 AM
  3. Concatenate, Skip Blanks and Carriage Return only after non-blank
    By GTide in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-14-2014, 05:08 PM
  4. Skip Blanks on Line Graph or Scatter Plot (Excel 2007)
    By Ctolson2344 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 02-15-2013, 04:04 AM
  5. Concatenate a Range Skip Blanks
    By seanyeap in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-24-2009, 08:54 PM
  6. Concatenate - Skip Blanks
    By gquest in forum Excel General
    Replies: 1
    Last Post: 04-05-2007, 08:31 PM
  7. Line break using Concatenate
    By kys2000 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-27-2007, 06:03 PM

Tags for this Thread

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