在 Excel 中更新由 & 符号分隔的分组名称
基本上，我想做的是我有这个Excel公式：
=IF(ISNUMBER(FIND(",",A1,FIND(",",A1,FIND("&",A1,FIND("&",A1))))+1),TRIM(MID(A1,FIND("&",A1,1)+1,FIND("&",A1,FIND("&",A1,1)+1)-FIND("&",A1,1)-1)),IF(ISNUMBER(FIND(",",A1,FIND(",",A1)+1)),TRIM(MID(A1,FIND("&",A1)+1,FIND("&",A1)-3)),IF(ISNUMBER(FIND("&",A1,FIND("&",A1)+1)),CONCATENATE(LEFT(A1,FIND(",",A1)-1),",",TRIM(MID(A1,FIND("&",A1,1)+1,FIND("&",A1,FIND("&",A1,1)+1)-FIND("&",A1,1)-1))),IF(ISNUMBER(FIND("&", A1)),SUBSTITUTE(A1,MID(A1,FIND(",", A1)+1,FIND(" &", A1)-FIND(",", A1)+2), ""),A1))))
如果你注意到，这部分：
=IF(ISNUMBER(FIND(",",A1,FIND(",",A1,FIND("&",A1,FIND("&",A1))))+1)
我试图说如果单元格包含两个逗号和两个＆符号，那么做一些事情。否则，我指定它做其他事情。不幸的是，这种情况的表现并不如预期。尽管我的单元格带有两个逗号且只有一个 & 符号：
CORNWALL,A ROBERT & CORNWALL,ANNA
它会将其视为 true，并将后续 true 语句应用于上面的单元格，因此会导致 VALUE 错误，因为后续 true 语句不考虑上面的单元格。它仅考虑带有两个逗号和两个与号的单元格。我已经为上面的单元格设置了另一个 ISNUMBER 条件，正如您在我的初始公式中看到的那样。
有没有办法修复我的 ISNUMBER 条件，以便在第一级执行中，它检查单元格中的两个逗号和两个与号？
感谢您的回复。
Basically, what I'm trying to do is I have this excel formula:
=IF(ISNUMBER(FIND(",",A1,FIND(",",A1,FIND("&",A1,FIND("&",A1))))+1),TRIM(MID(A1,FIND("&",A1,1)+1,FIND("&",A1,FIND("&",A1,1)+1)-FIND("&",A1,1)-1)),IF(ISNUMBER(FIND(",",A1,FIND(",",A1)+1)),TRIM(MID(A1,FIND("&",A1)+1,FIND("&",A1)-3)),IF(ISNUMBER(FIND("&",A1,FIND("&",A1)+1)),CONCATENATE(LEFT(A1,FIND(",",A1)-1),",",TRIM(MID(A1,FIND("&",A1,1)+1,FIND("&",A1,FIND("&",A1,1)+1)-FIND("&",A1,1)-1))),IF(ISNUMBER(FIND("&", A1)),SUBSTITUTE(A1,MID(A1,FIND(",", A1)+1,FIND(" &", A1)-FIND(",", A1)+2), ""),A1))))
if you notice, this part of it:
=IF(ISNUMBER(FIND(",",A1,FIND(",",A1,FIND("&",A1,FIND("&",A1))))+1)
I'm trying to say if the cell contains two commas and two ampersands, then do something. Otherwise, I specify it to do something else. Unfortunately, this condition doesn't behave as expected. Although I have cells with two commas and only a single ampersand:
CORNWALL,A ROBERT & CORNWALL,ANNA
it treats this as true, and applies the subsequent true statement to the above cell and therefore causes a VALUE error because the subsequent true statement doesn't account for the cell above. It only accounts for cells with two commas and two ampersands. I already have another ISNUMBER condition for the cell above, as you can see in my initial formula.
Is there a way to fix my ISNUMBER condition so in the first level of execution, it checks for both two commas and two ampersands in the cell?
Thanks for response.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我不知道我是否能看出您的代码有什么问题，但是您是否尝试过不同的方法来计算与号和逗号的数量？
这看起来简单了一些。
I don't know if I can see what's wrong with your code there, but have you tried a different approach to counting the number of ampersands and commas?
This just seems a little simpler.