The hard return can be handled easily enough (at least in the function I
posted), but can you give an example of the & in use so that we can figure
out how to account for it?

--
Rick (MVP - Excel)


"AFSSkier" wrote in message
...
I'll try this one more time. The codes are and &.

" 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