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

1. ## 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.

2. ## 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. ## 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").

4. ## 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. ## Re: Concatenate If Blank - remove blank line if first cell is blank

Originally Posted by ker9
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. ## Re: Concatenate If Blank - remove blank line if first cell is blank

Thank you for trying.

7. ## 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. ## 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.

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

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

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

#### 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