office excel最常用函数公式技巧搜集大全(13.12.09更新)导读:截取字符的公式,数据在A1单元格,则公式为:,取数值后三位公式,取数函数,单元格中用函数单独取出*号前(后)的数,数组公式,用数组公式:,这个用函数可轻易解决:=LEFT(A1,FIND(":",A,若没有要求一定要用函数解,公式为:=LEFT(A1,FIND("",A1)-1),采用函数分割:例如:A1:name@163.com,且这个函数没有四舍五入
至此,读者一定会发现,再复杂的斜线表头,利用直线和文本框工具,也就迎刃而解了! ㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜ 【字符截取与增减】 截取单元格里某个字符后的字符 =RIGHT(A1,LEN(A1)-SEARCH(",",A1,1)) 截取字符的公式 有一组数据101~103*,11~20*?我想截取~至*之間的數字。 =MID($A3,FIND("~",$A3)+1,FIND("*",$A3)-(FIND("~",$A3)+1)) 如何确定*号后的数字 =RIGHT(A1,LEN(A1)-FIND("*",A1,1)) =REPLACE(A1,1,FIND("*",A1),) =TRIM(RIGHT(SUBSTITUTE(A1,"*",REPT(" ",LEN(A1))),LEN(A1))) =MID(A1,SEARCH("~*",A1)+1,100) =SUBSTITUTE(A1,LEFT(A1,FIND("*",A1)),"") {=--MID(A1,MATCH("~*",MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),)+1,100)} =RIGHT(A1,LEN($A$1)-FIND(CHAR(CODE("*")),$A$1,1))(取最后三位) 如何提取数字中间的数 数据在A1单元格,则公式为: =MID(A1,5,3) 解释:“5”是从第5位开始提取,“3”是提取3个数。 三个数中,如何取出中间那个 比如1,2,3三个数字,我想取出2,用什么方法? =LARGE(A1:A3,2) 取数值后三位公式 =RIGHT(A1,3) 取数函数 单元格中用函数单独取出 *号前 (后)的数 假如2.01*750位于A1 =MID(A1,1,FIND("*",A1,1)-1) 取得*号前的数据 =MID(A1,FIND("*",A1,1)+1,LEN(A1)-FIND("*",A1,1)+1) 取得*号后的数据 对2.01*750*800的得数就是750*800,而不是800,怎么办呢 =MID(B2,FIND("*",B2,FIND("*",B2,1)+1)+1,LEN(B2)) =RIGHT(B2,LEN(B2)-FIND("/",SUBSTITUTE(B2,"*","/",LEN(B2)-LEN(SUBSTITUTE(B2,"*",""))))) 如何把单元格中的数字提取出来(字符串中不连续) 数组公式 =SUM(MID(0&A4,LARGE(ISNUMBER(--MID(A4,ROW($1:$20),1))*ROW($1:$20),ROW($1:$20))+1,1)*10^ROW($1:$20)/10) 数字在字符串中不连续如何提取数字 如:3k3mn249up 结果:33249 033k3mn249up 结果:333249 用数组公式: =SUM(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW($1:$1024),1))* ROW($1:$1024),ROW($1:$308))+1,1)*10^ROW($1:$308)/10) 用如何提取“-”前后的字符 5-0, 4-2, 0-6, 取左边=CHOOSE(TYPE($D4),MONTH($D4),LEFT($D4,FIND("-",$D4)-1)) 取右边=CHOOSE(TYPE($D4),DAY($D4),RIGHT($D4,LEN($D4)-FIND("-",D$4))) 怎样删去﹕后的文字 格式都是 XXXX : YYYYYY,如何只保留XXXX,而全部删下﹕ 后的文字呢﹖ (XXXX 的长度是不一样) 这个用函数可轻易解决:=LEFT(A1,FIND(":",A1)-1) 若没有要求一定要用函数解,那也可以试试用[数据]>(数据剖析) 怎样只取“.”之后的文字﹖ 如:Q24-S4. Working Status 只取 Working Status 如果“.” 前的字數固定 =RIGHT(A1,LEN(A1)-7) 如果不固定 =RIGHT(A1,LEN(A1)-FIND(".",A1)) =TRIM(RIGHT(A1,LEN(A1)-FIND(".",A1,1)) 获取单元格内容中字符串 08:25,18:25 如:要取得单元格b5中的从左边算起五位的字符串。即是08:25 =LEFT(B5,5) 如:要取得单元格b5中的从右边算起五位的字符串。即是08:25 =RIGHT(B18,5) 单元格编号,起始位数,从起始位算起的第几位数 MEP090296 =MID(B1,4,3) 结果:090 如何提取一串数字中的几位数字(字符) 如:050326 提取后3位数字 =RIGHT(A1,3) “3”是提取3位,如果改“4”,则提取4位。 =RIGHT(A3,LEN(A3)-3) =MID(A3,4,3) =REPLACE(A3,1,3,"") 提取中间的4位数字,“5032” =MID(A1,2,4) =MID(A3,(LEN(A3)-4)/2+1,4) 要提取 050324 中的 502 怎么提取?即:第二、三两位和第五位数字 =MID(A3,2,2)&MID(A3,5,1) =MID(A3,2,1)&MID(A3,3,1)&MID(A3,5,1) 如何把一个单元格中的数字挑出来 一个单元格中有数字、空格、汉字,如:“11210101 银行存款/工行”,数字的位数不确定,但都从最左边开始,数字和汉字中间有一个空格。如何只把数字显示出来? 1、如果都是这样就简单 "都从最左边开始,数字和汉字中间有一个空格" 假定在A1,公式为:=LEFT(A1,FIND(" ",A1)-1) 2、数据分列不更简单么?分列符号选中空格前面那个框。 分割文本 有一列数据,全部是邮箱的,现在想将@前面的账号与@后面的域名分割开,分为两列,如何做? 采用函数分割:例如:A1: name@163.com B1:=LEFT(A1,FIND("@",A1)-1) --> name C1:=RIGHT(A1,LEN(A1)-FIND("@",A1)) --> 163.com 或:数据-分列-分列-分隔符号-@就可以了 按照给定的位数,截断小数点后的数字 对整数无效,且这个函数没有四舍五入的功能 12512.2514 12512.25 =TRUNC(B23,2) 单元格数字提取问题 单元格里面填写的 CHIP(0601-2299),把0601-2299 提取出来应该怎么做。 =IF(B3="","",SUBSTITUTE(MID(B3,FIND("(",B3)+1,100),")","")) 我用IF函数是因为看到你给的表格当中,数据和数据之间都有一行空行, 如果没有空行的话,数据是连续的时候公式可以简化为: =SUBSTITUTE(MID(B3,FIND("(",B3)+1,100),")","") 以关键字提取名称 =INDIRECT("A"&MATCH(D2,B:B,)) =VLOOKUP(D2,IF({1,0},$B$2:$B$23,$A$2:$A$23),2,0) 如何把文本中的前几个字符去除 如将“第二班AAA”中的“第二班”三个字去除。 1、用公式: =RIGHT($A2,3) =RIGHT($A2,LEN($A2)-FIND("班",$A2)) =MID($A2,FIND("班",$A2)+1,LEN($A2)) =RIGHT(B2,LENB(B2)-LEN(B2)) =REPLACE(A1,1,3,"") =SUBSTITUTE(A1,"第二班",) 如前几个字符或后英文字數, 不相同或不固定,公式可用: {=MID(A2,MATCH(0,--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<65),0),255)} 2、数据>分列>固定宽度,把上述数据分为两列后,删除第一班的列 对一列中的文字统一去掉最后一个字 能否对一列中的文字统一去掉最后一个字?这些文字不统一,有些字数多,有些字数少。如何处理? =REPLACE(A1,LEN(A1),1," ")(在过渡列进行) 讨如何去掉单元格中的第一个数字? =MID(A1,2,LEN(A1)-1) 或者 =RIGHT(A1,LEN(A1)-1) =REPLACE(A1,1,1,"") 论一下取最后一个单词的方法 例如现在在A1中有一句“M. Henry Jackey”,如何用函数将最后的一个单词取出来呢? 当然,我们现在是知道最后的单词是6个字符,可以用Right(A1,6)来计算,但如果最后一个单词的字符数是不定的呢,如果做呢? 请大家试下有几种方法。 |
|