Excel公式问题：提取单元格开头1-16数字，现有公式仅支持到10

我有一个包含编码的表格，编码范围为1至16，编码后紧跟一个字母，示例数据如下：

11A - Reconnect
15b telephone
1b reconnect
2c

部分数据开头是单个数字，部分是两位数字。我当前使用的公式仅能正确提取10及以下的数字，无法提取11-16的完整数字：

=SUBSTITUTE(J2,RIGHT(J2,LEN(J2)-MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9,10},J2),""))),"")

我尝试在公式中添加"11,12,13,14,15,16"，但仍无法生效。另外，我提取字母的公式运行正常：

=IF(L2="INVALID CODE","",LEFT(TEXTAFTER(J2, L2),1))

可行解决方案 #

方案1：正则提取（支持Excel 365/2021及以上） #

直接用REGEXEXTRACT匹配开头的所有连续数字，一步到位：

=REGEXEXTRACT(J2,"^\d+")

这个公式会自动识别开头的1位或2位数字，完美覆盖1-16的编码范围。

方案2：兼容旧版Excel的公式 #

如果你的Excel版本不支持正则，用这个公式定位第一个字母的位置，提取前面的数字：

=LEFT(J2,MIN(IFERROR(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"},J2),LEN(J2)+1))-1)

原理是找到第一个大小写字母的位置，截取该位置之前的所有字符，就是目标编码数字。

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