Clean up HTML tags in an Excel Export

This has been very useful for me in the past.

When an HTML field is exported to Excel, it displays all the HTML tags which makes it very difficult to read in Excel. Currently there is no way to hide the HTML tags when exporting but this is a useful Excel macro to clean it up after the export.

Open the Visual basic editor in Excel (Alt + F11)

Right click on the sheet -> Insert -> Module

Enter this code and then click F5 to run

Sub StripTags()

      Dim cell               As Range

      Dim s                     As String

      Dim asWd()           As String

      Dim iWd                 As Long

 

      For Each cell In Intersect(Selection.Cells, ActiveSheet.UsedRange)

              s = Replace(cell.Value, Chr(160), " ")

              s = Replace(s, ">", "<")

              s = Replace(s, vbCr, vbLf)

   

              asWd = Split(s, "<")

              s = vbNullString

              For iWd = 0 To UBound(asWd) Step 2

                      s = s & " " & asWd(iWd)

              Next iWd

 

              Do While InStr(s, vbLf & " ")

                      s = Replace(s, vbLf & " ", vbLf)

              Loop

 

              Do While InStr(s, vbLf & vbLf)

                      s = Replace(s, vbLf & vbLf, vbLf)

              Loop

   

              cell.Value = WorksheetFunction.Trim(s)

      Next cell

End Sub