兼容Excel与LibreOffice Calc的字符串末尾数字递增公式开发需求

需求 #

编写同时兼容Microsoft Excel和LibreOffice Calc的公式，支持跨列复制，实现将前一列单元格字符串中的最后一组数字递增1，且保留原数字的前导零格式。

输入输出示例 #

输入（单元格A1） #

• host1
• host01
• host1-ilo
• host01-idrac
• dc1-host1
• dc1host01
• dc1-host1-ilo
• dc01host01-idrac

期望输出（单元格B1） #

• host2
• host02
• host2-ilo
• host02-idrac
• dc1-host2
• dc1host02
• dc1-host2-ilo
• dc01host02-idrac

核心思路 #

提取字符串中最后一组连续数字，将其转为数值加1后，按原数字长度补全前导零，最后替换回原字符串对应位置。

现有公式问题 #

当前使用的数组公式会提取字符串中所有数字拼接在一起，无法处理包含多组数字的输入（如dc1-host1这类示例），公式如下：

=IFERROR(IF(COUNT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>LEN(TEXTJOIN("",1,IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0,""))),"", TEXT(VALUE(TEXTJOIN("",1,IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0,"")))+1,REPT("0",LEN(TEXTJOIN("",1,IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0,"")))))),""

解决方案公式 #

以下公式可兼容Excel（旧版本需按Ctrl+Shift+Enter作为数组公式输入）和LibreOffice Calc，精准定位最后一组数字并完成递增：

=REPLACE(A1,MAX(IFERROR(SEARCH("[^0-9]",A1,ROW(INDIRECT("1:"&LEN(A1)))),0))+1,LEN(A1)-MAX(IFERROR(SEARCH("[^0-9]",A1,ROW(INDIRECT("1:"&LEN(A1)))),0)),TEXT(VALUE(MID(A1,MAX(IFERROR(SEARCH("[^0-9]",A1,ROW(INDIRECT("1:"&LEN(A1)))),0))+1,LEN(A1)-MAX(IFERROR(SEARCH("[^0-9]",A1,ROW(INDIRECT("1:"&LEN(A1)))),0)))+1,REPT("0",LEN(A1)-MAX(IFERROR(SEARCH("[^0-9]",A1,ROW(INDIRECT("1:"&LEN(A1)))),0)))))

公式逻辑说明 #

1. 定位最后一组数字：通过MAX(IFERROR(SEARCH("[^0-9]",A1,ROW(INDIRECT("1:"&LEN(A1)))),0))找到字符串中最后一个非数字字符的位置，后续从该位置+1开始即为最后一组数字的起始点。
2. 提取并递增数字：提取最后一组数字转为数值后加1，再用REPT("0",原数字长度)格式化为带前导零的文本，确保格式与原数字一致。
3. 替换回原字符串：使用REPLACE函数将原字符串中的最后一组数字替换为递增后的格式化数字。

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