Spaces in Text within Cells

[Chip Rose]

Can I create a macro that will remove a couple spaces
between words or characters within a cell. Have tried to
create a macro to do this that results in replacing the
cell's contents with the original data that was in the
cell that I originally created the macro in. Easy in
Lotus... a pain in Excel. I would greatly appreciate any
help on this one.

Chip

 

[EarlK]

Chip,

You could put =TRIM(A2) in another cell. For a permanent change to the
original cell, you could copy the formula cell, then Paste special - Values
over the original cell, then get rid of the formula.

The code would be

Range("A2")=WorksheetFunction.Trim(Range("A2"))

or for a column:

Sub RemoveSpaces()
Range("A2").Select ' select starting cell
Do While Selection <> "" ' run until empty cell
Selection = WorksheetFunction.Trim(Selection)
Selection.Offset(1, 0).Select ' move down
Loop ' do it again
End Sub

There are slicker ways to move through a range, but this is easy to follow
and debug if you haven't worked with VBA much.

Regards from Virginia Beach,

EarlK

 

[Bob Phillips]

Chip,

To remove embedded spaces, uses SUBSTITUTE
=SUBSTITUTE(A1," ","")

or as a macro

Sub TrimAll()
Dim cell As Range
For Each cell In Selection
If Not cell.HasFormula Then
cell.Value = Application.Substitute(cell, " ", "")
End If
Next
End Sub

 

[Michael Malson]

No need for a macro....First highlight the text that
contains the extra spaces...Go to Edit, replace...Then
type space, space (if 2 spaces in the text), then type 1
space in the other field. I would click replace once in
order to keep it from replacing things you may not want
replced.