CODEWITHSUNDEEP
vbaexcel
Chris
Chris

Reputation: 337

Delete blanks from start and end of a string in vba

I've written this function to delete blanks from the start and the end of a string, any ideas why it isn't working?

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

Upvotes: 7

Views: 47907

Answers (3)

Siddharth Rout
Siddharth Rout

Reputation: 149315

Why not this?

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

Alexandre/slaver113 are absolutely correct that it doesn't make any sense to wrap a built-in function inside a UDF. The reason why I had done the above is to point out how to make your UDF work. In real life scenario, I would never use the UDF in such a way. :)

Upvotes: 1

user857521
user857521

Reputation:

How about this. Note the use of Worksheetfunction.Trim which removes multiple whitespaces which 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 A-Z, a-z, 0-9, a space and a hyphen -
        .Pattern = "[^A-Za-z0-9 -]"
        cleanStr = .Replace(dirtyString, vbNullString)
    End With
    cleanStr = WorksheetFunction.Trim(cleanStr)
End Function

Upvotes: 4

danielpiestrak
danielpiestrak

Reputation: 5439

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

You can use the built in Trim() function that does this for you instead of creating a UDF that does the same thing.

Trim(TextLine)

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

Upvotes: 16

Related Questions