+ Reply to Thread
Results 1 to 9 of 9

Concatenate If Blank - remove blank line if first cell is blank

  1. #1
    Forum Contributor
    Join Date
    06-16-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    278

    Concatenate If Blank - remove blank line if first cell is blank

    Hi,
    This is working very well, however, if my first cell (K22) is blank, my first line is blank. Is there anyway to eliminate the first line being blank?
    (There is a return between each item.)

    =IF(LEN($K$22)=0,"", & " " &$K$22) & IF(LEN($K$23)=0,"", "
    " & " "&$K$23) & IF(LEN($K$24)=0,"", " Alt-Enter line return here
    " & " "&$K$24)& IF(LEN($K$25)=0,"", " Alt-Enter line return here
    " & " "&$K$25)& IF(LEN($K$26)=0,"", " Alt-Enter line return here
    " & " "&$K$26)& IF(LEN($M$22)=0,"", " Alt-Enter line return here
    " & " "&$M$22)& IF(LEN($M$23)=0,"", " Alt-Enter line return here
    " & " "&$M$23)& IF(LEN($M$24)=0,"", " Alt-Enter line return here
    " & " "& $M$24)

    EDIT: wrap text is on in the cell so that each is on its own line. Turning wrap text off eliminates the first blank line, but then it also eliminates each one being on its own line, which I need.

    Thank you.
    Last edited by ker9; 05-27-2014 at 02:18 PM.

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

    Re: Concatenate If Blank - remove blank line if first cell is blank

    Try

    =SUBSTITUTE(TRIM($K$22&" "&$K$23&" "&$K$24&" "&$K$25&" "&$K$26&" "&$M$22&" "&$M$23&" "&$M$24)," "," ")

  3. #3
    Forum Contributor
    Join Date
    06-16-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    278

    Re: Concatenate If Blank - remove blank line if first cell is blank

    Thank you but that does not put each item on its own line in the cell (with or without wrap text). It also puts a bullet point between each word. The cells have multiple words in them ("the cat in the hat"; "the quick brown fox").
    Last edited by ker9; 05-27-2014 at 01:40 PM.

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

    Re: Concatenate If Blank - remove blank line if first cell is blank

    I just copied the " • " from the formula you posted.
    Something must have gotten lost in translation when you pasted your formula.

    Replace the " • " with CHAR(10)

    =SUBSTITUTE(TRIM(K22&" "&K23&" "&K24&" "&K25&" "&K26&" "&M22&" "&M23&" "&M24)," ",CHAR(10))

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

    Re: Concatenate If Blank - remove blank line if first cell is blank

    Quote Originally Posted by ker9 View Post
    The cells have multiple words in them ("the cat in the hat"; "the quick brown fox").
    Ah, bummer. That makes a big difference.
    The formula I suggested won't work then.

  6. #6
    Forum Contributor
    Join Date
    06-16-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    278

    Re: Concatenate If Blank - remove blank line if first cell is blank

    Thank you for trying.

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

    Re: Concatenate If Blank - remove blank line if first cell is blank

    The formula you posted doesn't work at all for me, it says the formula you entered contains an error...

    Can you post a sample book?

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  8. #8
    Forum Contributor
    Join Date
    06-16-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    278

    Re: Concatenate If Blank - remove blank line if first cell is blank

    I found that if I added this

    "Reason for Change:" &

    to the very beginning after the = sign, then the first line has that wording and is not blank. Thankfully, this will work for me.
    Thank you for your help!

    FYI: There is an Alt-Enter line return after each ending " on each line

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

    Re: Concatenate If Blank - remove blank line if first cell is blank

    Glad you got it sorted.

+ 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] Remove blank rows that aren't really blank (contain unused formulas)
    By painterartist in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-02-2014, 02:01 PM
  2. Replies: 3
    Last Post: 01-23-2014, 12:19 PM
  3. Remove CHAR(10) blank from CONCATENATE formula
    By OLLY-7 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-21-2013, 08:47 AM
  4. How to remove blank rows, then insert blank row conditionally, PLUS error proof
    By GTS115 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-05-2009, 11:39 AM
  5. Replies: 1
    Last Post: 01-15-2005, 06:06 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