楼主
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
|