如何统一表格巧克力条目格式,避免PROPER误改全大写CHOC编码
巧克力条目格式批量统一方法
核心逻辑是拆分字段、分别格式化、拼接还原,完全规避PROPER()函数直接作用整段文本时误改编码的问题,根据日常使用的工具场景,对应实现方案如下:
通用公式实现(适配Excel 365/WPS)
利用「最后一个空格是名称和编码的固定分隔点」的规则(巧克力名称可包含多个空格,但编码永远在条目最末尾),用单条公式就能完成全部处理:
=LET( 原始内容, A2, 分隔位置, FIND("#", SUBSTITUTE(原始内容, " ", "#", LEN(原始内容)-LEN(SUBSTITUTE(原始内容, " ", "")))), 名称部分, LEFT(原始内容, 分隔位置-1), 编码部分, RIGHT(原始内容, LEN(原始内容)-分隔位置), PROPER(名称部分) & " " & UPPER(编码部分) )
公式执行的处理规则:
- 自动定位文本里最后一个半角空格的位置,哪怕巧克力名称包含多个单词(比如Kinder Bueno)也不会拆分错误
- 对单独提取的名称段应用
PROPER(),实现每个单词首字母大写、其余字母小写的格式 - 对单独提取的编码段应用
UPPER(),强制把所有字母转成大写,不管原编码是混写的choc、cHOc、choC,都会统一变成全大写的CHOC开头格式 - 最后将处理好的两部分用空格拼接成标准格式
如果用的是没有LET()函数的旧版Excel,可以拆成4个辅助列分步实现:
- 计算最后一个空格的位置:
=FIND("#",SUBSTITUTE(A2," ","#",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))) - 格式化名称段:
=PROPER(LEFT(A2,B2-1)) - 格式化编码段:
=UPPER(RIGHT(A2,LEN(A2)-B2)) - 拼接最终内容:
=C2&" "&D2
注:原数据里类似
ch0c005这类条目,是输入时把字母O误打成数字0,单纯转大写没法修正这类笔误,只需要在编码处理步骤加一步对应替换逻辑,就能把误打的数字0替换回字母O,匹配CHOC005的标准格式。
大批量数据处理方案(Power Query)
如果是上千上万条的条目数据,不需要逐行拖拽公式,直接用Power Query批量处理:
- 导入数据到Power Query编辑器
- 选择条目列,调用「拆分列-按分隔符拆分」功能,分隔符选半角空格,高级选项设置为「拆分为最右侧的2列」,自动将最后一段编码和前面的名称内容分开
- 对拆分出的名称列,应用「转换-格式-首字母大写」规则
- 对拆分出的编码列,先应用「转换-格式-大写」规则,再用「替换值」功能把输入时手滑打错的数字0(本该是字母O的位置)替换成字母O,匹配标准编码规则
CHOC加三位数字即可 - 用自定义列功能将两列按空格拼接,导出最终结果
内容的提问来源于stack exchange,提问作者Rus Marcoderin
相关产品推荐
相关产品推荐

