JimS
10-16-2009, 09:17 AM
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>SI# 6312341223>PSLK> Tack# 39134>$125</i></span></div> <span style="font-family: Arial; color: #ff0000;"><i>PS# DEF1234>SI#6312341224>PS$HIJ0987> Tack# 21546>$150</i></span></div> <span style="font-family: Arial; color: #ff0000;"><i>PS# MNO583018>SI# 6312341225>PS#FFST68> Tack# 87903>$200</i></span></div>
Here the actual data that I need to end up with:
SA -Prast (123-456-7890) PS# ABC12344>SI# 6312341223>PSLK> Tack# 39134>$125 PS# DEF1234>SI#6312341224>PS$HIJ0987> Tack# 21546>$150 PS# MNO583018>SI# 6312341225>PS#FFST68> Tack# 87903>$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
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>SI# 6312341223>PSLK> Tack# 39134>$125</i></span></div> <span style="font-family: Arial; color: #ff0000;"><i>PS# DEF1234>SI#6312341224>PS$HIJ0987> Tack# 21546>$150</i></span></div> <span style="font-family: Arial; color: #ff0000;"><i>PS# MNO583018>SI# 6312341225>PS#FFST68> Tack# 87903>$200</i></span></div>
Here the actual data that I need to end up with:
SA -Prast (123-456-7890) PS# ABC12344>SI# 6312341223>PSLK> Tack# 39134>$125 PS# DEF1234>SI#6312341224>PS$HIJ0987> Tack# 21546>$150 PS# MNO583018>SI# 6312341225>PS#FFST68> Tack# 87903>$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