excel提取相同数据

发布网友 发布时间：2022-02-26 13:42

我来回答

共3个回答

热心网友 时间：2022-02-26 15:11

假设数据在a列a1
b1输入
=IF(LEN(A12)-LEN(SUBSTITUTE(A1,"/",""))=2,LEFT(A1,6)&"/"&LEFT(A1,6-(LEN(A1)-FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))))&MID(A1,8,1)&"/"&LEFT(A1,6-(LEN(A1)-FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))))&RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))),LEFT(A1,6)&"/"&LEFT(A1,6-(LEN(A1)-FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))))&RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))))

综合2楼的方法会更简单：
=SUBSTITUTE(A1,"/","/"&LEFT(A1,6-(LEN(A1)-FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))))

热心网友 时间：2022-02-26 16:29

假设数据在a列a1
b1输入
=SUBSTITUTE(A1,"/","/"&LEFT(A1,5))

向下拖

可以替换568947/5/6这种的 就是只有1位的

你的两位的都是这样的么？ 598764/75

有没有 598764/75/9的？

如果1位的就是1位的
2位的就都是2为的
可以这样
=IF(LEFT(RIGHT(A1,2),1)="/",SUBSTITUTE(A1,"/","/"&LEFT(A1,5)),SUBSTITUTE(A1,"/","/"&LEFT(A1,4)))

要是598764/75/9的的暂时不能用

然后复制b列-右健-选择性粘贴-数值
删除a列

热心网友 时间：2022-02-26 18:04

你的要求是？？？