Append texts into one string from 4 cells that each cell has a formula

[Rocky2013]

Hi,
I have problem to append texts into one string from 4 cells that each cell has a formula.

I can join all the texts from 4 cells in cell A1 using A1&A2&A3&A4, but cell B1, C1 and
D1 blocks the length of the text. I do not want to widen cell A1 to show the full length of text,
and delete the formulas in B1,C1 and D1. Please see more in the attached file.

Thanks

[FDibbins]

If you have a formula genetating the text you want to combine, have you tried to add/remove spaces in THOSE formulas 1st? If none if the answers begin/end with a space, then it's easy to add then where needed

[martindwilson]

to achieve the spacing add them then trim
=TRIM(A1&" "&B1&" "&C1&" "&D1)
so your formula would be
=TRIM(IF(F2="Yes","Let's go","")&" "&B14&" "&C14&" "&D14)


the others should also include an result for if f2<>yes probably blank ie ""
=IF(F2="Yes","to the","") =IF(F2="Yes","park has","") =IF(F2="Yes","some fun.","")

not sure about 3.

cell B13, C13,D13 blocks the text. I do not want to delet the formulas in B4,C4,D4 in order to show the text.

text will not show across cells if something is in the adjacent cell(s)

[Rocky2013]

Thanks,
I try to show text across the cells. In my spread sheets, I have four columns (few hundred rows theinformations are different in each row.
Most of the rows, the contents are in seperate columns with numbers and texts except some of them should be one line of text in A1.

Maybe something like this,
If "yes", =TRIM(IF(F2="Yes","Let's go","")&" "&B14&" "&C14&" "&D14); then clear contents in cell B1, C1 and D1. This will allow text to cross cells.
If "No", copy all the formulas back to cell A1, B1, C1 and D1.

Thanks

[martindwilson]

its not going to happen anything in a cell will stop the text extending over it that includes a formula
the onlyway would be to write vba code to do the whole thing

[FDibbins]

OK I guess my comment was more regarding the extra/lack of spaces in the text. maybe if you included a carriage return in the concatenate formula, and set the cell to wrap?

[Rocky2013]

I am OK with VBA. Should I repost this topic in VBA section or do it here?

Thanks,