vba 删除特定字符后单元格中的数据

delete data in cell after specific character

提问by sam

I have data in cells A1:A1000. It is a list of names followed by a small note, like this:

我在单元格 A1:A1000 中有数据。它是一个名称列表，后跟一个小注释，如下所示：

• sam" fast
• nick" long
• tom" quick

• 山姆”快
• 尼克”长
• 汤姆”快

They all have "and a space after the names and then the note. What I am trying to do is delete the everything after the name.

它们都"在名称后面有一个空格，然后是注释。我想要做的是删除名称后的所有内容。

I was playing around with macros to try and do this, but could not get anything to work. Any idea how I might do this?

我正在尝试使用宏来尝试执行此操作，但无法进行任何操作。知道我怎么做吗？

回答by ApplePie

Here is a nifty trick without macros:

这是一个没有宏的绝妙技巧：

Select the proper range (or even just click on Ato select the entire column) and then do Ctrl+F, click Replace, in Findwrite exactly "*and leave the Replace withbox empty. Now click Replace alland tada !

选择适当的范围（甚至只需单击A以选择整列），然后执行Ctrl+F、单击Replace、Find准确写入"*并将该Replace with框留空。现在点击Replace all和多田！

It replaces everything after (and including) the quote with nothing because it uses *as a wildcard you left the replace box empty.

它将引用之后（并包括）的所有内容*替换为空，因为它用作您将替换框留空的通配符。

Edit: As suggested here is the VBA code for this:

编辑：正如这里所建议的，这是用于此的 VBA 代码：

Columns("A:A").Replace What:="""*", Replacement:="", LookAt:=xlPart

回答by theJollySin

Easy!I don't know what version of Excel you are using, but in short you want to do a Convert Text to Columnsand then split the cells using a delimiter of ". This will leave you with two columns, one of the data you want and one you can just delete.

简单！我不知道您使用的是哪个版本的 Excel，但简而言之，您想要执行 aConvert Text to Columns然后使用". 这将为您留下两列，一列是您想要的数据，另一列是您可以删除的数据。

Here is the walk through in Office 2010:

以下是 Office 2010 中的演练：

1. Highlight column A
2. find the Datamenu
3. find the Convert Text to Columnsmenu
4. Pick Delimitedand hit next
5. In the Otherbox, type "
6. hit Finish

1. 突出显示 A 列
2. 找到Data菜单
3. 找到Convert Text to Columns菜单
4. 选择Delimited并击中next
5. 在Other框中，键入"
6. 打 Finish

Done!Now you have all your names in column A and you can just delete column B.

完毕！现在您在 A 列中拥有所有名称，您可以删除 B 列。

To sum up, do a "Convert Text to Columns" and then split the cells using a delimiter of ". Super easy and fast.

总而言之，执行“将文本转换为列”，然后使用". 超级简单快捷。

回答by InContext

few options:

几个选项：

Replace

代替

Range("A1:A1000").Replace """*", vbNullString

If you require to manipulate the value further then the below are more appropriate:

如果您需要进一步操纵该值，则以下更合适：

With Regex:

使用正则表达式：

Dim str As String, strClean As String
Dim cell As Range

For Each cell In Range("A1:A1000")

    With CreateObject("vbscript.regexp")
        .Pattern = "\""(.*)"
        .Global = True
        cell = .Replace(cell, vbNullString)
    End With

Next cell

Without Regex, splitting the string:

没有正则表达式，拆分字符串：

Dim strSplit() As String
Dim cell As Range

For Each cell In Range("A1:A1000")

    If (cell.Value <> vbNullString) Then

        cell.Value = Split(cell.Value, """")(0)

    End If

Next cell

回答by Peter Albert

In case you want to keep your source data, you can also do it with a simple Excel formula in the next column. Assuming that your data is in column A, the following formula will return only the name: =LEFT(A1,SEARCH("""",A1)-1)

如果您想保留源数据，也可以使用下一列中的简单 Excel 公式来完成。假设您的数据在 A 列中，以下公式将仅返回名称：=LEFT(A1,SEARCH("""",A1)-1)

回答by Ernie

Sub Macro1()
    For Row = 1 To 1000
       S = Range("A" & Row).Cells.Value
       Pos = InStr(S, Chr(34))
       If Pos > 0 Then Range("A" & Row).Cells.Value = Left(S, Pos - 1)
    Next
End Sub

回答by mohitgupta

Press ctrl + f, click on replace tab, type *in the find whatbox and then click on replace all. No need to put anything in replacebox. Here you are replacing everything after ..

按ctrl + f，单击replace tab，*在find what框中键入，然后单击replace all。不需要在replace盒子里放任何东西。在这里，您将在..