本帖最后由 mmlzh 于 2018-4-19 10:10 编辑
扯蛋的数据,为毛不能在输入的时规范,折腾,1周是1个星期还是1周岁?这能说得准吗- =IFERROR(IF(OR(ISNUMBER(FIND("周",A2)),ISNUMBER(FIND("岁",A2)),A2),LEFT(A2,MIN(FIND("周",A2&"周"),FIND("岁",A2&"岁"))-1),)+IF(LEN(TRIM(SUBSTITUTE(A2,IF(OR(ISNUMBER(FIND("周",A2)),ISNUMBER(FIND("岁",A2)),A2),LEFT(A2,MIN(FIND("周",A2&"周"),FIND("岁",A2&"岁"))),),"")))>0,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,IF(OR(ISNUMBER(FIND("周",A2)),ISNUMBER(FIND("岁",A2)),A2),LEFT(A2,MIN(FIND("周",A2&"周"),FIND("岁",A2&"岁"))),""),""),"个月",""),"月","")/12,),A2)
复制代码 |