Excel - 删除每列中的第四个单词

Excel - 删除每列中的第四个单词

如何删除 Excel 电子表格中每列的第四个单词?例如,我有“史密斯先生和夫人”。我想保留史密斯先生和夫人,但删除史密斯。有没有办法找到并删除第四个单词?非常感谢您的帮助...

答案1

这是一种解决方法,但如果您只是处理常规文本(而不是公式),它就会起作用。

假设包含数据的表格名为“Sheet1”。在电子表格上创建一个新工作表(假设名为“Sheet2”),然后在 A1 中输入:

=IFERROR(LEFT(Sheet1!A1,FIND(" ",Sheet1!A1,FIND(" ",Sheet1!A1,FIND(" ",Sheet1!A1)+1)+1)-1),"")

如果你想删除仅有的第四个单词,输入:

=IFERROR(LEFT(Sheet1!A1,FIND(" ",Sheet1!A1,FIND(" ",Sheet1!A1,FIND(" ",Sheet1!A1)+1)+1))&RIGHT(Sheet1!A1,LEN(Sheet1!A1)-FIND(" ",Sheet1!A1,FIND(" ",Sheet1!A1,FIND(" ",Sheet1!A1,FIND(" ",Sheet1!A1)+1)+1)+1)),IFERROR(LEFT(Sheet1!A1,FIND(" ",Sheet1!A1,FIND(" ",Sheet1!A1,FIND(" ",Sheet1!A1)+1)+1)-1),""))

复制单元格 A1,然后将其粘贴到 Sheet2 上与 Sheet1 上包含您要处理的数据的单元格相匹配的单元格中。(因此,如果您在 Sheet1 的单元格 A1 到 D5 中有数据,请将其粘贴到 Sheet2 的单元格 A1 到 D5 中。)复制您粘贴的所有单元格,切换到 Sheet1,然后粘贴值在匹配的位置。现在您可以删除 Sheet2。

答案2

这可能是一个不切实际的想法,但请尝试将文件变成空格分隔的文件,然后使用 excel 打开它。现在删除第 4 列。从纸面上看,这听起来不错……除此以外我就不知道了。

答案3

如果您可以粘贴 Visual Basic 宏,那么这里有一种更优雅/更有效的方法:

假设包含数据的表格名为“Sheet1”。

  1. 开发人员选项卡,点击. (如果没有开发人员选项卡,点击大圆形Microsoft Office 按钮点击窗口左上角的Excel 选项,然后点击受欢迎的,然后点击在功能区中显示“开发工具”选项卡复选框)。
  2. 将会弹出一个对话框。在宏名称:文本框,命名你的宏;类似于删除第四个字将工作。
  3. 点击创造按钮。(如果创造按钮仍然被禁用,请从宏名称中删除空格和标点符号。)
  4. 在出现的窗口中,将以下内容粘贴在“Sub DeleteFourthWord()”和“End Sub”之间的空格中:

     For Each c In ActiveCell.CurrentRegion.Cells
            If c.HasFormula = False Then
                Original_Cell_Text = c.Value
                Text_To_Parse = Original_Cell_Text
                Word1 = Left(Text_To_Parse, InStr(1, Text_To_Parse, " ", vbTextCompare))
                If Len(Word1) = 0 Then
                    Word1 = Text_To_Parse
                    Text_To_Parse = ""
                Else
                    Text_To_Parse = Right(Text_To_Parse, Len(Text_To_Parse) - Len(Word1))
                End If
                Word2 = Left(Text_To_Parse, InStr(1, Text_To_Parse, " ", vbTextCompare))
                If Len(Word2) = 0 Then
                    Word2 = Text_To_Parse
                    Text_To_Parse = ""
                Else
                    Text_To_Parse = Right(Text_To_Parse, Len(Text_To_Parse) - Len(Word2))
                End If
                Word3 = Left(Text_To_Parse, InStr(1, Text_To_Parse, " ", vbTextCompare))
                If Len(Word3) = 0 Then
                    Word3 = Text_To_Parse
                    Text_To_Parse = ""
                Else
                    Text_To_Parse = Right(Text_To_Parse, Len(Text_To_Parse) - Len(Word3))
                End If
                Word4 = Left(Text_To_Parse, InStr(1, Text_To_Parse, " ", vbTextCompare))
                If Len(Word4) = 0 Then
                    Word4 = Text_To_Parse
                    Text_To_Parse = ""
                Else
                    Text_To_Parse = Right(Text_To_Parse, Len(Text_To_Parse) - Len(Word4))
                End If
                Remaining_Text = Text_To_Parse
                If (Len(Word1) > 0 And Len(Word2) > 0 And Len(Word3) > 0 And Len(Word4) > 0) Then
                    If Len(Remaining_Text) > 0 Then
                        c.Value = Word1 + Word2 + Word3 + Remaining_Text
                    Else
                        c.Value = Word1 + Word2 + Word3
                    End If
                End If
            End If
        Next
    
  5. 关上微软 Visual Basic窗口并单击开发人员标签。

  6. 选择刚刚创建的宏并单击选项...按钮。
  7. 在下面快捷键,选择用于此功能的快捷键(在此示例中我将使用“t”键)并在那里输入。
  8. 好的按钮,然后关闭对话框。
  9. 选择要从中删除第四个单词的单元格,然后按快捷键(如 Ctrl+t)。您可以根据需要重复此步骤多次。

笔记:如果要删除第三个单词后的所有内容,请粘贴此内容:

For Each c In ActiveCell.CurrentRegion.Cells
    If c.HasFormula = False Then
        Original_Cell_Text = c.Value
        Text_To_Parse = Original_Cell_Text
        Word1 = Left(Text_To_Parse, InStr(1, Text_To_Parse, " ", vbTextCompare))
        If Len(Word1) = 0 Then
            Word1 = Text_To_Parse
            Text_To_Parse = ""
        Else
            Text_To_Parse = Right(Text_To_Parse, Len(Text_To_Parse) - Len(Word1))
        End If
        Word2 = Left(Text_To_Parse, InStr(1, Text_To_Parse, " ", vbTextCompare))
        If Len(Word2) = 0 Then
            Word2 = Text_To_Parse
            Text_To_Parse = ""
        Else
            Text_To_Parse = Right(Text_To_Parse, Len(Text_To_Parse) - Len(Word2))
        End If
        Word3 = Left(Text_To_Parse, InStr(1, Text_To_Parse, " ", vbTextCompare))
        If Len(Word3) = 0 Then
            Word3 = Text_To_Parse
            Text_To_Parse = ""
        Else
            Text_To_Parse = Right(Text_To_Parse, Len(Text_To_Parse) - Len(Word3))
        End If
        Remaining_Text = Text_To_Parse
        If (Len(Word1) > 0 And Len(Word2) > 0 And Len(Word3) > 0) Then
            c.Value = Word1 + Word2 + Word3
        End If
    End If
Next

(来源注:我修改了有关如何从 Excel 帮助文件中获取“开发人员”选项卡的说明。)

相关内容