vba 如何删除单元格中第一个空格后的所有字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5833137/
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
How do I delete all characters after the first space in a cell?
提问by dzilla
I have a list of city names followed by the state in which they are located all in one column in Excel. How can I delete everything after the first space so that the city name is the only thing that's left in the cell?
我有一个城市名称列表，后跟它们在 Excel 中的一列中所在的州。如何删除第一个空格之后的所有内容，以便城市名称是单元格中唯一剩下的内容？
example: A1 = "johnson TX"
should be just A1= "johnson"
例如：A1 = "johnson TX"
应该只是 A1 = "johnson"
回答by Jean-Fran?ois Corbett
I assume you want a VBA solution since you tagged your question excel-vba.
我假设您想要一个 VBA 解决方案，因为您将问题标记为excel-vba。
This works:
这有效：
Sub KeepCity()
Dim strCityAndState As String
Dim strCityOnly As String
strCityAndState = Range("A1").Value
strCityOnly = Left(strCityAndState, InStr(strCityAndState, " ") - 1)
Range("A2").Value = strCityOnly
End Sub
If you don't want VBA and want a cell formula instead, then @JToland's answer works fine, though this one is more concise and doesn't keep the trailing space character:
如果您不想要 VBA 而是想要一个单元格公式，那么@JToland 的答案就可以正常工作，尽管这个答案更简洁并且不保留尾随空格字符：
=LEFT(A1, FIND(" ",A1)-1)
回答by JToland
Well doing something like this
做这样的事情
=Mid(A1, 1, Find(" ",A1))
in another column should grab all text beforethe " ". So you could build another column with just all the city names and then you could use that column for your purposes.
在另一列中应该抓取“”之前的所有文本。因此，您可以使用所有城市名称构建另一个列，然后您可以将该列用于您的目的。
回答by Pavan Chinta
If you are looking for a VBA function, you can use Leftand InStras shown below.
如果您正在寻找 VBA 函数，您可以使用Left和InStr如下所示。
Dim Temp As String: Temp = "Hello_World! This is my first answer here. :D"
Temp = Left(Temp, InStr(Temp, " ")-1)
In which case, Tempwill be "Hello_World!"
在这种情况下，Temp将"Hello_World!"
回答by T.M.
Use of Splitfunction
Split函数的使用
An elegant approach is to use the first token of the Splitfunction:
一种优雅的方法是使用Split函数的第一个标记：
Code Example extracting from cell A1 to A2
从单元格 A1 到 A2 提取的代码示例
Option Explicit
Sub KeepCity()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("MySheet") ' << Change to your sheet name
ws.[A2] = Split(ws.[A1], " ")(0)
End Sub
Alternative Syntax
替代语法
Instead of cell abbreviations [A2]or [A1]you can also use:
除了单元格缩写[A2]，[A1]您也可以使用：
ws.Range("A2").Value = Split(ws.Range("A1").Value, " ")(0)
Note
笔记
The resulting split 1-dimensional array is zero based, so you get the first part (or token) of the original string via index (0).
生成的拆分一维数组是基于零的，因此您可以通过 index 获得原始字符串的第一部分（或标记）(0)。
If you are looking for a second part, I recommend adding an additional delimiter value (" ") to the original string, e.g. s: MsgBox split(s & " "," ")(1). By that way you avoid error number 9 "Subscript out of range", if there is no delimiter in between at all, thus no second part at all.
如果您正在寻找第二部分，我建议在原始字符串中添加一个额外的分隔符值 (" ")，例如s: MsgBox split(s & " "," ")(1)。通过这种方式，您可以避免错误编号 9 "Subscript out of range"，如果两者之间根本没有分隔符，则根本没有第二部分。
