How to eliminate spurious data in a string?
 

Thanks very much.

Two questions:
How can you say Application.Substitute
without saying WorksheetFunction?

Where are these functions in VBA help?

Thanks again,
Peter.

"keepITcool" wrote in message
...

All the are worksheetfunctions...

the first clean takes care of non printing (incl tabs and linefeeds!)
the subst takes care of the chr(160)
the trim takes care of the double spaces in mid text, and the starting
and trailing spaces.

Note the &h denotes hexcodes
&h7 = 7 = tab
&ha = 10 = linefeed
&h20 = 32 = space
&hA0 = 160 = non breaking space


Sub cleanstring()
With activecell
'fill with dirt
.Value = "abc" & Chr$(&H20) & Chr$(&H7) & Chr$(&HA) & Chr$(&HA0)
'clean and trim
'always use application iso application.worksheetfunction
'as this has error handling advantages :)
.Value = Application.Trim( _
Application.Substitute( _
Application.Clean(.Value), Chr(&HA0), vbNullString))

End With
End Sub




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Peter Chatterton wrote in message
:

I have a cell that contains X'2020A0' as trailing spaces.
('A0' I am told is a non-breaking space in html)

I've used the following on it:
Application.WorksheetFunction.Clean
RTrim
but can't get them to go away.

Any suggestions would be much appreciated,
and thanks,
Peter.

P.S. The data comes from an external source.

How to eliminate spurious data in a string?
 

these are the same as application.worksheetfunction except you dont get
intellisense.

The big advantage of 'forgetting' the worksheetfunction qualifier is
that in case of errors like #NA they will pass the errorvalue as the
function's result where application.worksheetfunction will raise an
error.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Peter Chatterton wrote in message
:

Thanks very much.

Two questions:
How can you say Application.Substitute
without saying WorksheetFunction?

Where are these functions in VBA help?

Thanks again,
Peter.

"keepITcool" wrote in message
...

All the are worksheetfunctions...

the first clean takes care of non printing (incl tabs and linefeeds!)
the subst takes care of the chr(160)
the trim takes care of the double spaces in mid text, and the
starting
and trailing spaces.

Note the &h denotes hexcodes
&h7 = 7 = tab
&ha = 10 = linefeed
&h20 = 32 = space
&hA0 = 160 = non breaking space


Sub cleanstring()
With activecell
'fill with dirt
.Value = "abc" & Chr$(&H20) & Chr$(&H7) & Chr$(&HA) & Chr$(&HA0)
'clean and trim
'always use application iso application.worksheetfunction
'as this has error handling advantages :)
.Value = Application.Trim( _
Application.Substitute( _
Application.Clean(.Value), Chr(&HA0), vbNullString))

End With
End Sub




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Peter Chatterton wrote in message
:

I have a cell that contains X'2020A0' as trailing spaces.
('A0' I am told is a non-breaking space in html)

I've used the following on it:
Application.WorksheetFunction.Clean
RTrim
but can't get them to go away.

Any suggestions would be much appreciated, and thanks,
Peter.

P.S. The data comes from an external source.

How to eliminate spurious data in a string?
 

Peter


Two questions:
How can you say Application.Substitute
without saying WorksheetFunction?

See here http://www.dicks-blog.com/excel/2004...rksheetfu.html


Where are these functions in VBA help?


If you look up the WorksheetFunction object in VBA help, then click on the
Methods link, you can get a list of functions available.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

How to eliminate spurious data in a string?
 

Thanks Dick, but when I looked up WorksheetFunction Object Methods
(from F1 in the macro editor) it only gave me 5 methods.
Peter.

"Dick Kusleika" wrote in message
...
Peter


Two questions:
How can you say Application.Substitute
without saying WorksheetFunction?

See here http://www.dicks-blog.com/excel/2004...rksheetfu.html


Where are these functions in VBA help?


If you look up the WorksheetFunction object in VBA help, then click on the
Methods link, you can get a list of functions available.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

How to eliminate spurious data in a string?
 

Peter

