I am using the following formula to collate text from 3 sheets into a 4th.

=$'sheet 1'.A1&CHAR(10)&$'sheet 2'.A1&CHAR(10)&$'sheet 3'.A1&CHAR(10)

this collates the text with a return after each sheets contribution. However the return remains even it the is no text in the corresponding box e.g. sheet 1 has 'hi' sheet 2 empty, sheet 3 there looks like:



leaving a return between the 'hi' and the 'there' where any text from sheet 2 would be. I want the return only to be used if there is text from the corresponding cell so there is no return space between.

Hope that makes sense.


Try something like:

=IF(Sheet1!A1="","",Sheet1!A1 & CHAR(10)) & IF(Sheet2!A1="","",Sheet2!A1 & CHAR(10)) & IF(Sheet3!A1="","",Sheet3!A1 )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.