EXCEL IP地址最后一位填充为3位数

谁能告诉我有什么函数可以办到的

2025-02-03 04:49:06

推荐回答（2个）

回答1：

LEFT(A2,FIND("X",SUBSTITUTE(A2,".","X",3))-1)&"."&IF(LEN(RIGHT(A2,LEN(A2)-FIND("X",SUBSTITUTE(A2,".","X",3))))=3,RIGHT(A2,LEN(A2)-FIND("X",SUBSTITUTE(A2,".","X",3))),IF(LEN(RIGHT(A2,LEN(A2)-FIND("X",SUBSTITUTE(A2,".","X",3))))=2,"0"&RIGHT(A2,LEN(A2)-FIND("X",SUBSTITUTE(A2,".","X",3))),"00"&RIGHT(A2,LEN(A2)-FIND("X",SUBSTITUTE(A2,".","X",3)))))

回答2：

假设为IP地址在A1单元格则在其他地方输入
=LEFT(A1,LEN(A1)-RIGHT(A1))&TEXT(RIGHT(A1),"000")
不好意思写错了修改了两下
或者用公式 =MID(A1,1,LEN(A1)-RIGHT(A1))&TEXT(RIGHT(A1),"000")