Replace html code - formula too long

[andy62]

I receive some files extracted from a corporate system. A couple of the
extracted data fields are rich text that use html code, and those who provide
me the data don't have any way to "turn off" the codes before they do the
extract. Once I get the data into Excel (2003) I can remove all the codes
using Find/Replace with a wildcard: <*>. Since all the codes are always in
angle brackets, this essentially removes any instance, no matter what the
included codes.

The only glitch is that some of the cells are very long, causing that
"Formula is too long" error. I saw somewhere a macro I could use instead of
running Find/Replace, but if that's the best way around the problem, can
anyone tell me how to replicate a wildcard in the macro? The macro was going
to replace a specific term, "$$$$$", with nothing. Now I need a wildcard so
the macro will find and replace anything contained within angle brackets.

TIA

 

[JLatham]

Try this macro - doesn't care what's between < and >, only that there are
matching pairs of them in the text.

As an example, 123<456>789<abc>def will end up as 123789def when it finishes
with the source text. No error testing, so a mismatched pair could cause a
run time error.

Sub RemoveHTML()
'select all cells with HTML containing text in them
'and then call this routine from Tools | Macro | Macros
'errors could occur if '<' and '>' aren't paired up in
'a string of text
Dim anyCell As Range
Dim tempText As String
Dim leftCaret As Long
Dim rightCaret As Long
For Each anyCell In Selection
If Not IsEmpty(anyCell) Then
tempText = anyCell.Value
Do While InStr(tempText, "<")
leftCaret = InStr(tempText, "<")
rightCaret = InStr(leftCaret + 1, tempText, ">")
tempText = Left(tempText, leftCaret - 1) & _
Right(tempText, Len(tempText) - rightCaret)
Loop
anyCell = tempText
End If
Next
End Sub