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!

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!

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

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

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

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

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!

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