How to remove spaces from a word in a cell

J

Jerry

I want to do it automatically for a group of cells.

for example, if the word in a cell is "a b c", after spaces are
removed, the new word in the cell will be "abc"

how to do it by using VBA? thanks!
 
Y

Yacbo

Anything wrong with the Substitute function for this cause?

=SUBSTITUTE(A1," ","") where A1 is the cell location of the text.

"a b c" becomes "abc".
 
T

T. Valko

Try this macro:

Sub RemSpace()

Dim cell As Range
For Each cell In Selection
Selection.Replace What:=" ", Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

Next cell

End Sub

Select the range of cells in question then run the macro.

You really don't need a macro to do this. You can do the same by following
these steps:

Select the range of cells in question
Goto the menu Edit>Replace
Find what: enter a space in the box by hitting your space bar
Replace with: nothing, leave this empty
Replace all
Close

Biff
 
L

Lori

It's easy to get the VBA code by using Tools > Macro > Record New Macro
and then using the Edit>Replace method.

BTW The "for each cell" loop is redundant in the code above.
 
D

Dave Peterson

Another option would be to just edit|Replace the space character with nothing:

In code:

Option Explicit
Sub testme01()
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

Select the range to fix first.
 
T

T. Valko

BTW The "for each cell" loop is redundant in the code above.

That's why I almost never post code! Good pointer, though.

Biff
 
Top