使所有行都使用相同的格式标准 [英] Get all rows to the same format standard

问题描述

我当前有一些行格式不正确.本质上，我以以下XXXX CHOC00X格式存储巧克力.因此，例如，在下面的列表中，赏金CHOC001有效.但是，任何不遵循这种格式的巧克力都是无效的.请参阅下面的列表:

I currently have some rows that are not formatted properly. Essentially, I am storing chocolates in the following format XXXX CHOC00X. So, for example, in the list below Bounty CHOC001 is valid. But then any chocolates that don't follow this format are invalid. Please see list below:

Chocolates
Bounty CHOC001
twIX CHOC002
snickers CHOC003
Mars choc004
kinder Bueno ch0c005
dairymilk cHOc006
wisPa choC007

您会看到诸如 twIX CHOC002 和 kinder Bueno ch0c005 之类的条目无效.我想要这样的东西:

As you can see entries like twIX CHOC002 and kinder Bueno ch0c005 are invalid. I want something like this:

Chocolates
Bounty CHOC001
Twix CHOC002
Snickers CHOC003
Mars CHOC004
Kinder Bueno CHOC005
Dairymilk CHOC006
Wispa CHOC007

我尝试使用此公式 PROPER()，但是随后它仅将每个单词都大写，然后 CHOC001 将改为 Choc001 .不确定是否可以采用与第一列相同的条件格式.

I tried using this formula PROPER() but then it only capitalises each word which is good by then CHOC001 would then instead be Choc001. Not sure if there is a way to apply the same conditional formatting of the first column.

谢谢

推荐答案

尝试

={"Chocolates"; ArrayFormula(regexreplace(proper(A2:A8); "(?i)Ch(o|0)c"; "CHOC"))}

更改范围以适合自己，看看是否有帮助?

Change range to suit and see if that helps?

这篇关于使所有行都使用相同的格式标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！