优化谷歌表格数值编码生成公式：解决姓名格式适配问题

编码规则回顾 #

需要生成的编码由以下4部分按顺序组合而成：

• 3位补零数字：C列数字格式化为3位（如1→001）
• MMDDYY日期：B列日期转为月(2位)日(2位)年(后2位)格式（如2024-05-03→050324）
• 姓首字母序：姓名中第一个姓氏的首字母转为字母序（A=01，J=10，补零为2位）
• 最后名首字母序：姓名中最后一个名字的首字母转为字母序（补零为2位）

原公式的问题 #

原公式通过FIND(" ", A4)定位第一个空格拆分姓名，仅能处理双字姓名（如"John Brown"）。当姓名包含中间名、多空格时（如"John Michael Brown"），无法正确提取最后一个名字的首字母，导致编码错误。

修正后的公式 #

使用正则表达式提取姓名首尾单词的首字母，兼容任意数量的空格和中间名：

=TEXT(C4, "000") & TEXT(B4, "MMDDYY") & TEXT(CODE(UPPER(REGEXEXTRACT(A4, "^(\w)\w*")))-64, "00") & TEXT(CODE(UPPER(REGEXEXTRACT(A4, "\b(\w)\w*$")))-64, "00")

公式各部分说明 #

1. TEXT(C4, "000")：将C列数字格式化为3位补零字符串
2. TEXT(B4, "MMDDYY")：直接将日期格式化为MMDDYY，替代原公式拆分月/日/年的冗余写法，更可靠
3. REGEXEXTRACT(A4, "^(\w)\w*")：提取姓名开头第一个单词的首字母（姓氏首字母），如需兼容前导空格可改为^[\s]*(\w)\w*
4. REGEXEXTRACT(A4, "\b(\w)\w*$")：提取姓名末尾最后一个单词的首字母，自动跳过中间空格和中间名
5. CODE(UPPER(...))-64：将字母转为字母序（A=1，Z=26），再用TEXT(..., "00")格式化为2位补零字符串

示例验证 #

对于姓名John Michael Brown：

• 姓氏首字母J→10→10
• 最后名首字母B→2→02
• 结合C列数字和日期，生成符合要求的完整编码

内容的提问来源于stack exchange，提问作者Skilz Work