Delete blanks from start and end of a string in vba

Question

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

The [cell view addin](http://www.cpearson.com/excel/CellView.htm) from Pearson'site will help identify the problematic blank. I wouldn't be surprised if it is the `CHAR(160)` non-breaking-space character. — brettdj, Sep 30 '12 at 04:26

score 15 · Answer 1 · answered Sep 29 '12 at 13:24

15

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

answered Sep 29 '12 at 13:24

danielpiestrak

5,279
3
30
29

I think it might not be working because its not a blank character in front, maybe a tab or something. Is the a line of code that will delete anything but text characters? – Chris Sep 29 '12 at 13:46

score 4 · Accepted Answer · answered Sep 29 '12 at 14:24

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

Siddharth Rout · Answer 3 · 2013-12-18T13:01:30.683

1

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. :)

edited Dec 18 '13 at 13:01

answered Sep 29 '12 at 13:24

Siddharth Rout

147,039
17
206
250

I think it might not be working because its not a blank character in front, maybe a tab or something. Is the a line of code that will delete anything but text characters? – Chris Sep 29 '12 at 13:47
Are those non printable characters or the Square looking characters? `Is the a line of code that will delete anything but text characters? ` Do you want to delete Numeric values also? – Siddharth Rout Sep 29 '12 at 13:53
Why would you do such a thing ? Is there any advantage to wrapping a built-in function inside a UDF, passing the argument straight to the built-in function without any processing done at all before-hand ? This seems like completely useless to me. – ApplePie Sep 29 '12 at 16:07
I see that you have assumed that It will `only` be used as a UDF? – Siddharth Rout Sep 29 '12 at 16:32
@slaver113: Is that the reason why you downvoted me? – Siddharth Rout Dec 17 '13 at 18:05
@Siddharth Rout: Yes, because people, who have the same problem might copy and use your code as it is. It is overhead and bad readable style if you do that on every action. What was your intention behind? – Sebastian Viereck Dec 18 '13 at 08:45
1

@slaver113: Thanks for being honest. A rare trait now a days I must say :) I was answering keeping OP's format in mind. Point taken. Amending my post. – Siddharth Rout Dec 18 '13 at 12:58

Delete blanks from start and end of a string in vba

3 Answers3

Linked