Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting non-alphanumeric characters
Hello, I have code that extracts certain things from a text file
containing source code for a web site. One of the things I am extracting is the web page name. For some reason it extracts it with a LOT of carriage returns. Like 7-8 of those rectangular characters. I have in my code the following: Cells.Replace What:=Chr(13), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart, SearchOrde:=xlByRows, MatchCase:=False But it only seem to delete like the first and last 2 of the characters...I can't figure out how to get rid of them. I figured if I also put something in the code along the lines of "If not alphanumeric then delete character (not entire cell)" Any suggestions?? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting non-alphanumeric characters
Try replacing Chr(160) with " " or "" -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Mark" wrote in message Hello, I have code that extracts certain things from a text file containing source code for a web site. One of the things I am extracting is the web page name. For some reason it extracts it with a LOT of carriage returns. Like 7-8 of those rectangular characters. I have in my code the following: Cells.Replace What:=Chr(13), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart, SearchOrde:=xlByRows, MatchCase:=False But it only seem to delete like the first and last 2 of the characters...I can't figure out how to get rid of them. I figured if I also put something in the code along the lines of "If not alphanumeric then delete character (not entire cell)" Any suggestions?? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting non-alphanumeric characters
On Apr 29, 9:47*am, "Jim Cone" wrote:
Try replacing Chr(160) with *" " *or *"" -- Jim Cone Portland, Oregon *USAhttp://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Mark" wrote in message Hello, I have code that extracts certain things from a text file containing source code for a web site. *One of the things I am extracting is the web page name. *For some reason it extracts it with a LOT of carriage returns. *Like 7-8 of those rectangular characters. I have in my code the following: * * Cells.Replace What:=Chr(13), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False * * Cells.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart, SearchOrde:=xlByRows, MatchCase:=False But it only seem to delete like the first and last 2 of the characters...I can't figure out how to get rid of them. *I figured if I also put something in the code along the lines of "If not alphanumeric then delete character (not entire cell)" Any suggestions?? Thanks! Yea for some odd reason they still won't delete. I have no idea why. Is there a way to seach for ASCII characters? I looked it up and its 0013 I think... Thanks again |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting non-alphanumeric characters
Yea for some odd reason they still won't delete. I have no idea why.
Is there a way to seach for ASCII characters? I looked it up and its 0013 I think... Let me preface this by saying I have zero experience with international coding issues (all my programming efforts have been US based), so this is only a guess. That 0013 may be a Unicode value... try using ChrW instead of Chr and see if that finds them. Rick |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting non-alphanumeric characters
Run the Excel "Clean" function on each cell. Either on the worksheet in a helper column or in code using Application.Clean( ) -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Mark" wrote in message Yea for some odd reason they still won't delete. I have no idea why. Is there a way to seach for ASCII characters? I looked it up and its 0013 I think... Thanks again |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting non-alphanumeric characters
On Tue, 29 Apr 2008 07:24:31 -0700 (PDT), Mark
wrote: Hello, I have code that extracts certain things from a text file containing source code for a web site. One of the things I am extracting is the web page name. For some reason it extracts it with a LOT of carriage returns. Like 7-8 of those rectangular characters. I have in my code the following: Cells.Replace What:=Chr(13), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart, SearchOrde:=xlByRows, MatchCase:=False But it only seem to delete like the first and last 2 of the characters...I can't figure out how to get rid of them. I figured if I also put something in the code along the lines of "If not alphanumeric then delete character (not entire cell)" Any suggestions?? Thanks! Is the text file coded in Unicode? If *not*, then you could do something like: ======================= Dim c As Range Dim re As Object Set re = CreateObject("vbscript.regexp") re.IgnoreCase = True re.Global = True re.Pattern = "[^ -~]+" For Each c In Selection c.Value = re.Replace(c.Value, "") Next c ========================= The regular expression selects any character that is not in the class (range) of the <space character (ASCII code 32) to the tilde (ASCII code 126) and replaces it with nothing. --ron |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting non-alphanumeric characters
On Apr 29, 12:22*pm, Ron Rosenfeld wrote:
On Tue, 29 Apr 2008 07:24:31 -0700 (PDT), Mark wrote: Hello, I have code that extracts certain things from a text file containing source code for a web site. *One of the things I am extracting is the web page name. *For some reason it extracts it with a LOT of carriage returns. *Like 7-8 of those rectangular characters. I have in my code the following: * *Cells.Replace What:=Chr(13), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False * *Cells.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart, SearchOrde:=xlByRows, MatchCase:=False But it only seem to delete like the first and last 2 of the characters...I can't figure out how to get rid of them. *I figured if I also put something in the code along the lines of "If not alphanumeric then delete character (not entire cell)" Any suggestions?? Thanks! Is the text file coded in Unicode? *If *not*, then you could do something like: ======================= Dim c As Range Dim re As Object Set re = CreateObject("vbscript.regexp") * * re.IgnoreCase = True * * re.Global = True * * re.Pattern = "[^ -~]+" For Each c In Selection * * c.Value = re.Replace(c.Value, "") Next c ========================= The regular expression selects any character that is not in the class (range) of the <space character (ASCII code 32) to the tilde (ASCII code 126) and replaces it with nothing. --ron- Hide quoted text - - Show quoted text - Thanks Ron That worked perfectly, nice and clean without any boxes! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting non-alphanumeric characters
On Wed, 30 Apr 2008 08:24:35 -0700 (PDT), Mark
wrote: Thanks Ron That worked perfectly, nice and clean without any boxes! Glad to help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File names appended with ~ plus alphanumeric characters | Excel Discussion (Misc queries) | |||
HOW DO YOU COUNT ALPHA CHARACTERS IN AN ALPHANUMERIC FIELD | Excel Discussion (Misc queries) | |||
Identify non-alphanumeric characters within a cell in Excel | Excel Programming | |||
Search for non alphanumeric characters | Excel Programming | |||
Strip Alpha Characters out of an Alphanumeric Dataset | Excel Programming |