PDA

View Full Version : vba Trim help



aldenny
05-28-2010, 02:20 AM
hi guys

can someone help me with a function that i can use to replace the trim function. i'm working on a project that requires me to use all other excel functions except for the trim function and yet i need to remove both trailing and leading spaces from a cell.

all help will be extremely appreciated
thanks in advance

Bob Phillips
05-28-2010, 02:28 AM
Use a couple of Do loops, one starting at character 1 and loop until you find a non-space. Then another to work from the end (Len) backwards until you find a non-space. With these two numbers you can find the trimed data using Mid.

aldenny
05-28-2010, 02:48 AM
i've tried implementing this code but it's not working. i'm still fairly new at vba. i don't know if anyone can help correct it and make it work or spruce up something totally new altogether

Function mySplit(txt As String)
Dim i As Long, a As String, n As Long
a = txt

For i = 1 To Len(a)
If (Mid$(a, i, 1) <> " ") + (Mid$(a, i, 1) <> "") Then
n = n + 1: a(n) = Mid$(a, i, 1)
End If
Next
ReDim Preserve a(1 To n)
mySplit = a
End Function

GTO
05-28-2010, 03:38 AM
Maybe:

Function TrimText(ByVal Text As String) As String

Do While Left(Text, 1) = Chr(32)

Text = Right(Text, Len(Text) - 1)
Loop
Do While Right(Text, 1) = Chr(32)
Text = Left(Text, Len(Text) - 1)
Loop
TrimText = Text
End Function


Hope that helps,

Mark

Bob Phillips
05-28-2010, 03:43 AM
I shouldn't be doing this, but this is what I meant



Function mySplit(txt As String)
Dim StartChar As Long
Dim EndChar As Long
Dim i As Long

i = 1
Do While Mid$(txt, i, 1) = " "
i = i + 1
Loop
StartChar = i

i = Len(txt)
Do While Mid$(txt, i, 1) = " "
i = i - 1
Loop
EndChar = i

mySplit = Mid$(txt, StartChar, EndChar - StartChar + 1)
End Function

Paul_Hossler
05-28-2010, 07:33 AM
Wrapper maybe? You didn't say why Trim() couldn't be used, so if it's because of a conflict with something, this would 'hide' it


Option Explicit
Function MyTrim(s As String) As String
MyTrim = Application.WorksheetFunction.Trim(s)
End Function


Paul