Trim Words

The following macro codes are used to remove unnecessary empty spaces between the words while typing a text into a cell.
Function trimWords(expr1 as String)
Dim x() as String
Dim isFirst as boolean
x=split(expr1)
isFirst = True
For Each i In x
   If(len(trim(i))<>0) Then
      If(isFirst ) Then 
       expr1=i
       isFirst = False
     Else 
       expr1 = expr1 & space(1) & i
     End If
   End If
Next i
trimWords=expr1 
End Function

Sub Worksheet_Change(Target as Range)
Application.EnableEvents = false
On Error Goto ENEV
Target.value=trimWords(Target.value)
ENEV:
Application.EnableEvents = true
End Sub
 


    		 RSS of this page