Remove spaces from start and end of line in vba

Question

I wrote this function to remove spaces from the beginning and end of a line, any ideas why it doesn't work?

Public Function PrepareString(TextLine As String) Do While Left(TextLine, 1) = " " ' Delete any excess spaces TextLine = Right(TextLine, Len(TextLine) - 1) Loop Do While Right(TextLine, 1) = " " ' Delete any excess spaces TextLine = Left(TextLine, Len(TextLine) - 1) Loop PrepareString = TextLine End Function 

Answer 1

How about this one. Note the use of Worksheetfunction.Trim, which removes several spaces that Application.Trim does not.

 Option Explicit Dim oRegex As Object Sub test() Dim dirtyString As String dirtyString = " This*&(*&^% is_ The&^%&^%><><.,.,.,';';'; String " Debug.Print cleanStr(dirtyString) End Sub Function cleanStr(ByVal dirtyString As String) As String If oRegex Is Nothing Then Set oRegex = CreateObject("vbscript.regexp") With oRegex .Global = True 'Allow AZ, az, 0-9, a space and a hyphen - .Pattern = "[^A-Za-z0-9 -]" cleanStr = .Replace(dirtyString, vbNullString) End With cleanStr = WorksheetFunction.Trim(cleanStr) End Function 

Answer 2

I tested your function and it works fine on my machine.

You can use the built-in Trim() function, which does this for you, rather than creating a UDF that does the same.

 Trim(TextLine) 

Link: http://www.techonthenet.com/excel/formulas/trim.php

Answer 3

Why not this?

 Public Function PrepareString(TextLine As String) PrepareString = Trim(TextLine) End Function 

Alexandre / slaver113 is absolutely correct that it makes no sense to wrap a built-in function inside UDF. The reason why I did above is to indicate how to make UDF work. In a real life scenario, I would never use UDF that way. :)