If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
detect trailing spaces in an excel document
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 |
#2
|
|||
|
|||
detect trailing spaces in an excel document
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 |
#3
|
|||
|
|||
detect trailing spaces in an excel document
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 |
#4
|
|||
|
|||
detect trailing spaces in an excel document
=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" San 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 |
#5
|
|||
|
|||
detect trailing spaces in an excel document
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. |
Thread Tools | |
Display Modes | |
|
|