Removing space

  • Thread starter Thread starter lstrat
  • Start date Start date
Manually......

In an adjacent column enter =TRIM(cellref). Double-click on the fill-handle
at bottom right of this cell and it will replicate down as far as data in the
original column.

When happy with the results, Copy the column with the TRIM formula and paste
special>values(in place).

Then delete original column.

VBA Macro..........

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 Excel MVP
 
There's an =trim() worksheet function that will eliminate leading, trailing and
multiple internal spaces.

if a1 held:
____asdf____asdfasdf____
(underscore represents the space character)
and you did =trim(a1), you'd get:
asdf_asdfasdf

If you want to keep the multiple internal spaces (and remove the leading and
trailing spaces, you could do:

Select your column
Data|Text to columns
fixed width
(remove all the lines that excel guessed and don't add any yourself.)

That sample would look like this after:
asdf____asdfasdf

If you only wanted to get rid of the leading spaces, you could use a macro:

Option Explicit
Sub testme()
Dim myCell As Range
For Each myCell In Selection.Cells
myCell.Value = LTrim(myCell.Value)
Next myCell
End Sub

This will leave trailing and multiple internal spaces, like:

asdf____asdfasdf____



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top