答案1
我在 DVD/蓝光电子表格中使用了一个公式,该公式允许我在排序时忽略某些字符和单词。它使用 SUBSTITUTE 函数删除我不想在排序时考虑的字符。公式如下所示:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"The ","",1),",","")," ",""),".",""),"&","and"),"":",""),"'","")
它用空字符替换“The”,用空字符替换逗号,用空字符替换空格,用空字符替换句号,用“and”替换“和”符号,用空字符替换冒号,用空字符替换引号。
(我还有另一个公式,用于删除标题中括号之间的所有内容,以包含简短注释,例如“one of boxed set”,但我认为没有人会感兴趣。)