How can I remove leading blanks from all cells in a selected column. I prefer
not to use a function because I prefer not to have to create another column.
Macro or FIND/Replace would be best if possible

Copy an unused cell then highlight the column you want to change then select
Past Special Add OK

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"GKW in GA" wrote in message
...
How can I remove leading blanks from all cells in a selected column. I
prefer
not to use a function because I prefer not to have to create another
column.
Macro or FIND/Replace would be best if possible

Thanks. So much for the how, now for the "why". What does ADD do?

"Sandy Mann" wrote:

Copy an unused cell then highlight the column you want to change then select
Past Special Add OK

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"GKW in GA" wrote in message
...
How can I remove leading blanks from all cells in a selected column. I
prefer
not to use a function because I prefer not to have to create another
column.
Macro or FIND/Replace would be best if possible

Sub TRIM_EXTRA_SPACES()
Dim Cell As Range
For Each Cell In Selection
If (Not IsEmpty(Cell)) And _
Not IsNumeric(Cell.Value) And _
InStr(Cell.Formula, "=") = 0 _
Then Cell.Value = Application.Trim(Cell.Value)
Next
End Sub


Gord Dibben MS Excel MVP

On Sat, 16 Aug 2008 14:56:02 -0700, GKW in GA
wrote:

How can I remove leading blanks from all cells in a selected column. I prefer
not to use a function because I prefer not to have to create another column.
Macro or FIND/Replace would be best if possible

Sandy

This does not work for me to remove leading spaces in a cell.

Have I missed a step?


Gord

On Sat, 16 Aug 2008 23:09:51 +0100, "Sandy Mann"
wrote:

Copy an unused cell then highlight the column you want to change then select
Past Special Add OK

"GKW in GA" wrote in message
...
Thanks. So much for the how, now for the "why". What does ADD do?


When you do maths on a piece of text that looks like a number then XL
converts the Text to a real number.

First of all check that all cells A1:B3 are indeed formatted to General then
enter in B1 =ISTEXT(A1) and copy down to B3, you will get three FALSE
returns. If you want to double check it enter =SUM(A1:A3) in C1 and you
will get 0 because SUM() ignores text.

Now A2 enter =A1 and you will again get a text 3 in A2 and C1 will still be
reported zero as the sum

Next enter in A3, =A1+0. You will yet again get a right aligned 3 but this
time B3 will remain reporting FALSE because although the cell will have
changed formatting to TEXT, (check it and see), the contents of the cell
will have been changed to a number. Reformat the cell to General and you
will have what we did with the Paste Special. Quite why Paste special does
the adding and formatting, (or perhaps prevents the format changing), I do
not know, you will have to ask one of the experts around here, (any takers?)

In A3, it you highlight just the A1 in the formula bar and press F9 you will
get "3" returned because A1 is still text. Press Escape or the 'X' to the
left of the formula bar if you have one to stop the "3" being hard coded
into the formula. If you highlight A1+0 in the formula then you will get 3
returned because once the addition has been done you will have a number.
You can of course use the number 1 in a cell just as well. In fact, and
this surprised me, you can enter a Text 1 in a cell then in any cell
formatted as General, enter =A1*C2 (where C2 holds a Text "1") and you still
get a real number, (or for the purest ISNUMBER() return TRUE).

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

Hi Gord,

I assumed that the OP was talking about numbers with a leading space. I
assume that it works for you with numbers?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Sandy

This does not work for me to remove leading spaces in a cell.

Have I missed a step?


Gord

On Sat, 16 Aug 2008 23:09:51 +0100, "Sandy Mann"

wrote:

Copy an unused cell then highlight the column you want to change then
select
Past Special Add OK

Yes, works with numbers

I assumed text for some reason.

Maybe a text day<g


Gord

On Sun, 17 Aug 2008 12:52:55 +0100, "Sandy Mann"
wrote:

Hi Gord,

I assumed that the OP was talking about numbers with a leading space. I
assume that it works for you with numbers?