在Excel 2010中删除某些字符 [英] Delete Certain Characters in Excel 2010

问题描述

我在Excel 2010中有〜4000个数据记录如下：

I have ~4000 records of data in Excel 2010 that as below:

QW3-WE-2232-322-A-2
WETE-33-3REE-33-WEWE-3
DEE-3YED-3432-DXSDDD-3FESE-2
343-34SE-34323-AWBE-3
ABDC-343-3THYT-2

我希望数据看起来像这样，删除。

I want the data to look like this where everything after the last dash is deleted.

QW3-WE-2232-322-A
WETE-33-3REE-33-WEWE
DEE-3YED-3432-DXSDDD-3FESE
343-34SE-34323-AWBE
ABDC-343-3THYT

想知道是否有简单的方法来做这个Excel？

Was wondering if there was an easy way to do this Excel?

推荐答案

最初有两个 - 分隔符的原始问题：

Answering the original question where at most two "-" separators occur:

=LEFT(A1,FIND("-",A1,FIND("-",A1)+1)-1)

回答修改后的问题，发生 - 分隔符：

Answering the modified question where an arbitrary number of "-" separators occur:

=LEFT(A1,FIND("|",SUBSTITUTE(A1,"-","|",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))-1)

这个答案是通过如何在Excel中执行反向字符串搜索而不使用VBA？，其中还可以找到几个适应性的选项。

This answer is via How can I perform a reverse string search in Excel without using VBA?, where several adaptable options may be found as well.

这是非常聪明的，但有一点VBA可以设计出一个更直接的答案。 VBA要做到这一点可以很简单，但是我建议引入正则表达式以获得更灵活的解决方案，您可以使用其他类似的问题。

This is very clever, but with a little VBA a more straightforward answer could be devised. The VBA to do this can be very simple, but I recommend bringing in Regular Expressions for more flexible solution you can use on other similar problems.

Function RegExReplace(pattern As String, sourceString As String, replaceVar As String) As String
    Dim RE As Object
    Set RE = CreateObject("vbscript.regexp")
    RE.pattern = pattern
    RE.Global = True
    RegExReplace = RE.Replace(sourceString, replaceVar)
End Function

然后在您的工作表中，您将使用

Then in your worksheet you would use

=RegExReplace("-[^-]*$",A1,"")

这篇关于在Excel 2010中删除某些字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！