If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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. divfont 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
|
|||
|
|||
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. divfont 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
|
|||
|
|||
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. divfont 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
|
|||
|
|||
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. divfont 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
|
|||
|
|||
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. divfont 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
|
|||
|
|||
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. divfont 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
|
|||
|
|||
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. divfont 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
|
|||
|
|||
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). divfont 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. divfont 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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|