Help!

detect trailing spaces in an excel document

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Discussions RSS
Next:  I can not find my signature in the drop down menu..  
Author Message
San antonio
External


Since: Apr 09, 2010
Posts: 1



PostPosted: Fri Apr 09, 2010 6:38 am    Post subject: detect trailing spaces in an excel document
Archived from groups: microsoft>public>excel>misc (more info?)

Dears,
I know how to cancel trailing spaces in a document.
But is there a way to detect where they are in an excel document ?
Thanks
Back to top
Mike H
External


Since: May 24, 2006
Posts: 1182



PostPosted: Fri Apr 09, 2010 6:46 am    Post subject: RE: detect trailing spaces in an excel document [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hi,

Detecting them isn't straightforward, you generally find you have them when
you start getting unexpected results from a formula.

You can remove leading/trailing spaces using

=TRIM(A1)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"San antonio" wrote:

> Dears,
> I know how to cancel trailing spaces in a document.
> But is there a way to detect where they are in an excel document ?
> Thanks
Back to top
Gary''s Student
External


Since: Oct 01, 2005
Posts: 3403



PostPosted: Fri Apr 09, 2010 6:59 am    Post subject: RE: detect trailing spaces in an excel document [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Try this simple macro:

Sub FindSpace()
Dim r As Range, rr As Range, rs As Range
Set rs = Nothing
For Each r In ActiveSheet.UsedRange
v = r.Value
If Len(v) = 0 Then
Else
If Right(v, 1) = " " Then
If rs Is Nothing Then
Set rs = r
Else
Set rs = Union(rs, r)
End If
End If
End If
Next

If rs Is Nothing Then
Else
rs.Select
End If
End Sub

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Gary''s Student - gsnu201001


"San antonio" wrote:

> Dears,
> I know how to cancel trailing spaces in a document.
> But is there a way to detect where they are in an excel document ?
> Thanks
Back to top
David Biddulph
External


Since: Feb 24, 2007
Posts: 1373



PostPosted: Fri Apr 09, 2010 5:10 pm    Post subject: Re: detect trailing spaces in an excel document [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

=RIGHT(A1)=" " will return TRUE if you have trailing spaces, and FALSE if
you don't.
You could use that formula as a conditional formatting condition, if you
wish.
--
David Biddulph


"San antonio" wrote in message

> Dears,
> I know how to cancel trailing spaces in a document.
> But is there a way to detect where they are in an excel document ?
> Thanks
Back to top
Gord Dibben
External


Since: Feb 23, 2004
Posts: 9431



PostPosted: Fri Apr 09, 2010 7:36 pm    Post subject: Re: detect trailing spaces in an excel document [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

In addition to David's reply...............possibly =RIGHT(A1)=CHAR(160)
for the html non-breaking spaces if you have those.


Gord Dibben MS Excel MVP

On Fri, 9 Apr 2010 17:14:55 +0100, "David Biddulph" <groups [at]
biddulph.org.uk> wrote:

>=RIGHT(A1)=" " will return TRUE if you have trailing spaces, and FALSE if
>you don't.
>You could use that formula as a conditional formatting condition, if you
>wish.
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Discussions All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum