string 在 Excel 中截断字符串 - 是否有删除分隔符后字符串最后一部分的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/713752/
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
Truncate strings in Excel - is there a function to remove last part of string after separator
提问by Brummo
Is there a smart macro in Excel (2000 upwards) to remove the last part of a string, if it has a certain separator?
Excel（2000以上）中是否有智能宏来删除字符串的最后一部分，如果它有一定的分隔符？
If the separator is not there, the string should be preserved
如果分隔符不存在，则应保留字符串
For instance, for "."
例如，对于“。”
abcd.1 => abcd
abcd => abcd
I guess I could write something using a combination of Instr and Mid and such, but I am curious to know whether there is a smarter shorthand for it, like "Strip('abcd.1';'.')" or so.
我想我可以使用 Instr 和 Mid 之类的组合来写一些东西，但我很想知道是否有更智能的速记，比如“Strip('abcd.1';'.')”等等。
回答by Chris
I do not believe there is any function that will do this for you.
我不相信有任何功能可以为您做到这一点。
You can probably use something like this:
你可能可以使用这样的东西：
=LEFT(A1,IF(ISERROR(SEARCH(".",A1)),LEN(A1),SEARCH(".",A1) - 1))
This will remove all characters after the first instance of the seperator, if you want to remove from the last instance you may need to write your own function to do a search on the reversed string.
这将删除分隔符的第一个实例之后的所有字符，如果您想从最后一个实例中删除，您可能需要编写自己的函数来对反向字符串进行搜索。
A full list of available functions can be found at:
可用功能的完整列表可在以下位置找到：
回答by bendewey
You can also create you own User Defined Function (UDF) for this.
您还可以为此创建自己的用户定义函数 (UDF)。
Function Strip(ByVal oCell As Range, ByVal sSeperator As String) As String
Dim iPos As Integer
Dim sValue As String
sValue = oCell.Value
iPos = InStr(sValue, sSeperator)
If (iPos <= 0) Then
Strip = sValue
Else
Strip = Left(sValue, iPos - 1)
End If
End Function
Then you can call that in your formula bar using
然后你可以在你的公式栏中调用它
=Strip(A1, ".")
=Strip(A1, ".")
回答by Sam
You could alternately use the Data column, click on the "text to column option", select "delimited", hit next and select the symbol of your choice to split the data(in your case, '.'). Your data should now be split in two columns. Delete the second column :)
您可以交替使用数据列，单击“文本到列选项”，选择“分隔”，点击下一步并选择您选择的符号来拆分数据（在您的情况下，'.'）。您的数据现在应该分成两列。删除第二列:)
回答by John Smith
'Use SPLIT in a function
'在函数中使用 SPLIT
Public Function extractName(parmField As String, Optional parmSplitChar As String = ".") As String
Public Function extractName(parmField As String, Optional parmSplitChar As String = ".") As String
Dim vSplit As Variant
vSplit = Split(parmField, parmSplitChar)
extractName = vSplit(0)
End Function
结束函数
回答by Ludwig S
This works for the German version of Excel.
这适用于德语版的 Excel。
=LINKS(A1;WENN(ISTFEHLER(SUCHEN(".";A1));L?NGE(A1);SUCHEN(".";A1) - 1))
