Remove CHAR(10) blank from CONCATENATE formula

Hi Guys,

I am trying to remove the blanks from a CONCATENATE formula that uses CHAR(10). The CONCATENATE formula references a range of cells and puts a CHAR(10) to make a new row after each referenced cell. However some of these cells may be blank if the cell THEY reference is 0. But the CONCATENATE formula still puts in the CHAR(10) line return resulting in a cell with lots of gaps and therefore messy!!

I'd rather not get into VBA as that's a whole can of worms I'm not sure I want to get into! A formula solution would be great!

See file attached.

Thanks very much.

Remove gaps from CONCATENATE.xlsx

Hi

Try replacing the Char(10) text with if(F2<>"",Char(10),"") Obviously change the F2 to the relevant cell in column F.

Chris

Thanks Ace_XL

That works great however in another spread sheet I am trying to use this on, the values in the qty column A are actually derived from =SUMIF formulas. Your suggested formulas seam not to work with this.

Any ideas?

Thanks.

Originally Posted by Ace_XL

Change formula in F2 to
=IF(A2="","",A2&" No. "&B2&CHAR(10))

Copy down

In F24
=LEFT(CONCATENATE(F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,F21,F22),LEN(CONCATENATE(F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,F21,F22))-1)

Hi Chris 53,

Thanks for that. Sorry to sound daft but where would I put the if(F2<>"",Char(10),"") ?

Thanks.

In F24 your formula starts with =CONCATENATE(F2,CHAR(10),F3,CHAR(10),F4,CHAR(10),F5,CHAR(10),F6,CHAR(10),

Change all the ,CHAR(10), to if(F2<>"",Char(10),"")

Chris

Thanks guys. Got it working as I would like (using martindwilson's solution).

Final query to make it perfect!!: if I have 2 digit values after a single digit values in F24, it looks like this:

5 No. CH - 10
4 No. SO - 10
19 No. ST-13
4 No. ST-12 - TRAY
6 No. Scatter Cushion


Any way to get all the 'No.' to line up.

No matter if not.

Thanks again.

Hi,
I have similar issue with Olly-7, but your formula does not work for me, it does not move the next text to the next line and when it is emply I still see a blank row. and I am concatenating two different sheet. Can you help

Originally Posted by martindwilson

f2 down =IF(A2=0," ",A2&"^No.^"&SUBSTITUTE(B2," ","^")&" ")
then i24
=SUBSTITUTE(SUBSTITUTE(TRIM(F2&F3&F4&F5&F6&F7&F8&F9&F10&F11&F12&F13&F14&F15&F16&F17&F18&F19&F20&F21&F22)," ",CHAR(10)),"^"," ")

Hi, the formula did not work for me. I am trying to concatenate two rows in different sheets into 2 separate lines, and if cells is blank there is still an emty row showing. Can you help?