Excel 自动4位分隔长数字 ，如银行卡号

 #记录工作实践#

背景

工作中，做财务相关工作时，有大量的银行卡号希望打印成每 4 位空一格的形式，方便后续查看。

如：6200 1234 1234 1234 123

Word 域代码没有实现方式，VBA 编程太麻烦就不考虑了。于是选择用 Excel 做表格，然后邮件合并到 Word 里。

在 Excel 里，银行卡号必须用字符串，不能用数字储存，否则就会变成科学计数法和很多 0。

先考虑单元格格式，如果原始数据是文本的情况下，千位分隔符根本无法使用。自定义设置 #### #### #### #### 也无效。

用 TEXT 函数强制转换会丢失精度，并且因为这个是按照数字从右往左匹配，遇上一些美国银行的公司账号只有12位的则会变得很诡异。

于是我翻看 Excel 函数列表，看能不能用 SPLIT 搞出点啥名堂，结果发现不能使用固定位数进行分割。直到我学习至数组运算部分，发现了 SEQUENCE 这个函数可以生成连续的数字，这不就可以用来做 for 循环了吗。

---

结论

先贴上完整代码和实现效果。注意我用到了 MS Office 2021 才支持的函数。

=LET(account, A1, TEXTJOIN(" ",TRUE,MID(account,SEQUENCE(CEILING(LEN(account)/4,1),,0)*4+1,4)))

---

分步解释 Excel 函数

因为有两处需要写数据的地方，首先将 A1 格值存为 account 变量方便使用。

=LET(account, A1, 剩余代码)

 LEN 得到字符串长度，每 4 位分隔需要除以 4 后用 CEILING 向上取整，对于银联 18 位卡号等于5。

=CEILING(LEN(account)/4, 1)

SEQUENCE 可以生成连续的数字的数组，默认从 1 开始。更改为从 0 开始，然后 ×4 + 1 就得到了 MID 函数用的字符串截取开始位置索引数字数组。

刚才我才发现，SEQUENCE 里原来自带了增长值，就可以少写后面的数字计算了。前面的代码我就不改了，以下三种方式等价。

=SEQUENCE(5,,0) * 4 + 1
=(SEQUENCE(5) - 1) * 4 + 1
=SEQUENCE(5,,1,4)

然后上述数组作为 MID 的索引，每次固定取 4 位。就会得到从左到右按 4 位分隔的字符串数组了。

=MID("6200123412341234123", {1;5;9;13;17}, 4)

最后再用 TEXTJOIN ，空格作为分隔符，将数组合并到一个字符串就成功了。

=TEXTJOIN(" ", TRUE, {"6200";"1234";"1234";"1234";"123"} )

这样，不管是多少位字符都可以按 4 位分隔了。

用我比较熟悉的 JavaScript 代码来解释步骤，就是这样

//用 LET 存储 =LET(account, 输入字符串, 剩余代码)
let account = "6200123422343234423";
//计算按 4 位分割需要分割为几个部分，结果为 5
let loopTime = Math.ceil(account.length / 4);
//得到5个序列的数组 [ 0, 1, 2, 3, 4 ]
let indexArray = new Array(loopTime).fill(null).map((_, index)=>index);
//变化为字符串分割开始下标 [ 0, 4, 8, 12, 16 ]，注意 Excel 里下标从 1 开始所以需要 + 1
let sequnceArray = indexArray.map(index=>index * 4);
//从原始字符串里每次截取 4 个字符 [ "6200", "1234", "2234", "3234", "423" ]
let stringArray = sequnceArray.map(index=>account.substring(index,index+4));
//用空格作为分隔符合并 "6200 1234 2234 3234 423"
stringArray.join(" "); 

---

新结论

我写到这里才发现，把函数里的所有 4 也提出来成为变量，就可以按任意位分隔字符了。比如如下代码可以按 7 位分割字符。

=LET(account, A1, part_len, 7, TEXTJOIN(" ",TRUE,MID(account,SEQUENCE(CEILING(LEN(account)/part_len,1),,1,part_len),part_len)))