☰

在 Excel 2007 中，我需要将电话号码转换为序列号，并保留最后 4 位数字

tag-icon • tag-icon microsoft-excel worksheet-function
在 Excel 2007 中，我需要将电话号码转换为序列号，并保留最后 4 位数字

例如：假设设备电话号码是847-630-4459。

  • 步骤 1：我们在数字前面添加一个“1”，得出：1-847-630-4459
  • 第 2 步：对于前 7 位数字（1-847-630），我们从 10 中减去每个数字。例如 10-1 = 9、10-8 = 2，等等。
  • 步骤 3：新标签创建为“序列号”。最终结果如下：

    序列号 92634704459

我需要对 465 条电话线路执行此操作 - 有没有办法创建一个公式来自动执行此操作？

答案1

假设您的数字都有 10 位数字，那么使用 A1 中的数字，在 B1 中尝试此公式进行转换

="S/N 9"&SUMPRODUCT(MOD(-MID(SUBSTITUTE(A1,"-",""),{1,2,3,4,5,6},1),10),10^{5,4,3,2,1,0})&RIGHT(A1,4)

您可以将公式复制到列中以转换全套数字

答案2

这是一个更加数学导向的方法：

="S/N 9"&(1111110-INT(SUBSTITUTE(A1,"-","")/10000))*10000+MOD(SUBSTITUTE(A1,"-",""),10000)

不幸的是，它不能完全实现您的指令——当输入的电话号码包含0（零）时，前一位数字会偏离一位。但它确实满足了您的隐含要求，即不同的电话号码应始终产生不同的序列号。

当然，您可以使用辅助列来缩短该公式。如果您设置B1为包含=SUBSTITUTE(A1, "-", "")，则序列号可以表示为

="S/N 9" & (1111110-INT(B1/10000))*10000 + MOD(B1, 10000)

相关内容