excel - 如何在Excel VBA中删除单词末尾的空格？

Question

我有一个脚本，可以很好地基于两列创建文件夹，但由于某种原因，它的文件夹末尾有一个空格。

代码如下:

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

现在它陷入困境的是这个:

“The Richmond Group” 我认为是因为它的标题末尾有一个空格，而在创建文件夹时不会转换为空格。

现在我在 S 和 T 中的内容是这样的:

S

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

T

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

话虽如此，我无法找到删除名称末尾的空格的方法。

如果有人可以提供帮助，我们将不胜感激。

Answer 1

尝试修剪功能:

Dim LResult As String

LResult = Trim (" Alphabet ")

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