Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <div ?
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) -- Thanks, Kevin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <div ?
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,   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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <div ?
Try this..
=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",),"","|"),"|<",""),FIND("|",SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(A1," ",),"","|"),"|<",""))+1,FIND("<",SUBSTITUTE(SUB STITUTE(SUBSTITUTE(A1," ",),"","|"),"|<",""),2)-FIND("|",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",),"","|"),"|<",""))-1) If this post helps click Yes --------------- Jacob Skaria "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) -- Thanks, Kevin |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <div ?
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,   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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <div ?
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,   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) ^^^ Is that semi-colon a typo? I assumed it was in my response but, if not, what are the rules for its insertion? --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <div ?
I assumed the same thing in my response... it looks like the OP traded the
last space from two consecutive spaces for that semi-colon, which I assumed was a typo. -- Rick (MVP - Excel) "Ron Rosenfeld" wrote in message ... 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,   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) ^^^ Is that semi-colon a typo? I assumed it was in my response but, if not, what are the rules for its insertion? --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <div ?
And I also assumed the spaces between some of the formats (between the and
<) at the end were also typos (my code depends on this). -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... I assumed the same thing in my response... it looks like the OP traded the last space from two consecutive spaces for that semi-colon, which I assumed was a typo. -- Rick (MVP - Excel) "Ron Rosenfeld" wrote in message ... 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,   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) ^^^ Is that semi-colon a typo? I assumed it was in my response but, if not, what are the rules for its insertion? --ron |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <div ?
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,   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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
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,   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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
Jacob,
Your function works great, however there are still additional formatting codes like (hard return), and &. Also, where the field is blank or has no formatting codes, an error #VALUE! is the result. -- Thanks, Kevin "Jacob Skaria" wrote: Try this.. =MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",),"","|"),"|<",""),FIND("|",SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(A1," ",),"","|"),"|<",""))+1,FIND("<",SUBSTITUTE(SUB STITUTE(SUBSTITUTE(A1," ",),"","|"),"|<",""),2)-FIND("|",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",),"","|"),"|<",""))-1) If this post helps click Yes --------------- Jacob Skaria "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) -- Thanks, Kevin |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
The additional codes are "(hard return), and &". These did not show on my
last request. -- 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,   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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
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,   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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
Sorry, this is frustrating to me too.
The codes must be html, are & n b s p; and & a m p; (without spaces). "& n b s p ; and & a m p ;" -- 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,   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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
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,   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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
The semi-colon is a typo from a html code not showing up here. The are & n b
s p; and & a m p; (without spaces). "& n b s p ; and & a m p ;" -- Thanks, Kevin "Rick Rothstein" wrote: I assumed the same thing in my response... it looks like the OP traded the last space from two consecutive spaces for that semi-colon, which I assumed was a typo. -- Rick (MVP - Excel) "Ron Rosenfeld" wrote in message ... 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,   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) ^^^ Is that semi-colon a typo? I assumed it was in my response but, if not, what are the rules for its insertion? --ron |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
Can you show a real example of these & items in use so I can see what
locations they have to be accounted for at. -- Rick (MVP - Excel) "AFSSkier" wrote in message ... The semi-colon is a typo from a html code not showing up here. The are & n b s p; and & a m p; (without spaces). "& n b s p ; and & a m p ;" -- Thanks, Kevin "Rick Rothstein" wrote: I assumed the same thing in my response... it looks like the OP traded the last space from two consecutive spaces for that semi-colon, which I assumed was a typo. -- Rick (MVP - Excel) "Ron Rosenfeld" wrote in message ... 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,   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) ^^^ Is that semi-colon a typo? I assumed it was in my response but, if not, what are the rules for its insertion? --ron |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <div?
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. |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
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. |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
On Mon, 1 Jun 2009 10:36:01 -0700, AFSSkier
wrote: Ron, Your UDF code works great, however there are still additional formatting codes like (hard return), and & -- Thanks, Kevin That's just a matter of adding those codes to the Pattern. A code like nbsp has to be added as the hexadecimal ascii code for that which is A0. \r and \n are the codes for <CR and <LF and the & stands alone -- but I don't know how to tell the difference between an ampersand used as part of a text string, and one being used as a formatting code. But try this for the pattern line in the UDF. ====================== re.Pattern = "<[^<]+|[&\xA0\r\n]" ====================== Or, all together: ============================== Option Explicit Function StripFormat(S As String) As String Dim re As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "<[^<]+|[&\xA0\r\n]" StripFormat = re.Replace(S, "") End Function ================================= --ron |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
On Mon, 1 Jun 2009 10:36:01 -0700, AFSSkier
wrote: Ron, Your UDF code works great, however there are still additional formatting codes like (hard return), and & -- Thanks, Kevin Sorry, a little bit of research and I realized about the ampersand issue. Try this instead: --ron |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
On Mon, 1 Jun 2009 10:36:01 -0700, AFSSkier
wrote: Ron, Your UDF code works great, however there are still additional formatting codes like (hard return), and & -- Thanks, Kevin Hit send too fast. A bit of research reveals that your codes are designated by being between an "&" and a ";". So we just need to remove those substrings. Again, easily done with a slight change to the pattern: re.Pattern = "<[^<]+|[\r\n]|&[^;]+;" --ron |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
Ron,
The amp and nbsp are HTML amper codes. How do you remove them? Original Data (my comments): <div<font face=Arial size=2 color="#006666"THE (5/31)6-3 FOR VERSION 2 & 3 ARE READY FOR ON & amp ;(<=no spaces after amper) OFF REPORTS </font</div <div & nbsp ;(<=no spaces after amper) </div UDF Result (it's only stripping the none printable characters): THE (5/31)6-3 FOR VERSION 2 amp; 3 ARE READY FOR ON amp; OFF REPORTS nbsp; Result needed: THE (5/31)6-3 FOR VERSION 2 and 3 ARE READY FOR ON and OFF REPORTS. -- Thanks, Kevin "Ron Rosenfeld" wrote: On Mon, 1 Jun 2009 10:36:01 -0700, AFSSkier wrote: Ron, Your UDF code works great, however there are still additional formatting codes like (hard return), and & -- Thanks, Kevin That's just a matter of adding those codes to the Pattern. A code like nbsp has to be added as the hexadecimal ascii code for that which is A0. \r and \n are the codes for <CR and <LF and the & stands alone -- but I don't know how to tell the difference between an ampersand used as part of a text string, and one being used as a formatting code. But try this for the pattern line in the UDF. ====================== re.Pattern = "<[^<]+|[&\xA0\r\n]" ====================== Or, all together: ============================== Option Explicit Function StripFormat(S As String) As String Dim re As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "<[^<]+|[&\xA0\r\n]" StripFormat = re.Replace(S, "") End Function ================================= --ron |
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
On Mon, 1 Jun 2009 17:49:00 -0700, AFSSkier
wrote: Ron, The amp and nbsp are HTML amper codes. How do you remove them? Original Data (my comments): <div<font face=Arial size=2 color="#006666"THE (5/31)6-3 FOR VERSION 2 & 3 ARE READY FOR ON & amp ;(<=no spaces after amper) OFF REPORTS </font</div <div & nbsp ;(<=no spaces after amper) </div UDF Result (it's only stripping the none printable characters): THE (5/31)6-3 FOR VERSION 2 amp; 3 ARE READY FOR ON amp; OFF REPORTS nbsp; Result needed: THE (5/31)6-3 FOR VERSION 2 and 3 ARE READY FOR ON and OFF REPORTS. Slight change to my last Pattern after seeing this example and your results: re.Pattern = "<[^<]+|[\r\n]|&[^&;]+;" --ron |
#24
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
Ron,
You're getting closer. However, the & HTML codes need to be replaced, not stripped. &(amper)nbsp = " " (space) &(amper)amp = & (and) -- Thanks, Kevin "Ron Rosenfeld" wrote: On Mon, 1 Jun 2009 17:49:00 -0700, AFSSkier wrote: Ron, The amp and nbsp are HTML amper codes. How do you remove them? Original Data (my comments): <div<font face=Arial size=2 color="#006666"THE (5/31)6-3 FOR VERSION 2 & 3 ARE READY FOR ON & amp ;(<=no spaces after amper) OFF REPORTS </font</div <div & nbsp ;(<=no spaces after amper) </div UDF Result (it's only stripping the none printable characters): THE (5/31)6-3 FOR VERSION 2 amp; 3 ARE READY FOR ON amp; OFF REPORTS nbsp; Result needed: THE (5/31)6-3 FOR VERSION 2 and 3 ARE READY FOR ON and OFF REPORTS. Slight change to my last Pattern after seeing this example and your results: re.Pattern = "<[^<]+|[\r\n]|&[^&;]+;" --ron |
#25
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
On Mon, 1 Jun 2009 18:20:02 -0700, AFSSkier
wrote: Ron, You're getting closer. However, the & HTML codes need to be replaced, not stripped. &(amper)nbsp = " " (space) &(amper)amp = & (and) -- Thanks, Kevin Kevin, That can be done. I hope you have a typo in your information, though, because you have substituted the word "and" for a "text" ampersand in your original data. In certain types of coding, the "text" ampersand would be an incorrect usage. Also, your comments interspersed with the text string to be processed makes things a bit more confusing to me than might ordinarily have been the case, so if this doesn't work, you may need to provide more accurate input and desired outputs, with comments outside of the string to be processed. In any event, one way is to first process the string to get rid of the <html codes and all of the & HTML codes except for & and  . Then we do replacements on those two codes. So: =========================== Option Explicit Function StripFormat(S As String) As String Dim sTemp As String Dim re As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "<[^<]+|[\r\n]|(&\s*(amp|nbsp)\s*;)|&[^;&]+;" sTemp = re.Replace(S, "$1") re.Pattern = "&\s*amp\s*;" sTemp = re.Replace(sTemp, "&") re.Pattern = "&\s*nbsp\s*;" StripFormat = re.Replace(sTemp, " ") End Function =========================== In your examples, you had <space's included within the ampersand HTML coding. If you can be certain that there will never be any spaces in those locations, the UDF could be made a bit more efficient, by using the VBA replace method instead of the Regular Expression replace method. --ron |
#26
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
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. |
#27
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
On Mon, 1 Jun 2009 18:20:02 -0700, AFSSkier
wrote: Ron, You're getting closer. However, the & HTML codes need to be replaced, not stripped. &(amper)nbsp = " " (space) &(amper)amp = & (and) -- Thanks, Kevin Kevin, Also, there are, according to one source, 53 ampersand codes. If you want to be able to replace all of them, we will need to set up an array, or lookup table, to do so. Not difficult, just tedious. --ron |
#28
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
On Mon, 01 Jun 2009 22:56:42 -0400, Ron Rosenfeld
wrote: On Mon, 1 Jun 2009 18:20:02 -0700, AFSSkier wrote: Ron, You're getting closer. However, the & HTML codes need to be replaced, not stripped. &(amper)nbsp = " " (space) &(amper)amp = & (and) -- Thanks, Kevin Kevin, Also, there are, according to one source, 53 ampersand codes. If you want to be able to replace all of them, we will need to set up an array, or lookup table, to do so. Not difficult, just tedious. --ron Here's one method that first replaces all of the ampersand codes with their ASCII equivalents, and then strips out the <... codes. It assumes that the ampersand codes do NOT have any included spaces. (If they do, different coding will be required). If you copy this, be aware that the lines making up the array should all be on one single line. Your newsreader (or mine) may insert inadvertent line breaks that you will need to remove to make it work correctly. ================================= Option Explicit Function StripFormat(S As String) As String Dim AmpCodes, AmpChars Dim i As Long Dim sTemp As String Dim re As Object AmpCodes = Array(""", "&", "<", ">", " ", "¡", "¢", "£", "¤", "¥", "¦", "§", "¨", "©", "ª", "«", "¬", "­", "®", "¯", "°", "±", "²", "³", "´", "µ", "¶", "·", "¸", "¹", "º", "»", "¼", "½", "¾", "¿", "×", "÷", "Ð", "ð", "Þ", "þ", "Æ", "æ", "Œ", "œ", "Å", "Ø", "Ç", "ç", "ß", "Ñ", "ñ") AmpChars = Array("""", "&", "<", "", "*", "", "", "", "", "", "", "", "", "", "", "", "", "*", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "") 'Replace HTML Ampersand Codes sTemp = S For i = 0 To UBound(AmpCodes) sTemp = Replace(sTemp, AmpCodes(i), AmpChars(i)) Next i 'Strip out < codes; CR and LF Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "<[^<]+|[\r\n]" StripFormat = re.Replace(sTemp, "") End Function ===================================== --ron |
#29
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
Ron,
From your latest UDF works awesome! Thank you very much for your time a efforts. Kevin "Ron Rosenfeld" wrote: On Mon, 01 Jun 2009 22:56:42 -0400, Ron Rosenfeld wrote: On Mon, 1 Jun 2009 18:20:02 -0700, AFSSkier wrote: Ron, You're getting closer. However, the & HTML codes need to be replaced, not stripped. &(amper)nbsp = " " (space) &(amper)amp = & (and) -- Thanks, Kevin Kevin, Also, there are, according to one source, 53 ampersand codes. If you want to be able to replace all of them, we will need to set up an array, or lookup table, to do so. Not difficult, just tedious. --ron Here's one method that first replaces all of the ampersand codes with their ASCII equivalents, and then strips out the <... codes. It assumes that the ampersand codes do NOT have any included spaces. (If they do, different coding will be required). If you copy this, be aware that the lines making up the array should all be on one single line. Your newsreader (or mine) may insert inadvertent line breaks that you will need to remove to make it work correctly. ================================= Option Explicit Function StripFormat(S As String) As String Dim AmpCodes, AmpChars Dim i As Long Dim sTemp As String Dim re As Object AmpCodes = Array(""", "&", "<", "", " ", "¡", "¢", "£", "¤", "¥", "¦", "§", "¨", "©", "ª", "«", "¬", "*", "®", "¯", "°", "±", "²", "³", "´", "µ", "¶", "·", "¸", "¹", "º", "»", "¼", "½", "¾", "¿", "×", "÷", "Ð", "ð", "Þ", "þ", "Æ", "æ", "Œ", "œ", "Å", "Ø", "Ç", "ç", "ß", "Ñ", "ñ") AmpChars = Array("""", "&", "<", "", " ", "¡", "¢", "£", "¤", "¥", "¦", "§", "¨", "©", "ª", "«", "¬", "*", "®", "¯", "°", "±", "²", "³", "´", "µ", "¶", "·", "¸", "¹", "º", "»", "¼", "½", "¾", "¿", "×", "÷", "Ð", "ð", "Þ", "þ", "Æ", "æ", "Œ", "œ", "Å", "Ø", "Ç", "ç", "ß", "Ñ", "ñ") 'Replace HTML Ampersand Codes sTemp = S For i = 0 To UBound(AmpCodes) sTemp = Replace(sTemp, AmpCodes(i), AmpChars(i)) Next i 'Strip out < codes; CR and LF Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "<[^<]+|[\r\n]" StripFormat = re.Replace(sTemp, "") End Function ===================================== --ron |
#30
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#31
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With a Function, how can I get rid of formating codes like <di
On Tue, 2 Jun 2009 07:36:01 -0700, AFSSkier
wrote: Ron, From your latest UDF works awesome! Thank you very much for your time a efforts. Kevin Glad to help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formating only on sum function not on =A1+A2 | Excel Worksheet Functions | |||
Conditional Formating or IF Function | Excel Discussion (Misc queries) | |||
How can i remove the lotus formating codes when copying? | Excel Discussion (Misc queries) | |||
How can i remove the lotus formating codes when copying? | Excel Discussion (Misc queries) | |||
formating zip codes with leading zero to print zero | Excel Discussion (Misc queries) |