CODEWITHSUNDEEP
excelvba
Matt Ridge
Matt Ridge

Reputation: 3651

How to remove a space at the end of word in Excel VBA?

I have a script that works well to create folders based off of two columns, but for some reason it's crapping out with a folder that has a space at the end of it.

Here is the code: 

Dim baseFolder As String, newFolder As String
    lastrow = wsJL.Cells(Rows.Count, "B").End(xlUp).Row
    wsJL.Range("S2:U2").Copy wsJL.Range("S3:U" & lastrow)
    Range("J3:M" & lastrow).Calculate
    Range("S3:U" & lastrow).Calculate
    baseFolder = wbBK1.path & Application.PathSeparator & "Photos" & Application.PathSeparator
     'folders will be created within this folder - Change to sheet of your like.

    If Right(baseFolder, 1) <> Application.PathSeparator Then _
     baseFolder = baseFolder & Application.PathSeparator

       For Each cell In Range("S3:S" & lastrow)   'CHANGE TO SUIT

           'Company folder - column S

           newFolder = baseFolder & cell.Value
           If Len(Dir(newFolder, vbDirectory)) = 0 Then MkDir newFolder

           'Part number subfolder - column T

           newFolder = newFolder & Application.PathSeparator & cell.Offset(0, 1).Value
           If Len(Dir(newFolder, vbDirectory)) = 0 Then MkDir newFolder

       Next

        End With

Now where it's getting caught up is at this one:

"The Richmond Group " the reason I think is because it has a space at the end of the title, and that doesn't translate to a space when creating a folder.

Now I have in S and T is this:

S

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($C2,",","")," "," "),".",""),"/","-"),"""",""),"*","")

T

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($D2,",","")," "," "),".",""),"/","-"),"""",""),"*","")

Now with that being said, I can't figure out a way to remove the end space at the end of the name.

If someone can help there it be greatly apprecaited.

Upvotes: 2

Views: 4050

Answers (2)

brettdj
brettdj

Reputation: 55702

Use Trim$.

It is a string function which means it is more efficient than it's variant cousin Trim

A very useful reference No variants please

Upvotes: 3

Richard Morgan
Richard Morgan

Reputation: 7691

Try the Trim function:

Dim LResult As String

LResult = Trim (" Alphabet ")

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

Upvotes: 3

Related Questions