Hi all member!

I found this function to trim 2 spaces not neccessary in the string :

Example: TrimSpace(" This is new user ") ="This is new user"

How can i use this function to apply to the whole worksheet?



Function TrimSpace(strInput As String) As String
' This procedure trims extra space from any part of a string

Dim astrInput() As String
Dim astrText() As String
Dim strElement As String
Dim lngCount As Long
Dim lngIncr As Long
If Trim(strInput) = "" Then Exit Function
' Split passed-in string.
astrInput = Split(Trim(strInput))

' Resize second array to be same size.
ReDim astrText(UBound(astrInput))

' Initialize counter variable for second array.

lngIncr = LBound(astrInput)
' Loop through split array, looking for
' non-zero-length strings.
For lngCount = LBound(astrInput) To UBound(astrInput)
strElement = astrInput(lngCount)
If Len(strElement) 0 Then
' Store in second array.
astrText(lngIncr) = strElement
lngIncr = lngIncr + 1
End If
Next
' Resize new array.
ReDim Preserve astrText(LBound(astrText) To lngIncr - 1)

' Join new array to return string.
TrimSpace = Join(astrText)
End Functio

--
thanhnguye
-----------------------------------------------------------------------
thanhnguyen's Profile: http://www.excelforum.com/member.php...fo&userid=3050
View this thread: http://www.excelforum.com/showthread.php?threadid=50154