vba 查找字符串中的最后一个空格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23393919/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find last space in string
提问by steventnorris
I want to find the last space in a string. The purpose of this is to split a name into it's three parts. I can get the first name easy enough, but the last two are not so simple.
我想找到字符串中的最后一个空格。这样做的目的是将一个名称分成三个部分。我可以很容易地获得名字，但后两个并不那么简单。
fullName = "Giger,San Paulo Fisher"
first = Left(fullName, InStr(1, ",", fullName, vbTextCompare))
last = Mid(fullName,InStr(1, ",", fullName, vbTextCompare),[POSITION OF LAST SPACE]-InStr(1, ",", fullName, vbTextCompare))
middle = Right(fullName,Len(fullName)-[POSITION OF LAST SPACE])
Thanks in advance!
提前致谢！
回答by APrough
You can use the Split function without VBA. This will get you "Giger,San","Paulo","and "Fisher". It should be relatively easy to split out the last name and first name by looking for the comma. And you don't have to worry about how many names are included or how long they are.
您可以在没有 VBA 的情况下使用拆分功能。这将得到“Giger,San”、“Paulo”和“Fisher”。通过查找逗号将姓氏和名字分开应该相对容易。而且你不必担心如何包括许多名称或它们的长度。
Dim tStr As String
tStr = "Giger,San Paulo Fisher"
Dim tArray() As String
tArray = Split(tStr, " ")
For i = 0 To UBound(tArray)
MsgBox tArray(i)
Next I
If you want just the index of the last space. Either of these will do. Note that the second one is using the code above to find the array of strings from the Split function.
如果你只想要最后一个空间的索引。这两个都行。请注意，第二个是使用上面的代码从 Split 函数中查找字符串数组。
MsgBox InStrRev(tStr, " ")
MsgBox InStr(1, tStr, tArray(UBound(tArray))) - 1
回答by Floris
There is a fairly well known trick for this (see for example Excel: last character/string match in a string- I adapted my answer from the last version of the one accepted there): you replace one space with lots of spaces, then take the last N characters and trim them:
对此有一个众所周知的技巧（例如，请参见Excel：字符串中的最后一个字符/字符串匹配- 我从那里接受的最后一个版本中改编了我的答案）：您用大量空格替换一个空格，然后取最后 N 个字符并修剪它们：
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))
You can make the number (99 in this case) as big as you need - longer than the longest word you envisage.
您可以根据需要使数字（在本例中为 99）尽可能大 - 比您想象的最长单词更长。
Here is how it works. Imagine your original string (name):
下面是它的工作原理。想象一下您的原始字符串（名称）：
Giger,San Paulo Fisher
Now replace every space with 8 spaces (keeping it manageable - formula uses 99, same principle):
现在用 8 个空格替换每个空格（使其易于管理 - 公式使用 99，原理相同）：
Giger,San Paulo Fisher
Now take the last 8 characters:
现在取最后 8 个字符：
__Fisher
(I am using underline to show the space)
（我使用下划线来显示空间）
Now trim it (remove leading and trailing spaces):
现在修剪它（删除前导和尾随空格）：
Fisher
which is the result you were after.
这就是你所追求的结果。
You can use other strings as well - but since you want to split on a space, it is the most natural string to use.
您也可以使用其他字符串 - 但由于您想在空格上拆分，因此它是最自然的字符串。
If you want to do this in VBA, you can just use
如果你想在 VBA 中做到这一点，你可以使用
lastSpace = InStrRev(fullName, " ")
lastName = Mid(fullName, lastSpace + 1)
By the way - beware of assuming that the thing after the last space is the whole last name. There are many names (more in some countries than others) that consist of multiple words separated by spaces.
顺便说一句 - 小心假设最后一个空格之后的东西是整个姓氏。有许多名称（在某些国家/地区比在其他国家/地区更多）由多个由空格分隔的单词组成。
回答by Jimit Rupani
its just you can use split command for to separate the name using space
它只是您可以使用 split 命令来使用空格分隔名称
For example: xyz = Split(tStr, " ")
例如： xyz = Split(tStr, " ")
