Removing space

How do I delete spaces at the beginning of a cell for the whol
column?:confused

--
Message posted from http://www.ExcelForum.com

Gord
Sun Jun 20 17:43:53 CDT 2004

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


On Sun, 20 Jun 2004 14:40:01 -0500, lstrat
<<lstrat.185zon@excelforum-nospam.com>> wrote:

>How do I delete spaces at the beginning of a cell for the whole
>column?:confused:
>
>
>---
>Message posted from http://www.ExcelForum.com/

Dave
Sun Jun 20 17:49:17 CDT 2004

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


"lstrat <" wrote:
>
> How do I delete spaces at the beginning of a cell for the whole
> column?:confused:
>
> ---
> Message posted from http://www.ExcelForum.com/

--

Dave Peterson
ec35720@msn.com