求助EXCEL 文本内添加信息(关于上次这个公式修改)
发布网友
共3个回答
热心网友
=if(A1="","","T"&text(TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",99)),1,99)),"00")&TEXT(TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",99)),2*99-98,99)),"-00")&TEXT(TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",99)),3*99-98,99)),"-0"))
热心网友
=IF(A1="","","T"&IF(LEN(LEFT(A1,FIND("-",A1,1)-1))=1,"0"&LEFT(A1,FIND("-",A1,1)-1),LEFT(A1,FIND("-",A1,1)-1))&"-"&IF(LEN(RIGHT(A1,LEN(A1)-FIND("-",A1,1)))=1,"0"&RIGHT(A1,LEN(A1)-FIND("-",A1,1)),RIGHT(A1,LEN(A1)-FIND("-",A1,1))))
看一下用这个公式怎么样？追问1-1
1-2
10-1
需要转变成
T01-01
T01-02
T10-01
"-"前面的数字也变成两位
追答我这个公式就是带两位的，前面加0
热心网友
=IF(A1="","","T"&IF(LEN(MID(A1&"--",1,FIND("-",A1&"--",1)-1))=1,0,"")&MID(A1&"--",1,FIND("-",A1&"--",1)-1)&IF(FIND("-",A1&"--",2)=LEN(A1&"--")-1,"","-"&IF(LEN(MID(A1&"--",FIND("-",A1&"--",1)+1,FIND("-",A1&"--",FIND("-",A1&"--",1)+1)-FIND("-",A1&"--",1)-1))=1,0,"")&MID(A1&"--",FIND("-",A1&"--",1)+1,FIND("-",A1&"--",FIND("-",A1&"--",1)+1)-FIND("-",A1&"--",1)-1)))
