例如:单元格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",""))