Bulk text converting into HTML Format

[ankrups]

I have my data file where some product description is not in html codes. what I need VBA code where if I select the particular column then it find the excel cells of those selected column and text within and convert them into html code.

For example, I am getting file as per below screenshot.

Description

Premium Generic Toner

Premium Generic Toner

Premium Generic Toner

Premium Generic Drum Unit * Please note this cartridge is not a toner cartridge

<p>Premium Quality New Compatible Drum Unit</p>

Premium Quality New Compatible Drum Unit

Premium Quality New Compatible Drum Unit

Premium Quality New Compatible Laser Toner Cartridge

Premium grade generic laser toner printer cartridge

Premium Remanufactured Cartridges

All I need is to convert the non html excel cells into html code.

let me know if you need any further information on this.

thank you in advance.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is this any use?

Sub HTML_tags()
  With Range("D2", Range("D" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(left(#,1)=""<"",#,""<p>""&#&""</p>"")", "#", .Address))
  End With
End Sub

Before:

ankrups.xlsm
	D
1	Description
2	Premium Generic Toner
3	Premium Generic Toner
4	Premium Generic Toner
5	Premium Generic Drum Unit * Please note this cartridge is not a toner cartridge
6	<p>Premium Quality New Compatible Drum Unit</p>
7	Premium Quality New Compatible Drum Unit
8	Premium Quality New Compatible Drum Unit
9	Premium Quality New Compatible Laser Toner Cartridge
10	Premium grade generic laser toner printer cartridge
11	Premium Remanufactured Cartridges
Sheet1

After:

ankrups.xlsm
	D
1	Description
2	<p>Premium Generic Toner</p>
3	<p>Premium Generic Toner</p>
4	<p>Premium Generic Toner</p>
5	<p>Premium Generic Drum Unit * Please note this cartridge is not a toner cartridge</p>
6	<p>Premium Quality New Compatible Drum Unit</p>
7	<p>Premium Quality New Compatible Drum Unit</p>
8	<p>Premium Quality New Compatible Drum Unit</p>
9	<p>Premium Quality New Compatible Laser Toner Cartridge</p>
10	<p>Premium grade generic laser toner printer cartridge</p>
11	<p>Premium Remanufactured Cartridges</p>
Sheet1

 

[ankrups]

Thanks Peter. It almost worked. only thing is my description columns is not fixed as Column D in this code.

It varies as some times they are in column A some times they are in other columns. To make this work I need code where I need to select the whole column which I need to convert into the HTML tags.

Also one more thing that I have noticed is, if any cells has existing html code then this solution giving the #value error.

For example Text before running this code.

Premium Generic Quality New Laser Toner Cartridge

Premium Generic Toner Cartridge

<p>Premium quality remanufactured laser toner cartridge.</p> <ul class="a-unordered-list a-vertical a-spacing-mini"> <li><span class="a-list-item">HP 307A (CE742A) toner cartridges work with: HP Color LaserJet CP5225.</span></li> <li><span class="a-list-item">Original HP toner cartridges produce an average of 71% more usable pages than non-HP cartridges.</span></li> <li><span class="a-list-item">Cartridge yield (approx.): 7,300 pages</span></li> <li><span class="a-list-item">307A toner is designed to work with your HP printer for high quality, reliable results every print.</span></li> <li><span class="a-list-item">What's in the Box: 1 New Original HP 307A (CE742A) toner cartridge</span></li> <li><span class="a-list-item">Color: Yellow</span></li> </ul> <p>This product is not an official manufacturer product, and neither the seller nor the product has any association or endorsement of the original compatible device manufacturer.</p>

<p>Premium quality remanufactured laser toner cartridge.</p> <ul class="a-unordered-list a-vertical a-spacing-mini"> <li><span class="a-list-item">HP 307A (CE742A) toner cartridges work with: HP Color LaserJet CP5225.</span></li> <li><span class="a-list-item">Original HP toner cartridges produce an average of 71% more usable pages than non-HP cartridges.</span></li> <li><span class="a-list-item">Cartridge yield (approx.): 7,300 pages</span></li> <li><span class="a-list-item">307A toner is designed to work with your HP printer for high quality, reliable results every print.</span></li> <li><span class="a-list-item">What's in the Box: 1 New Original HP 307A (CE743A) toner cartridge</span></li> <li><span class="a-list-item">Color: Magenta</span></li> </ul> <p>This product is not an official manufacturer product, and neither the seller nor the product has any association or endorsement of the original compatible device manufacturer.</p>

With using above code

<p>Premium Generic Quality New Laser Toner Cartridge</p>

<p>Premium Generic Toner Cartridge</p>

#VALUE!​

#VALUE!​

 

[Peter_SSs]

only thing is my description columns is not fixed as Column D in this code.

Yes, I knew that but thought that you might be able to adapt from the demonstration.

if any cells has existing html code then this solution giving the #value error.

It is not that the cell has existing html code, it is that the cell has text that is too long for the Evaluate function. The problems with sample data sometimes.

Try this instead. I have assumed that you have selected a whole single column & that it has a heading to be skipped in row 1.

Sub HTML_tags_v2()
  Dim a As Variant
  Dim i As Long
 
  With Range(Selection.Cells(2), Cells(Rows.Count, Selection.Column).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      If Left(a(i, 1), 1) <> "<" Then a(i, 1) = "<p>" & a(i, 1) & "</p>"
    Next i
    .Value = a
  End With
End Sub

 

[ankrups]

Excellent Peter. You are legend. Thank you very much for your help.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[ankrups]

You're welcome. Thanks for the follow-up.

HI Peter,

I got one more challenge in this project. I need to now convert some existing data displayed into columns to a non HTML format.

For example, below column "Product Description" has one row with HTML Tags and first two with out HTML Tags. I need to create a solution where If I select the columns then it creates HTML tags (which you already give it to me earlier). But now I need to create a functionality where if I select the columns then it removes the HTML tags and make it static text.

Let me know if you have any questions.

Thanks in advance.

 

[Peter_SSs]

Try this

Sub RemoveTags()
  Selection.Replace What:="<*>", Replacement:="", LookAt:=xlPart
End Sub

 

[ankrups]

Try this

Sub RemoveTags()
  Selection.Replace What:="<*>", Replacement:="", LookAt:=xlPart
End Sub

excellent peter. it works. thanks for your help.

 

[Peter_SSs]

Cheers. Glad to help.