vba 使用 Excel 进行字符串操作 - 如果存在另一部分，如何删除部分字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/263225/
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
String manipulation with Excel - how to remove part of a string if another part is there?
提问by warren
I've done some Googling, and can't find anything, though maybe I'm just looking in the wrong places. I'm also not very adept at VBA, but I'm sure I can figure it out with the right pointers :)
我已经做了一些谷歌搜索，但找不到任何东西，尽管也许我只是在错误的地方寻找。我也不太擅长 VBA，但我相信我可以用正确的指针弄清楚:)
I have a string I'm building that's a concatenation of various cells, based on various conditions. I hit these in order.
我有一个正在构建的字符串，它是基于各种条件的各种单元格的串联。我按顺序打了这些。
=IF(A405<>A404,G405,G405&H404)
What I want to do is go back through my concatenated list, removing a superseded value if the superseder is in the list.
我想要做的是返回我的连接列表，如果被取代的人在列表中，则删除被取代的值。
For example, see the following list:
例如，请参阅以下列表：
A, D, G, Y, Z
I want to remove Difand onlyifYis present.
我想删除D当且仅当Y存在时。
How would I go about this? (VBA or in-cell, though I'd prefer in-cell)
我该怎么办？（VBA 或 in-cell，虽然我更喜欢 in-cell）
采纳答案by warren
I just got this as a possible solution via email, too:
我也刚刚通过电子邮件将此作为可能的解决方案：
=IF(A15<>A14,G15,IF(OR(AND(G15="CR247, ",ISNUMBER(FIND("CR247, ",H14))),AND(G15="CR149, ",ISNUMBER(FIND("CR215, ",H14))),AND(G15="CR149, ",ISNUMBER(FIND("CR180, ",H14))),AND(G15="CR180, ",ISNUMBER(FIND("CR215, ",H14))),G15="CR113, "),H14,G15&H14))
(this has the "real" values with precedence rules)
（这具有具有优先规则的“真实”值）
It looks relatively similar to @Joseph's answer.
它看起来与@Joseph的答案相对相似。
Is there a better solution?
有更好的解决方案吗？
回答by Joseph Bui
Try:
尝试：
=IF(ISERROR(FIND("Y",A1)),A1,SUBSTITUTE(A1,"D, ",""))
But that assumes you always have the comma and space following the D.
但这假设您总是在 D 后面有逗号和空格。
回答by BradC
Firstly, why not keep a string array instead as you go through all the cells, then concatenate it all at the end?
首先，为什么不在遍历所有单元格时保留一个字符串数组，然后在最后将它们连接起来？
Otherwise, you'll be using string functions like INSTR and MID to do something like:
否则，您将使用 INSTR 和 MID 之类的字符串函数来执行以下操作：
start1 = instr(myLongString,"Y, ")
if start1 > 0 Then
start2 = instr(myLongString,"D, ")
if start2 > 0 then
newLongString = left(myLongString, start2 - 1) & _
mid(myLongString, start2 + 3)
end if
end if
But, as I said, I would keep an array that is easy to loop through, then once you have all the values you KNOW you will use, just concatenate them at the end.
但是，正如我所说，我会保留一个易于循环的数组，然后一旦您拥有了将要使用的所有值，只需在最后将它们连接起来即可。
回答by da_m_n
VBA: You can always use the regexp object. I think that gives you the ability to test anything on your script as long as you build correctly the regular expression.
VBA：您始终可以使用正则表达式对象。我认为只要正确构建正则表达式，您就可以测试脚本上的任何内容。
Check out : http://msdn.microsoft.com/en-us/library/yab2dx62(VS.85).aspx( for regexp reference )
and a simple tool to test your regexps : http://www.codehouse.com/webmaster_tools/regex/
查看：http: //msdn.microsoft.com/en-us/library/yab2dx62(VS.85) .aspx（用于正则表达式参考）
和一个测试正则表达式的简单工具：http: //www.codehouse.com /webmaster_tools/regex/
In-cell: you could do it in a more excel friendly way:
suppose on column A:A you have the values.
You can add a new column where you perform the checkif(indirect("A"&row()) <> indirect("A"&row()-1), indirect("G"&row()), indirect("G"&row())& indirect("H"&row()))
or whatever the values are. I guess however that on one branch of the if statement the value should be blank. After that you concatenate only the B:B column values ( skipping blanks if needed ).
单元格内：您可以以更 excel 友好的方式进行操作：
假设在 A:A 列上您有值。
您可以添加一个新列，您可以在其中执行检查if(indirect("A"&row()) <> indirect("A"&row()-1), indirect("G"&row()), indirect("G"&row())& indirect("H"&row()))
或任何值。然而，我想在 if 语句的一个分支上，该值应该为空。之后，您只连接 B:B 列值（如果需要，跳过空格）。
Hope this helps.
希望这可以帮助。
回答by bjorsig
If there are not too many of these combinations that you want to remove, you can use =IF(FIND("D"; A2)> 0; REPLACE(A2;1;3;"");A2).
如果要删除的这些组合不多，则可以使用 =IF(FIND("D"; A2)> 0; REPLACE(A2;1;3;"");A2)。
回答by Fionnuala
I guess D could appear anywhere, so how about:
我猜 D 可能出现在任何地方，那么怎么样：
If InStr(strString, "Y") > 0 Then
strString = Replace(strString, "d", "")
strString = Replace(strString, " ", "")
strString = Replace(strString, " ,", "")
strString = Replace(strString, ",,", ",")
End If
回答by Andru Luvisi
It's probably easier to start at the end, make your additions to the beginning of the string, and only add D if Y is not present.
从结尾开始，在字符串的开头添加内容可能更容易，如果 Y 不存在，则仅添加 D。
