楼主 lrlxxqxa |
Q:如何从同一单元格字符串中拆分最大值最小值?
A:以最小值为例(最大值同理)
1- =IF(ISNUMBER(FIND("-",$A2)),--LEFT($A2,FIND("-",$A2)-1),IF(OR(LEFT($A2)={"≥",">"}),--MID($A2,2,5),IF(OR(LEFT($A2)={"≤","<"}),0,IF(ISNUMBER(FIND("±",$A2)),LEFT($A2,FIND("±",$A2)-1)-MID($A2,FIND("±",$A2)+1,5)))))
2- =IF(ISNUMBER(FIND("-",$A2)),--LEFT($A2,FIND("-",$A2)-1),IF(OR(LEFT($A2)={"≥",">"}),--MID($A2,2,5),IF(OR(LEFT($A2)={"≤","<"}),0,IF(ISNUMBER(FIND("±",$A2)),LEFT($A2,FIND("±",$A2)-1)-MID($A2,FIND("±",$A2)+1,5)))))
3- =--CHOOSE(LOOKUP(1,0/(LEN(A2)-LEN(SUBSTITUTE(A2,{"-","≥","≤",">","<","±"},))),ROW($1:$6)),LEFT(A2,FIND("-",A2)-1),MID(A2,2,99),0,MID(A2,2,99),0,LEFT(A2,FIND("±",A2)-1)-MID(A2,FIND("±",A2)+1,99))
4- =--CHOOSE(LOOKUP(,-FIND({"-","≥",">","≤","<","±"},A2),{1,2,3,4,5,6}),LEFT(A2,FIND("-",A2)-1),MID(A2,2,15),MID(A2,2,15),0,0,LEFT(A2,FIND("±",A2)-1)-MID(A2,FIND("±",A2)+1,15))
5- =IF(OR(LEFT(A2)={"<","≤"}),,IF(OR(LEFT(A2)={"≥",">"}),MID(A2,2,9),IF(ISERR(FIND("-",A2)),LEFT(A2,FIND("±",A2)-1)-MID(A2,FIND("±",A2)+1,9),LEFT(A2,FIND("-",A2)-1))))
6- =IF(COUNTIF(A2,"*-*"),LEFT(A2,FIND("-",A2)-1),IF(OR(COUNTIF(A2,{"≥*",">*"})),RIGHT(A2,LEN(A2)-1),IF(OR(COUNTIF(A2,{"≤*","<*"})),0,IF(COUNTIF(A2,"*±*"),LEFT(A2,FIND("±",A2)-1)-RIGHT(A2,LEN(A2)-FIND("±",A2)),""))))*1
7- =CHOOSE(MATCH(9,FIND({"-","≥",">","<","≤","±"},A2)),LEFT(A2,FIND("-",A2)-1),MID(A2,2,9),MID(A2,2,9),,,LEFT(A2,FIND("±",A2)-1)-MID(A2,FIND("±",A2)+1,9))
8- =IF(COUNT(FIND({"≥",">"},A2)),RIGHT(A2,LEN(A2)-1),-LOOKUP(1,-LEFT("00"&A2,ROW($1:$9))))-(0&MID(A2,FIND("±",A2&"±")+1,9))
- =-IF(COUNT(FIND({"≥",">"},A2)),-MID(A2,2,9),LOOKUP(1,-LEFT("00"&A2,ROW($1:6))))-(0&MID(A2,FIND("±",A2&"±")+1,9))
- =IF(COUNT(FIND({"≥",">"},A2)),MID(A2,2,9),-LOOKUP(1,-LEFT(0&0&A2,ROW($1:9))))-(0&MID(A2,FIND("±",A2&"±")+1,9))
拆分最大值最小值.rar |