I receive an xls with text cells containing various long series of space to create carraige returns.
Picked up this elegant code on this bb (forgot the link).
The function cleans out long spacebands from text.
I'd like help to add Alt+Enter chr to the code, so that....
Several items in this cell sbsbsbsb format A sbsbsbsb copy II sbsb design New
becomes....
Several items in this cell
format A
copy II
design New
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
' Split passed-in string.
astrInput = Split(strInput)
' Resize second array to be same size.
ReDim astrText(UBound(astrInput))
'MsgBox UBound(astrInput) '=152 characters
' Initialize counter variable for second array.
lngIncr = LBound(astrInput)
' Loop through split array, looking for
' non-zero-length strings.
SpaceFlag = 0
For lngCount = LBound(astrInput) To UBound(astrInput) '0 to 152
strElement = astrInput(lngCount)
If Len(strElement) > 0 Then
' Store in second array.
astrText(lngIncr) = strElement
lngIncr = lngIncr + 1
SpaceFlag = 0
End If
'MsgBox strElement 'every word or every space character
Next
'Msgbox strElement '=3 number of groups of words
' Resize new array.
ReDim Preserve astrText(LBound(astrText) To lngIncr - 1)
' Join new array to return string.
TrimSpace = Join(astrText)
End Function
Picked up this elegant code on this bb (forgot the link).
The function cleans out long spacebands from text.
I'd like help to add Alt+Enter chr to the code, so that....
Several items in this cell sbsbsbsb format A sbsbsbsb copy II sbsb design New
becomes....
Several items in this cell
format A
copy II
design New
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
' Split passed-in string.
astrInput = Split(strInput)
' Resize second array to be same size.
ReDim astrText(UBound(astrInput))
'MsgBox UBound(astrInput) '=152 characters
' Initialize counter variable for second array.
lngIncr = LBound(astrInput)
' Loop through split array, looking for
' non-zero-length strings.
SpaceFlag = 0
For lngCount = LBound(astrInput) To UBound(astrInput) '0 to 152
strElement = astrInput(lngCount)
If Len(strElement) > 0 Then
' Store in second array.
astrText(lngIncr) = strElement
lngIncr = lngIncr + 1
SpaceFlag = 0
End If
'MsgBox strElement 'every word or every space character
Next
'Msgbox strElement '=3 number of groups of words
' Resize new array.
ReDim Preserve astrText(LBound(astrText) To lngIncr - 1)
' Join new array to return string.
TrimSpace = Join(astrText)
End Function