Combining multiple text cells into dedicated single cell, and inserting line break to separate text bulk in the dedicated single cell?

[Ritvars]

Hello,

I need to combine multiple product description texts which are in their own cells into a one single dedicated overall description cell which will encompass all the bulk texts from those separate cells, however not make all the text flow in single string without line breaks.

Required Fomula Result Example:
E1=
A1 "Short Description"
[line break]<line break="">
B1 "Full Description"
<line break="">[line break]
C1 "Nutrition"
<line break="">[line break]
D1 "Directions"

Let me know, how to do it please.</line></line></line>

 

[u0107]

Char(10) will give you a line break So you can enter "=CONCATENATE(A1,CHAR(10),B1,CHAR(10),C1,CHAR(10),D1)" in E1.

 

[Ritvars]

Thank you for guideline, here is the final tested formula that really works, and has properly line-breaked those 4 different descriptions:

=CONCATENATE(A1&CHAR(10)&CHAR(10)&B1&CHAR(10)&CHAR(10)&C1&CHAR(10)&CHAR(10)&D1)

Had to apply double &CHAR(10)& formula in order for it to break an empty line between description texts.

 

[Ritvars]

Ok, and now, lets take it to the next level!

As far as the formula was introduced in the previous thread, this is what it looks like in my case with added custom description titles in "TITLE", where column titles are used in Format as Table mode.

=CONCATENATE("SHORT DESCRIPTION"&CHAR(10)&CHAR(10)&[@[SHORT
DESCRIPTION]]&CHAR(10)&CHAR(10)&"DESCRIPTION"&CHAR(10)&CHAR(10)&[@DESCRIPTION])&CHAR(10)&CHAR(10)&"NUTRITION"&CHAR(10)&CHAR(10)&[@NUTRITION]&CHAR(10)&CHAR(10)&"DIRECTIONS"&CHAR(10)&CHAR(10)&[@DIRECTIONS]

How to write in this case a more advanced formula using =IF function or any other relavant on top of the mentioned previously, precisely, to write a full descriptive text with custom headings like indicated in the fomula above, where, if any of the descriptive information is missing in single or multiple columns as SHORT DESCRIPTION, DESCRIPTION, NUTRITION, DIRECTIONS, the formula would return only those custom heading with description which contain any information in the source cells?

Example of the idea

All info available in source cells in all categories:

SHORT DESCRIPTION
Abc
[line break]
DESCRIPTION
Abc
[line break]
NUTRITION
Abc
[line break]
DIRECTIONS,
Abc

Partial info available only in 2 source cells:

SHORT DESCRIPTION
Abc
[line break]
DESCRIPTION
Abc

Let me know what you can think of!

 

[István Hirsch]

Ok, and now, lets take it to the next level!

As far as the formula was introduced in the previous thread, this is what it looks like in my case with added custom description titles in "TITLE", where column titles are used in Format as Table mode.

=CONCATENATE("SHORT DESCRIPTION"&CHAR(10)&CHAR(10)&[@[SHORT
DESCRIPTION]]&CHAR(10)&CHAR(10)&"DESCRIPTION"&CHAR(10)&CHAR(10)&[@DESCRIPTION])&CHAR(10)&CHAR(10)&"NUTRITION"&CHAR(10)&CHAR(10)&[@NUTRITION]&CHAR(10)&CHAR(10)&"DIRECTIONS"&CHAR(10)&CHAR(10)&[@DIRECTIONS]

How to write in this case a more advanced formula using =IF function or any other relavant on top of the mentioned previously, precisely, to write a full descriptive text with custom headings like indicated in the fomula above, where, if any of the descriptive information is missing in single or multiple columns as SHORT DESCRIPTION, DESCRIPTION, NUTRITION, DIRECTIONS, the formula would return only those custom heading with description which contain any information in the source cells?

Example of the idea

All info available in source cells in all categories:

SHORT DESCRIPTION
Abc
[line break]
DESCRIPTION
Abc
[line break]
NUTRITION
Abc
[line break]
DIRECTIONS,
Abc

Partial info available only in 2 source cells:

SHORT DESCRIPTION
Abc
[line break]
DESCRIPTION
Abc

Let me know what you can think of!

Give this formula a try. In this example the texts to concatenate are in C1 - F1:

=IF(C1<>"","SHORT DESCRIPTION"&CHAR(10)&CHAR(10)&C1&CHAR(10)&CHAR(10),"")&IF(D1<>"","DESCRIPTION"&CHAR(10)&CHAR(10)&D1&CHAR(10)&CHAR(10),"")&IF(E1<>"","NUTRITION"&CHAR(10)&CHAR(10)&E1&CHAR(10)&CHAR(10),"")&IF(F1<>"","DIRECTIONS"&CHAR(10)&CHAR(10)&F1&CHAR(10)&CHAR(10),"")

 

[Ritvars]

Brilliant!

Thank you István Hirsch for the provided formula, here is the visible side of it in my case:

=
IF([@[SHORT DESCRIPTION]]<>"","SHORT DESCRIPTION"&CHAR(10)&CHAR(10)&[@[SHORT DESCRIPTION]]&CHAR(10)&CHAR(10),"")&
IF([@DESCRIPTION]<>"","DESCRIPTION"&CHAR(10)&CHAR(10)&[@DESCRIPTION]&CHAR(10)&CHAR(10),"")&
IF([@NUTRITION]<>"","NUTRITION"&CHAR(10)&CHAR(10)&[@NUTRITION]&CHAR(10)&CHAR(10),"")&
IF([@DIRECTIONS]<>"","DIRECTIONS"&CHAR(10)&CHAR(10)&[@DIRECTIONS]&CHAR(10)&CHAR(10),"")

So if any of the 4 description cells are empty, then the final 5th cell will only display the information that is available along with the relevant custom header titles.

Note if you have used Alt + Enter to break column title e.g.
SHORT
DESCRIPTION

Then it will appear in the formula with a broken line too, example:

=
IF([@[SHORT
DESCRIPTION]]<>"","SHORT DESCRIPTION"&CHAR(10)&CHAR(10)&[@[SHORT
DESCRIPTION]]&CHAR(10)&CHAR(10),"")&
IF([@DESCRIPTION]<>"","DESCRIPTION"&CHAR(10)&CHAR(10)&[@DESCRIPTION]&CHAR(10)&CHAR(10),"")&
IF([@NUTRITION]<>"","NUTRITION"&CHAR(10)&CHAR(10)&[@NUTRITION]&CHAR(10)&CHAR(10),"")&
IF([@DIRECTIONS]<>"","DIRECTIONS"&CHAR(10)&CHAR(10)&[@DIRECTIONS]&CHAR(10)&CHAR(10),"")

Now, taking it to the formatting stage... How to create custom header titles bold in the formula provided?

 

[István Hirsch]

Now, taking it to the formatting stage... How to create custom header titles bold in the formula provided?

Thanks. I am afraid it is not possible to format or keep special formatting of substrings in texts produced with a formula (only with VBA).