Excel VBA：字符串操作

Question

以下给出了名字、空格和姓氏首字母。我希望名字首字母和姓氏之间没有空格。

Sub FNLI() 

 Dim cell 

 For Each cell In Selection 
 cell.Offset(0, 1) = Mid$(cell.Value2, 1, InStr(cell.Value2, Chr(32)) + 1) 
 Next 

End Sub 

以下是它目前所做的工作 -

Data Before:      
Margaret Hicks

After Script:
Margaret H

What I need:
MHicks

Answer 1

尝试

For Each cell In Selection 
cell.Offset(0, 1) = trim(left(cell.Value2,1)&Mid(cell.Value2, InStr(cell.Value2, Char(32)) + 1,len(cell.Value2)-InStr(cell.Value2,Char(32)))) 
Next 

尝试 #2

cell.Offset(0, 1) = trim(left(cell.Value2,1)&Mid(cell.Value2, search(char(32),cell.Value2) + 1,len(cell.Value2)-search(Char(32),cell.Value2)))

Answer 2

正确的参数是“Chr”而不是“Char”

以下是完整编译并运行的子程序：

子 FNLI()

暗淡的细胞

对于选择中的每个单元格 cell.Offset(0, 1) = Trim(Left(cell.Value2, 1) & Mid(cell.Value2, InStr(cell.Value2, Chr(32)) + 1, Len(cell.Value2) - InStr(cell.Value2, Chr(32)))) 下一步

子目录结束