How to eliminate spurious data in a string?

[Peter Chatterton]

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.

 

[Don Guillett]

Is it always the same thing with the same length.
You could use find/replace or a substitute formula or a cell=left(cell)-8 or
whatever.

 

[keepITcool]

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

 

[Peter Chatterton]

Since the data is from an outside source, I'd like to clean it up properly.

I wonder if I'm doing something stupid (apart from trusting Excel that is).
My simplest code is:

Sub spuriousData4()
Dim value As String

value = ActiveCell.value
value = Mid(value, 1, Len(value) - 1)
value = Application.WorksheetFunction.Clean(value)
ActiveCell.value = value
End Sub

and this still doesn't work.
And what's more,
it doesn't even clean up ordinary trailing blanks = X'20'.
And I'm not trusting the last statement to rewrite it,
I'm going by the debugger.

Thanks, and still bewildered,
Peter.


If it were a function I wouldn't expect to be able to change the worksheet

 

[Peter Chatterton]

Here's a really simple version that doesn't work:

Sub spuriousData5()
ActiveCell.Value = Application.WorksheetFunction.Clean(
ctiveCell.Value )
'ActiveCell.value = "new" 'this works
End Sub

The cell contains "a" followed by ordinary blanks.

Still baffled,
Peter.

 

[Peter Chatterton]

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]

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

 

[Dick Kusleika]

Peter

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

See here http://www.dicks-blog.com/excel/2004/09/the_worksheetfu.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.

 

[Peter Chatterton]

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]

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.

 

[Peter Chatterton]

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]

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.

 

[Peter Chatterton]

Peter


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.

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)

 

[Dick Kusleika]

Peter

**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.