With a Function, how can I get rid of formating codes like <di
Jacob,
You're getting closer. However, the & HTML codes need to be replaced, not
stripped.
&(amper)nbsp = " " (space)
&(amper)amp = & (and)
--
Thanks, Kevin
"Jacob Skaria" wrote:
Dear "AFSSkier"
If you are looking for a UDF try the below which do not use any patterns...
I have tried with the examples you posted and is working...Try and feedback.
Function GetData(varRange)
Dim intTemp As Integer
If varRange = "" Then GetData = "": Exit Function
Do
intTemp = InStr(intTemp + 1, varRange, "")
If Mid(varRange, intTemp, 2) < "<" And _
Mid(varRange, intTemp, 3) < " <" Then
GetData = Mid(varRange, intTemp + 1, _
InStr(intTemp, varRange, "</") - intTemp - 1)
End If
Loop Until GetData < ""
End Function
If this post helps click Yes
---------------
Jacob Skaria
"AFSSkier" wrote:
Glen,
It's not always same. They are random formating codes from random
comments/memos.
Here's an example of the most complexed, (my comments).
<div<font face=Arial size=2KRAFT</div (hard return)
<divReport: Item Performance</div (hard return)
<div& n b s p ;(<=no spaces)</div (hard return)
<divrewrite table & a m p ;(<=no spaces) queries.</div
--
Thanks, Kevin
"Glenn" wrote:
AFSSkier wrote:
I have a refreshable spreadsheet with data imported from Access. When the
data imports, I get Access formatting codes like <div,   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)
Try this:
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("</",A1)-1),"",REPT(" ",9999)),9999))
Makes the assumption that your actual data is all in one chunk and will always
start after a "" and end before the first "</". If that's not always true, or
if there is "" or "</" within your data, then you will need to pursue more
complex solutions.
|