Remove leading blank(s)

[GKW in GA]

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

 

[GKW in GA]

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

 

[Gord Dibben]

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

 

[Gord Dibben]

Sandy

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

Have I missed a step?


Gord

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk