CODEWITHSUNDEEP
excelvba
Ankita
Ankita

Reputation: 1456

Converting bullets Text to HTML using VBA function Excel

I have lots of products which have description including bullets, which I want to convert it to html form excel file.

Description of product be like in one cell :

•   Our aim is to be devoted to building a sustainable future. 
•   We are keen to preserve the human being and its environment.
•   It is best for all skin types

some macro code I have tried is like:

Sub aTest()
    Dim rCell As Range, spl As Variant, i As Long
    
    For Each rCell In Selection
        spl = Split(rCell, Chr(10))
        For i = LBound(spl) To UBound(spl)
            spl(i) = Chr(60) & "li" & Chr(62) & spl(i) & Chr(60) & "/li" & Chr(62)
        Next i
        rCell = Join(spl, Chr(10))
    Next rCell
    Selection.ColumnWidth = 200
    Selection.EntireRow.AutoFit
    Selection.EntireColumn.AutoFit
End Sub

this code gives me output including bullets within it:

enter image description here

Expected:

<li> Our aim is to be devoted to building a sustainable future. </li>
<li> We are keen to preserve the human being and its environment.</li>
<li> It is best for all skin types</li>

Could anyone please help me get this correct, as I do not have any idea of VB. Moreover, it would be great to have Function rather than macro.

Thanks

Update: I have this whole content within one cell and want it to convert to html together. Is that possible?

BIODERMA is a brand of NAOS, the world’s number 1 cosmetic company. BIODERMA has been dedicated to skin health for 40 years and has pioneered many breakthroughs in dermatological care. We are a company that provides high-quality skin care products developed by medical professionals with the absolute highest safety standards. 
Key Features:
•   Our aim is to be devoted to building a sustainable future. 
•   We are keen to preserve the human being and its environment.
•   It is best for all skin types

Upvotes: 0

Views: 287

Answers (2)

Raymond Wu
Raymond Wu

Reputation: 3387

Private Function ConvertBulletToHTML(argInput As String) As String
    Dim cleanArr() As String
    cleanArr = Split(argInput, vbLf)
        
    Dim i As Long
    For i = 0 To UBound(cleanArr)
        If Instr(cleanArr(i), Chr(149)) <> 0 Then
              cleanArr(i) = Replace(cleanArr(i), Chr(149), vbNullString)
              cleanArr(i) = "<li>" & Trim(cleanArr(i)) & "</li>"
        End If
    Next i
        
    ConvertBulletToHTML = "<div>" & Join(cleanArr, vbLf) & "</div>"
End Function

This is the output:

<div><li>Our aim is to be devoted to building a sustainable future.</li> 
<li>We are keen to preserve the human being and its environment.</li> 
<li>It is best for all skin types</li></div>

And output for the updated question:

<div>BIODERMA is a brand of NAOS, the world’s number 1 cosmetic company. BIODERMA has been dedicated to skin health for 40 years and has pioneered many breakthroughs in dermatological care. We are a company that provides high-quality skin care products developed by medical professionals with the absolute highest safety standards.
Key Features:
<li>Our aim is to be devoted to building a sustainable future.</li>
<li>We are keen to preserve the human being and its environment.</li>
<li>It is best for all skin types</li></div>

Remove the Trim in the for loop if you do not want it trimmed.

Note: vbLf is the same as Chr(10) so you can just use the built-in constant.

Upvotes: 1

avb
avb

Reputation: 1753

Try:

spli(i) = Replace(spli(i), Chr(149), "")

You must check if it is actually Chr(149) and change it to something else if it is not.
Other way - assuming it is the first character in your spli(i)

spli(i) = Right(spli(i), Len(spli(i)) - 1)

This must be applied before adding html tags.

Upvotes: 1

Related Questions