32、left、mid、right的用法

例如：单元格AG8【每包12元】

一：left（text，num_chars）

num_chars：字符长度

（1）=LEFT(AG8,2)

        表示：提取2个长度

        结果为：每包

（2）=LEFT(AG8,3)

        表示：提取3个长度

        结果为：每包1

二：mid（text，startNum，num_chars）

startNum：查找起始位置;

num_chars：从起始位置提取的字符个数

（1）=MID(AG8,2,3) 

        表示：从第2个位置开始查找，查找3个字符

        结果为：包12  

（2）=MID(AG8,3,2)

       表示：从第3个位置开始查找，查找2个字符

       结果为：12  

注意：

（1）这里是一个字代表一个字符

（2）=TYPE(MID(D5,3,6))  这里输出的是【2】，说明返回的是文本

（3）=MID(D5,3,6)+4，这里输出的是【#value】，说明文本不能与数字相加

（4）=MID(D5,3,6)，这里返回空文本 (") ，原因是，这里的【D5】单元格里面的内容是【√】，因为3表示从第3个字符开始查找，

而单元格中只有1个字符（start_num大于文本长度）。可以用if语句来判断，如=IF(MID(D5,3,6)="","ddd","333")，输出为【ddd】。

 

三：right（text，num_chars） 

（1）=Right(AG8,2) 

        表示：提取2个长度 

        结果为：2元 

（2）=Right(AG8,3) 

        表示：提取两3个长度 

        结果为：12元

 

如果我只是查找数字的话，我不知道从第几个文字开始查找，也不知道数字的长度，那怎么办呢？

例如：加班8H,如何获取H前面的数字呢？而且怎么知道这个数字有多长呢？

 

 

=IF(MID(D6,3,6)="","0",SUBSTITUTE(MID(D6,3,6),"H",""))
+IF(MID(E6,3,6)="","0",SUBSTITUTE(MID(E6,3,6),"H",""))
+IF(MID(F6,3,6)="","0",SUBSTITUTE(MID(F6,3,6),"H",""))
+IF(MID(G6,3,6)="","0",SUBSTITUTE(MID(G6,3,6),"H",""))
+IF(MID(H6,3,6)="","0",SUBSTITUTE(MID(H6,3,6),"H",""))
+IF(MID(I6,3,6)="","0",SUBSTITUTE(MID(I6,3,6),"H",""))
+IF(MID(J6,3,6)="","0",SUBSTITUTE(MID(J6,3,6),"H",""))
+IF(MID(K6,3,6)="","0",SUBSTITUTE(MID(K6,3,6),"H",""))
+IF(MID(L6,3,6)="","0",SUBSTITUTE(MID(L6,3,6),"H",""))
+IF(MID(M6,3,6)="","0",SUBSTITUTE(MID(M6,3,6),"H",""))
+IF(MID(N6,3,6)="","0",SUBSTITUTE(MID(N6,3,6),"H",""))
+IF(MID(O6,3,6)="","0",SUBSTITUTE(MID(O6,3,6),"H",""))
+IF(MID(P6,3,6)="","0",SUBSTITUTE(MID(P6,3,6),"H",""))
+IF(MID(Q6,3,6)="","0",SUBSTITUTE(MID(Q6,3,6),"H",""))
+IF(MID(R6,3,6)="","0",SUBSTITUTE(MID(R6,3,6),"H",""))
+IF(MID(S6,3,6)="","0",SUBSTITUTE(MID(S6,3,6),"H",""))
+IF(MID(T6,3,6)="","0",SUBSTITUTE(MID(T6,3,6),"H",""))
+IF(MID(U6,3,6)="","0",SUBSTITUTE(MID(U6,3,6),"H",""))
+IF(MID(V6,3,6)="","0",SUBSTITUTE(MID(V6,3,6),"H",""))
+IF(MID(W6,3,6)="","0",SUBSTITUTE(MID(W6,3,6),"H",""))
+IF(MID(X6,3,6)="","0",SUBSTITUTE(MID(X6,3,6),"H",""))
+IF(MID(Y6,3,6)="","0",SUBSTITUTE(MID(Y6,3,6),"H",""))
+IF(MID(Z6,3,6)="","0",SUBSTITUTE(MID(Z6,3,6),"H",""))
+IF(MID(AA6,3,6)="","0",SUBSTITUTE(MID(AA6,3,6),"H",""))
+IF(MID(AB6,3,6)="","0",SUBSTITUTE(MID(AB6,3,6),"H",""))
+IF(MID(AC6,3,6)="","0",SUBSTITUTE(MID(AC6,3,6),"H",""))
+IF(MID(AD6,3,6)="","0",SUBSTITUTE(MID(AD6,3,6),"H",""))
+IF(MID(AE6,3,6)="","0",SUBSTITUTE(MID(AE6,3,6),"H",""))