Formula to Concat cells in Row, Skip blanks and add HTML code (text)

[undrgnd]

Hey all,

I have been trying to create a formula to concat 7 cells in a row, skip blanks, and add some HTML tags to it. I know the formaula wont work below but I have put it back to basic and need help to get it to the next step. Details below:

=CONCAT("[ul][li]",IF(A2="","",A2),"[/li][li]",IF(B2="","",B2),"[/li][li]",IF(C2="","",C2),"[/li][li]",IF(D2="","",D2),"[/li][li]",IF(E2="","",E2),"[/li][li]",IF(F2="","",F2),"[/li][li]",IF(G2="","",G2),"[/li][/ul]")

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Feature1[/TD]
[TD]Feature2[/TD]
[TD]Feature3[/TD]
[TD]Feature4[/TD]
[TD]Feature5[/TD]
[TD]Feature6[/TD]
[TD]Feature7[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]Round[/TD]
[TD]Square[/TD]
[TD]Flat[/TD]
[TD]Edge[/TD]
[TD]Great[/TD]
[TD]Little[/TD]
[TD]Solid[/TD]
[TD][TABLE="width: 1514"]
<tbody>[TR]
[TD="width: 1514"]

• [TABLE="width: 1514"]
  <tbody>[TR]
  [TD="width: 1514"][ul][li]Round[/li][li]Square[/li][li]Flat[/li][li]Edge[/li][li]Great[/li][li]Little[/li][li]Solid[/li][/ul][/TD]
  [/TR]
  </tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Round[/TD]
[TD]Square[/TD]
[TD]Flat[/TD]
[TD]Edge[/TD]
[TD]Great[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 1514"]
<tbody>[TR]
[TD="width: 1514"]

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1514"]
<tbody>[TR]
[TD="width: 1514"][ul][li]Round[/li][li]Square[/li][li]Flat[/li][li]Edge[/li][li]Great[/li][li][/li][li][/li][/ul][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Row 1 is correct as there are 7 features. Row 2 is wrong and it should finish after feature 5 and should look like this:
[TABLE="width: 1514"]
<tbody>[TR]
[TD="width: 1514"]

• [TABLE="width: 1514"]
  <tbody>[TR]
  [TD="width: 1514"][ul][li]Round[/li][li]Square[/li][li]Flat[/li][li]Edge[/li][li]Great[/li][/ul][/TD]
  [/TR]
  </tbody>[/TABLE]

So at the start of every row is [ul][li] then between each feature is [/i][li] and then at the lend of the last feature it is [/li][/ul][/TD]
[/TR]
</tbody>[/TABLE]

Also, is there any recommendation on sites etc to learn the next level of excel including VBA's etc.

Many thanks in advance.

Chris

EDIT: i had to replace <> with [] due to formatting issues.

 

[V_Malkoti]

Try:

=CONCATENATE("[ul]", IF(A2="","","[li]"&A2&"[/li]"), IF(B2="","","[li]"&B2&"[/li]"), IF(C2="","","[li]"&C2&"[/li]"), IF(D2="","","[li]"&D2&"[/li]"), IF(E2="","","[li]"&E2&"[/li]"), IF(F2="", "","[li]"&F2&"[/li]"), IF(G2="","","[li]"&G2&"[/li]"),"[/ul]")

 

[undrgnd]

Man that works a treat.. thank you so much.

I was heading down this path =CONCAT("[ul][li]",SUBSTITUTE(TRIM(TEXTJOIN(" ",TRUE,A4:G4))," ","[/i][li]"),"[li][ul]") but realised that if i had two words as a feature ie super solid, if would ad the [/i][li] after each space.

 

[Peter_SSs]

Don't you just need this?

Book1
	A	B	C	D	E	F	G	H
1	Feature1	Feature2	Feature3	Feature4	Feature5	Feature6	Feature7	Result
2	Round	Square	Flat	Edge	Great	Little	Solid	[ul][li]Round[/li][li]Square[/li][li]Flat[/li][li]Edge[/li][li]Great[/li][li]Little[/li][li]Solid[/li][/ul]
3	Round	Square	Flat	Edge	Great			[ul][li]Round[/li][li]Square[/li][li]Flat[/li][li]Edge[/li][li]Great[/li][/ul]
4	This	has multi word cells	in	it				[ul][li]This [/li][li]has multi word cells[/li][li]in[/li][li]it[/li][/ul]
Concat undrgnd

Cell Formulas
Range	Formula
H2	="[ul][li]"&TEXTJOIN("[/li][li]",1,A2:G2)&"[/li][/ul]"

 

[undrgnd]

Thank you Peter, this works very well too and less complicated. I only learned Textjoin today and think its going to be part of the repertoire.

 

[Peter_SSs]

Thank you Peter, this works very well too and less complicated.

You are welcome.

 

[Mike Szczesny]

Although, it is not exactly what you need, but can you use this. In order for this to work, you would need to have your source data vertically. In my example i selected the items in row 7 plus two empty rows. So my formula concatenates and transposes your data. I also included a * after each word (included the two empty cells. The formula is =concatenate(transpose(range&"*")). You must first highlight the transpose(.....) part of your formula with your mouse. Hit F9. Remove the { and }. Hit enter.


[TABLE="width: 443"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Round[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Square[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Flat[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Great[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Little[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solid[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Round*Square*Flat*Edge*Great*Little*Solid***[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Although, it is not exactly what you need, but can you use this.

Mike, I'm wondering why the OP would consider using this when, as you have stated, it isn't exactly what was needed and it's quite a complicated process compared to the straight-forward formula that already does exactly what is needed?

I'm also wondering why for it to work you have to put the data vertically? If you leave it horizontal and leave the transpose out of your formula, exactly the same result can be achieved.