Excel单元格中输入银行卡号每四位自动加一个空格
答案:4 悬赏:0
解决时间 2021-01-05 09:59
- 提问者网友：咪咪
- 2021-01-05 04:49
Excel单元格中输入银行卡号每四位自动加一个空格
最佳答案
- 二级知识专家网友：逐風
- 2021-01-05 05:08
假设A1中是19位的文本形的建行账号
在B1中输入或复制粘贴下列公式
=REPLACE(REPLACE(REPLACE(REPLACE(A1,17,," "),13,," "),9,," "),5,," ")
或
=REPLACE(REPLACE(REPLACE(REPLACE(A1,5,," "),10,," "),15,," "),20,," ")
下拉填充
在B1中输入或复制粘贴下列公式
=REPLACE(REPLACE(REPLACE(REPLACE(A1,17,," "),13,," "),9,," "),5,," ")
或
=REPLACE(REPLACE(REPLACE(REPLACE(A1,5,," "),10,," "),15,," "),20,," ")
下拉填充
全部回答
- 1楼网友：青灯有味
- 2021-01-05 07:19
选定这一列设置单元格格式----算定义---类型下输入0000 0000 0000 0000 00
- 2楼网友：duile
- 2021-01-05 06:38
假设需要转换的数据在A1,可以在B1输入下面的公式
=REPLACE(REPLACE(REPLACE(REPLACE(A1,5,1," "),10,1," "),15,1," "),20,1," ")
不知道是不是你要的效果
=REPLACE(REPLACE(REPLACE(REPLACE(A1,5,1," "),10,1," "),15,1," "),20,1," ")
不知道是不是你要的效果
- 3楼网友：怙棘
- 2021-01-05 05:22
最后一个空格后面如果是3个数字的话，就把格式中最后一个0去掉
追问不行啊？录入银行卡号时从16位开始都显示0了，
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！
大家都在看
推荐资讯