vba Trim help

[aldenny]

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]

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]

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

[VBA]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[/VBA]

[GTO]

Maybe:
[vba]
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
[/vba]

Hope that helps,

Mark

[Bob Phillips]

I shouldn't be doing this, but this is what I meant

[vba]

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
[/vba]

[Paul_Hossler]

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

[vba]
Option Explicit
Function MyTrim(s As String) As String
MyTrim = Application.WorksheetFunction.Trim(s)
End Function
[/vba]

Paul