Ron,
Your UDF code works great, however there are still additional formatting
codes like (hard return), and &

--
Thanks, Kevin


"Ron Rosenfeld" wrote:

On Mon, 1 Jun 2009 09:00:01 -0700, AFSSkier
wrote:

I have a refreshable spreadsheet with data imported from Access. When the
data imports, I get Access formatting codes like <div, &nbsp and font codes
(see below). I doing want the end user to have to do a search & replace a
blank, even with a macro. It only happens with memo/comment fields coming
from Access.

<div<font face=Arial size=2 color="#006666"PBA data. (ALL VERSIONS
INCLUDING MONTANA)</font</div <div </div

In an adjacent cell I want to use a function(s) to format the new cell to
look like this = PBA data. ;(ALL VERSIONS INCLUDING MONTANA)

I'm not sure of all the possible variations of formatting codes that might come
in with your import. But the following User Defined Function should strip out
most of the formatting codes if they are similar to those above. It works by
removing the <'s and everything in between. If you might have <'s within the
text portion, then a more complex algorithm would be required.

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=StripFormat(A1)

in some cell.

=================================
Option Explicit
Function StripFormat(S As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "<[^<]+"
StripFormat = re.Replace(S, "")
End Function
================================

--ron