detect trailing spaces in an excel document

[San antonio]

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

 

[Mike H]

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.

 

[Gary''s Student]

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

 

[David Biddulph]

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

 

[Gord Dibben]

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