(Excel)常用函数公式及操作技巧之四：文本与页面设置(五)

 

(Excel)常用函数公式及操作技巧之四：

文本与页面设置(五)

——通过知识共享树立个人品牌。

 

 

如何提取一串数字中的几位数字（字符）

如：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),")","")

以关键字提取名称

求当输入球队时，自动生成联赛名称

联赛	球队	当输入球队时	函数自动生成
英超	车路士	车路士	英超
英超	阿仙奴	爱华顿	英超
英超	曼联	祖云达斯	意甲
英超	利物浦	麦斯纳	意甲
英超	爱华顿
英超	米杜士堡
意甲	祖云达斯

=INDEX(A$2:A$23,MATCH(D2,B$2:B$23,0))

=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)来计算，但如果最后一个单词的字符数是不定的呢，如果做呢？ 请大家试下有几种方法。

方法1、用一列公式填充

=IF(LEFT(RIGHT($A$1,ROW()),1)=CHAR(32),RIGHT($A$1,ROW()-1),“”)

方法2、=MID(A1,FIND("       *",SUBSTITUTE(A1," ","       *",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1)-FIND(" ",A1))

方法3、=IF(ISERROR(SEARCH("",TRIM(LEFT(B1)))),RIGHT($A$1,ROW()),"")拖出来的第一个字符就行。

方法4、{=RIGHT(A1,LEN(A1)-MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")*ROW(INDIRECT("1:"&LEN(A1)))))}

嫌长就（假定最长100字符）

{=RIGHT(A1,LEN(A1)-MAX((MID(A1,ROW(1:100),1)=" ")*ROW(1:100)))}

如何去掉单元格最后一位数字

说明:单元格前面的数据不能改变，去掉最后一位数字。

=LEFT(A2,LEN(A2)-IF(ISNUMBER(--RIGHT(A2)),LEN(LOOKUP(9E+307,--RIGHT(A2,ROW(INDIRECT("1:"&LEN(A2)))))),))

如果后面代的数值在两位以内,也可以用以下方式实现,当然如果超过两位的话,可以加语句 IF(ISNUMBER(RIGHT(TRIM(A2), X )+0)=TRUE,1,0)  X 代表后面数值的个数。

=LEFT(TRIM(A2),LEN(TRIM(A2))-IF(ISNUMBER(RIGHT(TRIM(A2),1)+0)=TRUE,1,0)+IF(ISNUMBER(RIGHT(TRIM(A2),2)+0)=TRUE,1,0))

如何在一列已经输入的数据前添加“p”

比如一列数据            添加后变

112234                 p112234

123435                 p123435

124355                 p124355

123545                 p123545

选中所有单元格，设置格式，自定义-〉在缺省的“G/通用格式”前面加上“"p"”（半角的双引号中间是p）即可

加一列全是“p”，使用&=a1&b1         Shift+7

="P"&A1

在自定义中输入"Q"#即可,很简单的。

什么函数可以插入字符

怎样用第一列的数据形成第二列的数据，即在特定位置加上几个相同字符串？

解答： b1="04"& a1

问：哪如果倒过来呢？

答：用公式A1=MID(B1,3,13)或A1=SUBSTITUTE(B1,"04","",1)

如何在数据前添加“*”号

数据如在B列,在A列整列加“*”，C列C1输入公式C1=A1&B1,

下拉。用“选择性粘贴”选“值”复制到D列，删去A、B、C列。

数字前面加上数字

123

    0123

    75223

比如说上述的数字，我想在它们前面加上38910104，而且位置短的数字，会自动补0

变成如下：

3891010400123

    3891010400123

    3891010475223）

=TEXT(A1,"3891010400000")

3、查找替换，查找栏输入：第*班，替换栏空置，全部替换