The code I post in my earlier response assumes the spaces between the and
< symbols that you show in parts of your sample text are typos. If they are
not typos, then you will need to use this code instead...

Function StripFormat(ByVal S As String) As String
Dim X As Long
Dim Parts() As String
Do While InStr(S, " ")
S = Replace(S, " ", " ")
Loop
Parts = Split(Replace(S, " <", "<"), "<")
For X = 1 To UBound(Parts)
If Not Parts(X) Like "*" Then
StripFormat = Split(Parts(X), "")(1)
Exit For
End If
Next
End Function

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
As a follow up to Ron's posting, here is a non-Regular Expression UDF for
you to consider...

Function StripFormat(S As String) As String
Dim X As Long
Dim Parts() As String
Parts = Split(S, "<")
For X = 1 To UBound(Parts)
If Not Parts(X) Like "*" Then
StripFormat = Split(Parts(X), "")(1)
Exit For
End If
Next
End Function

--
Rick (MVP - Excel)


"AFSSkier" wrote in message
...
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)

--
Thanks, Kevin