如何删除 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”。
- 在开发人员选项卡,点击宏. (如果没有开发人员选项卡,点击大圆形Microsoft Office 按钮点击窗口左上角的Excel 选项,然后点击受欢迎的,然后点击在功能区中显示“开发工具”选项卡复选框)。
- 将会弹出一个对话框。在宏名称:文本框,命名你的宏;类似于删除第四个字将工作。
- 点击创造按钮。(如果创造按钮仍然被禁用,请从宏名称中删除空格和标点符号。)
在出现的窗口中,将以下内容粘贴在“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
关上微软 Visual Basic窗口并单击宏在开发人员标签。
- 选择刚刚创建的宏并单击选项...按钮。
- 在下面快捷键,选择用于此功能的快捷键(在此示例中我将使用“t”键)并在那里输入。
- 推好的按钮,然后关闭宏对话框。
- 选择要从中删除第四个单词的单元格,然后按快捷键(如 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 帮助文件中获取“开发人员”选项卡的说明。)