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

**ker9** · 05-27-2014, 01:31 PM

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.

**Jonmo1** · 05-27-2014, 01:36 PM

Try

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

**ker9** · 05-27-2014, 01:38 PM

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

**Jonmo1** · 05-27-2014, 01:41 PM

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

**Jonmo1** · 05-27-2014, 01:43 PM

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.

**ker9** · 05-27-2014, 02:01 PM

Thank you for trying.

**Jonmo1** · 05-27-2014, 02:04 PM

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

**ker9** · 05-27-2014, 02:08 PM

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

**Jonmo1** · 05-27-2014, 02:14 PM

Glad you got it sorted.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Remove CHAR(10) blank from CONCATENATE formula

[SOLVED] Remove blank rows that aren't really blank (contain unused formulas)

reference cell should be blank not 00-jan-00 how make it read like the blank cell (Blank )

How to remove blank rows, then insert blank row conditionally, PLUS error proof

COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C.

Bookmarks

Bookmarks

Posting Permissions