Solved: VBA to delete text between < >

[JimS]

I have a worksheet with several columns and rows of data that is an export from a database. Unfortunately the database includes all the control characters that it uses with the data.

Does anyone have a macro that can evaluate every cell and delete anything that begins with "<" and ends with ">"?

It should also delete the <>.

There could be several <> control charters within a single cell.

Here's an example of what gets exported out of the database into 1 cell:

<span style="font-family: Arial; color: #ff0000;"><b>SA</b>-Prast (123-456-7890)
</span></div> <span style="font-family: Arial; color: #ff0000;"><br></span></div> <span style="font-family: Arial; color: #ff0000;"><i>PS# ABC12344&gt;SI# 6312341223&gt;PSLK&gt; Tack# 39134&gt;$125</i></span></div> <span style="font-family: Arial; color: #ff0000;"><i>PS# DEF1234&gt;SI#6312341224&gt;PS$HIJ0987&gt; Tack# 21546&gt;$150</i></span></div> <span style="font-family: Arial; color: #ff0000;"><i>PS# MNO583018&gt;SI# 6312341225&gt;PS#FFST68&gt; Tack# 87903&gt;$200</i></span></div>


Here the actual data that I need to end up with:

SA -Prast (123-456-7890) PS# ABC12344&gt;SI# 6312341223&gt;PSLK&gt; Tack# 39134&gt;$125 PS# DEF1234&gt;SI#6312341224&gt;PS$HIJ0987&gt; Tack# 21546&gt;$150 PS# MNO583018&gt;SI# 6312341225&gt;PS#FFST68&gt; Tack# 87903&gt;$200

Basically removing all strings that begin with a "<" and end with ">" (including everything in between) and entering a space in it place.

Any ideas would greatly be appreciated.

JimS

[Bob Phillips]

[vba]

Public Function StripHtML(inval)
Dim RegEx As Object

Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Global = True
RegEx.Pattern = "<(.|\n)+?>"
StripHtML = RegEx.Replace(inval, "")

End Function
[/vba]

[JimS]

How do I call this routine?

[Bob Phillips]

From a worksheet as a function, or from a macro.

[JimS]

Excellent, Thanks...