Me too, but one of the "methods" is List of Worksheet Functions Available to
Visual Basic. Click on that it will give you all 200 or so functions that
you can access via the WorksheetFunction object. That's in XL2000, so if
you're using a different version, it may be different.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Peter Chatterton" wrote in message
.. .
Thanks Dick, but when I looked up WorksheetFunction Object Methods
(from F1 in the macro editor) it only gave me 5 methods.
Peter.

"Dick Kusleika" wrote in message
...
Peter


Two questions:
How can you say Application.Substitute
without saying WorksheetFunction?

See here http://www.dicks-blog.com/excel/2004...rksheetfu.html


Where are these functions in VBA help?


If you look up the WorksheetFunction object in VBA help, then click on
the
Methods link, you can get a list of functions available.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Worksheet Functions
 

I'm confused.

How can I tell from looking at Application.Substitute()
what's going on?

Incidentally (?), one of my books has Substitute as an
Object model function, not a Worksheet Function.

Peter.

BTW there is a "list of worksheet functions available to Visual Basic"
but the only way I can now get to it is by entering that phrase in the
Answer Wizard!

ALSO Does it mess with people if I change the Subject line?
I'm using Outlook Express with an option to keep msg's together,
but can I assume everyone else does?


"Dick Kusleika" wrote in message
...
Peter

Me too, but one of the "methods" is List of Worksheet Functions Available
to
Visual Basic. Click on that it will give you all 200 or so functions that
you can access via the WorksheetFunction object. That's in XL2000, so if
you're using a different version, it may be different.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Peter Chatterton" wrote in message
.. .
Thanks Dick, but when I looked up WorksheetFunction Object Methods
(from F1 in the macro editor) it only gave me 5 methods.
Peter.

"Dick Kusleika" wrote in message
...
Peter


Two questions:
How can you say Application.Substitute
without saying WorksheetFunction?

See here http://www.dicks-blog.com/excel/2004...rksheetfu.html


Where are these functions in VBA help?


If you look up the WorksheetFunction object in VBA help, then click on
the
Methods link, you can get a list of functions available.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Worksheet Functions
 

Peter

I'm confused.

How can I tell from looking at Application.Substitute()
what's going on?

Do you mean with intellisense? You can't. If you don't know the syntax for
the function, you have to look it up in help.


Incidentally (?), one of my books has Substitute as an
Object model function, not a Worksheet Function.

That would be a new one on me. Which book?


BTW there is a "list of worksheet functions available to Visual Basic"
but the only way I can now get to it is by entering that phrase in the
Answer Wizard!

We're probably using different versions of Excel. The help system gets
worse with each version.


ALSO Does it mess with people if I change the Subject line?
I'm using Outlook Express with an option to keep msg's together,
but can I assume everyone else does?

Yes, some people. Some newsreaders group by subject rather than message ID.
I've even had problems with that in OE, but I don't remember the specifics.
Also, I believe it screws up the Google archives when the subjects are
changed.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Worksheet Functions
 

"Dick Kusleika" wrote in message
...
Peter

I'm confused.

How can I tell from looking at Application.Substitute()
what's going on?

Do you mean with intellisense? You can't. If you don't know the syntax
for
the function, you have to look it up in help.

**What is intellisense?
**So if I look under Worksheet object methods I should find it?
There are 27, from Activate to UnProtect.

Incidentally (?), one of my books has Substitute as an
Object model function, not a Worksheet Function.

That would be a new one on me. Which book?

**Excel 2002 VBA: Programmers Reference
by Rob Bovey, Stephen Bullen, John Green, Robert Rosenberg

(I don't mean it's not an Worksheet object method)

Worksheet Functions
 

Peter


How can I tell from looking at Application.Substitute()
what's going on?

Do you mean with intellisense? You can't. If you don't know the syntax
for
the function, you have to look it up in help.

**What is intellisense?
**So if I look under Worksheet object methods I should find it?
There are 27, from Activate to UnProtect.

Intellisense is that little yellow box that pops up when you're typing vba.
For instance if you type Workbooks.Open and then a space, it will show you
what the arguments are.

Not VBA help, but regular Excel help. That's where you'll find the syntax
for the worksheet functions. Sorry I wasn't more clear about that.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com