Replacing bullets with html tags for csv load

[FuriousD]

I recently needed to add bullet points to a file and someone on here was able to help. Turns out we actually need to add html tags to the file for upload and I'm hoping there is a way for us to use a formula to replace bullets with the <li> </li> tags to have bullets load.

Any ideas? I need to maintain the intro paragraph and lines, which is leading me to more of a find/replace solution without bullets. Thanks in advance

Current:	What I need excel to display:
Your text goes here. Sample text showing paragraph intro followed by bullet items. Line 1 Line 2 Line 3	Your text goes here. Sample text showing paragraph intro followed by bullet items. <li> Line 1 </li> <li> Line 2 </li> <li> Line 3 </li>

 

[Sufiyan97]

Try

Book3
	A	B	C
1
2	• Line 1 • Line 2 • Line 3	</li> Line 1 </li> </li> Line 2 </li> </li> Line 3 </li>
3
Sheet2

Cell Formulas
Range	Formula
B2	B2	=TEXTJOIN(CHAR(10),TRUE,SUBSTITUTE(TEXTSPLIT(A2,CHAR(10)),"•","</li>")&" </li>")

 

[FuriousD]

Thank you, I think this is really close. I did remove the / from the first tag (<li> instead of </li>)

Here's the result I see. My issue is that I assume the closing html tag being added to the non-bullet item will cause an error (RED text below). I dont know for sure if it will, but I'm hoping not to run into any errors as it takes a while to get these files processed by our technical team. Thoughts on how to achieve the result without the end tag in the first line?

Output Example:

Your text goes here. Sample text showing paragraph intro followed by bullet items. </li>

<li> Text 1 </li>
<li> Text 2 </li>

Try

Book3
	A	B	C
1
2	• Line 1 • Line 2 • Line 3	</li> Line 1 </li> </li> Line 2 </li> </li> Line 3 </li>
3
Sheet2

Cell Formulas
Range	Formula
B2	B2	=TEXTJOIN(CHAR(10),TRUE,SUBSTITUTE(TEXTSPLIT(A2,CHAR(10)),"•","</li>")&" </li>")

 

[FuriousD]

The other option that might be easier would be if I can split the bullet list items into a separate column? Like text to columns with a bullet point delimiter, but to take all bullet items into one separate cell??

From here I could easily add the tags and concatenate?

Current (A1)	Desired State Intro (A2)	Desired State Bullets (A3)
Your text goes here. Sample text showing paragraph intro followed by bullet items. Line 1 Line 2 Line 3	Your text goes here. Sample text showing paragraph intro followed by bullet items.	Line 1 Line 2 Line 3

 

[Sufiyan97]

Just remove "/" from SUBSTITUTE

=TEXTJOIN(CHAR(10),TRUE,SUBSTITUTE(TEXTSPLIT(A2,CHAR(10)),"•","</li>")&" </li>")


Try

Book3
	A	B	C
1
2	• Line 1 • Line 2 • Line 3	<li> Line 1 </li> <li> Line 2 </li> <li> Line 3 </li>
3
4
Sheet2

Cell Formulas
Range	Formula
B2	B2	=TEXTJOIN(CHAR(10),TRUE,SUBSTITUTE(TEXTSPLIT(A2,CHAR(10)),"•","<li>")&" </li>")

 

[FuriousD]

Thanks again for the quick reply. I did remove the / from the forumula. Here is what I have (my data is in O2).

=TEXTJOIN(CHAR(10),TRUE,SUBSTITUTE(TEXTSPLIT(O2,CHAR(10)),"•","<li>")&" </li>")

However the results are showing as below. All of the RED tags do not have bullets associated to them but are adding a closing html tag. Is there a way to only have this for the bullet list? If not, is there an easy way to split the bullet list into new column? I've tried using a bullet character for delimiter, but its not working.

Maintains application systems that are beyond the development stage and running in the daily operations of the firm. </li> May have overall ownership of a product or app, gathering requirements, managing and prioritizing changes </li> Specializes in configuring and supporting applications (e.g. SaaS). A Supervisor of Support level employees. May include supervising offshore resources and/or contract resources. </li> </li> Responsibilities typically include: </li> <li> Setting day-to-day operational objectives and KPIs for team. </li> <li> Defining technical requirements </li> <li> Managing quality issues </li> <li> Handling routine problems, escalating when necessary </li> <li> Ensuring IT Compliance policies and procedures are understood and followed. </li>

Just remove "/" from SUBSTITUTE

=TEXTJOIN(CHAR(10),TRUE,SUBSTITUTE(TEXTSPLIT(A2,CHAR(10)),"•","</li>")&" </li>")


Try

Book3
	A	B	C
1
2	• Line 1 • Line 2 • Line 3	<li> Line 1 </li> <li> Line 2 </li> <li> Line 3 </li>
3
4
Sheet2

Cell Formulas
Range	Formula
B2	B2	=TEXTJOIN(CHAR(10),TRUE,SUBSTITUTE(TEXTSPLIT(A2,CHAR(10)),"•","<li>")&" </li>")

 

[Sufiyan97]

Try

Book3
	A	B	C
1
2	• Line 1 • Line 2 • Line 3 Line 4 • Line 5	<li> Line 1 </li> <li> Line 2 </li> <li> Line 3 </li> Line 4 <li> Line 5 </li>
3
4
Sheet2 (2)

Cell Formulas
Range	Formula
B2	B2	=TEXTJOIN(CHAR(10),TRUE,IF(ISNUMBER(SEARCH("•",TEXTSPLIT(A2,CHAR(10)))),SUBSTITUTE(TEXTSPLIT(A2,CHAR(10)),"•","<li>")&" </li>",TEXTSPLIT(A2,CHAR(10))))

 

[FuriousD]

Thanks for the help, but its not working. The first solution was closer, but I think it might actually be easier to split the cells and then insert the changes using your formula above. I'll start a separate thread.

 

[Sufiyan97]

In what way it's not working?
What error you are getting?

 

[FuriousD]

In what way it's not working?
What error you are getting?

Actually - I was wrong...it does appear to be working! My formatting in the cell needed to be updated (it appeared to be cutting out text).

I'll update my file and pass to our technical team, but I believe this is the solution!!!! Thank you!