vba 在 Excel 2010 中，如何使用宏删除单元格内的部分字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12845185/
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
In Excel 2010, how to delete a portion of a string within a cell using a Macro?
提问by user1738868
I am trying to create a macro for an Excel file which has a column (let's call this column A) of cells, and each cell contains 4 to 5 project Deliverables & Work Products separated by a comma, as well as identified by a tag "DEL" for Deliverable and "WKP" for Work Products.
我正在尝试为 Excel 文件创建一个宏，该文件有一列单元格（我们称之为 A 列），每个单元格包含 4 到 5 个项目交付成果和工作产品，以逗号分隔，并由标签标识“ DEL”代表可交付成果，“WKP”代表工作产品。
I wish to accomplish the following with a Macro:
我希望用宏完成以下操作：
- I would like to keep column A as it is.
- Create a column B for Deliverables, which need to be extracted from cells in Col A
- Likewise, create a column C for Work Products, which need to be extracted from cells in Col A
- 我想保持 A 列不变。
- 为交付物创建 B 列，需要从 A 列的单元格中提取
- 同样，为工作产品创建一个列 C，它需要从 A 列的单元格中提取
Let me give an example:
让我举个例子吧：
Supposing Cell A1 contains
假设单元格 A1 包含
DEL1: DEL 1 Project Process, DEL2: DEL 2 Project Schedule, WKP1: WKP 1 Work Product Document
Here's what I want Cell B1 to look like:
这是我希望单元格 B1 的样子：
DEL1: DEL 1 Project Process, DEL2: DEL 2 Project Schedule
And Cell C1 to look like this:
和单元格 C1 看起来像这样：
WKP1: WKP1 Work Product Document
And mind you, not all the cells in Col A will be the exact character length as Cell A1. But they will contain both DELs and WKPs.
请注意，并非 Col A 中的所有单元格都与单元格 A1 的字符长度完全相同。但它们将同时包含 DEL 和 WKP。
Given that the names of the Deliverables and Work Products vary in length, I cannot simply copy Col A and run TextToColumn in order to split it.
鉴于可交付成果和工作产品的名称长度不同，我不能简单地复制 Col A 并运行 TextToColumn 来拆分它。
So far, the approach I have come up with is to copy Col A to Col B, retain the Deliverables and try to remove Work Products.
到目前为止，我提出的方法是将 Col A 复制到 Col B，保留可交付成果并尝试删除工作产品。
As you can see, this entails deleting the part of the string that starts with the comma (",") and space (" ") in front of "WKP" and ends with the last character of the Work Product in question (which in this case would be the last character of the entire string itself since it seems that the WKPs are mentioned right after DELs).
如您所见，这需要删除字符串中以逗号 (",") 和空格 (" ") 开头并以相关工作产品的最后一个字符结尾的部分（在这种情况将是整个字符串本身的最后一个字符，因为似乎在 DEL 之后提到了 WKP）。
While I am able to write a Macro the can find the text "WKP" within each cell range and delete it, I am not able to delete the stuff that comes right after it. In other words, I am only able to produce this for Cell B1:
虽然我能够编写一个宏，可以在每个单元格范围内找到文本“WKP”并将其删除，但我无法删除紧随其后的内容。换句话说，我只能为 Cell B1 生成这个：
DEL1: DEL 1 Project Process, DEL2: DEL 2 Project Schedule, 1: 1 Work Product Document
Can someone help me write the Macro to do this? I would appreciate it if the syntax was also explained.
有人可以帮我写宏来做到这一点吗？如果还解释了语法，我将不胜感激。
Thanks!
谢谢！
回答by Scott Holtzman
If the data is laid out so that the DEL(deliverables) are always listed before the WKP(Work products), there is no need for VBA.
如果数据的布局使得DEL（可交付成果）始终列在WKP（工作产品）之前，则不需要 VBA。
Here is the non-vba solution:
这是非vba解决方案：
Assuming data is column A, place this formula in B1
假设数据是A列，把这个公式放在B1
=LEFT(A1,FIND(", WKP1",A1)-1)
And this in C1
这在 C1
=RIGHT(A1,LEN(A1)-FIND(", WKP1",A1)-1)
Then drag down.
然后往下拉。
回答by Jamie Bull
This should do the job. Call it using =WKP_DEL(A1,"DEL")in B1 and =WKP_DEL(A1,"WKP")in C1.
这应该可以完成工作。=WKP_DEL(A1,"DEL")在 B1 和=WKP_DEL(A1,"WKP")C1 中调用它。
Function WKP_DEL(str As String, DelOrWkp As String) As String
Dim V() As String
Dim SubStr As Variant
Dim WKPs As String
Dim DELs As String
WKP_DEL = ""
' Split the string into an array
V = Split(str, ",")
' Loop through the array adding WKPs to the WKPs string and DELs to the DELs string
For Each SubStr In V
If InStr(SubStr, "WKP") > 0 Then
WKPs = WKPs & Trim(SubStr) & ", "
End If
If InStr(SubStr, "DEL") > 0 Then
DELs = DELs & Trim(SubStr) & ", "
End If
Next
' Remove the trailing ", " and return the required string
If DelOrWkp = "DEL" Then WKP_DEL = Left(DELs, Len(DELs) - 2)
If DelOrWkp = "WKP" Then WKP_DEL = Left(WKPs, Len(WKPs) - 2)
End Function
