在Excel中常用函数
函数1：MID函数
在Excel中常用的函数，你知道吗？
在Excel中常用的函数，你知道吗？
在Excel中常用的函数，你知道吗？
函数功能：输出文本字符串中从指定位置开始的特定数目的字符。
函数语法：MID(text, start_num, num_chars)
参数解释：
text：表示包含要提取字符的文本字符串。
start_num：表示文本中要提取的个字符的位置。文本中个字符的start_num为1，依此类推。
num_chars：表示指定希望MID从文本中返回字符的个数。
函数2：FIND函数
函数功能：在第二个文本串中定位个文本串，并输出个文本串的起始位置的值。
函数语法：FIND(find_text, within_text, [start_num])
参数解释：在第二个文本串中定位个文本串，并输出个文本串的起始位置的值。
find_text：表示要查找的文本。
within_text：表示包含要查找文本的文本。
start_num：表示指定要从其开始搜索的字符。within_text中的首字符是编号为1的字符。如果省略start_num，则默认其值为1。
函数3：LEFT函数
函数功能：输出从文本左侧开始指定个数的字符。
函数语法：LEFT(text, [num_chars])
参数解释：
text：表示包含要提取的字符的文本字符串。
num_chars：可选。指定要由LEFT提取的字符的数量。
如图，在B2收件地址中，从左提取出省市的名称。公式为：
=LEFT(B2,FIND({"市","省"},B2))
函数4：RIGHT函数
函数功能：输出文本字符串中后一个或多个字符。
函数语法：RIGHT(text,[num_chars])
参数解释：
text：表示包含要提取字符的文本字符串。
num_chars：可选。指定了要由RIGHT提取的字符的数量。
如图，在B2单元格中提取出市级之后的详细地址信息。公式为：
=RIGHT(B2,FIND("市",B2)+1)
函数5：LOWER函数
函数功能：将大写字母转换为小写字母。
函数语法：LOWER(text)
参数解释：
text：表示要转换为小写字母的文本。函数LOWER不改变文本中的非字母的字符。
如图，将A2中的大写字母转换成小写字母。公式为：
=LOWER(A2)
函数6：PROPER函数
函数功能：首字母转换成大写，并将其余的字母转换成小写。
函数语法：PROPER(text)
参数解释：
text：表示用引号括起来的文本、返回文本值的公式或是对包含文本（要进行部分大写转换）的单元格的引用。
函数7：UPPER函数
函数功能：将文本转换成大写形式。
函数语法：UPPER(text)
参数解释：
text：需要转换成大写形式的文本。text可以为引用或文本字符串。
函数8：LEN函数
函数功能：输出文本字符串中的字符数。
函数语法：LEN(text)
参数解释：
text：表示要查找其长度的文本。空格将作为字符进行计数。
函数9：TEXT函数
函数功能：将数值转换为按指定数字格式表示的文本。
函数语法：TEXT(value,format_text)
参数解释：
value：表示数值、计算结果为数字值的公式或对包含数字值的单元格的引用。
format_text：作为用引号括起的文本字符串的数字格式。通过单击“设置单元格格式”对话框中的“数字”选项卡的“类别”框中的“数字”、“日期”、“时间”、“货”或“自定义”并查看显示的格式，可以查看不同的数字格式。format_text不能包含星号（）。
如图，将简化输入的日期，利用TEXT函数转换为日期格式。公式为：
函数10：REPLACE函数
函数功能：使用其它文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。
函数语法：REPLACE(old_text, start_num, num_chars, new_text)
参数解释：
old_text：表示要替换其部分字符的文本。
start_num：表示要用new_text替换的old_text中字符的位置。
num_chars：表示希望REPLACE使用new_text替换old_text中字符的个数。
new_text：表示将用于替换old_text中字符的文本。
函数11：SUBSTITUTE函数
函数功能：在文本字符串中用new_text替代old_text。
函数语法：SUBSTITUTE(text,old_text,new_text,instance_num)
参数解释：
text：表示需要替换其中字符的文本，或对含有文本的单元格的引用。
old_text：表示需要替换的旧文本。
new_text：用于替换old_text的文本。
instance_num：可选。用来指定要以new_text替换第几次出现的old_text。如果指定了instance_num，则只有满足要求的old_text被替换；否则会将text中出现的每一处old_text都更改为new_text。
如图，要去除A1单元格中多余的空格。公式为：
函数14：TRIM函数
函数功能：当从其他应用程序中获取带有不规则空格的文本时，可以使用TRIM函数，清除文本中的7位ASCII空格字符（值32）。
函数语法：TRIM(text)
参数解释：
text：表示需要删除其中空格的文本。
如图，删除A1中多余的空格。公式为：
=TRIM(A1)
函数15：SEARCH函数
函数功能：在第二个文本字符串中查找个文本字符串，并输出个文本字符串的起始位置的编号，该编号从第二个文本字符串的个字符算起。
函数语法：SEARCH(find_text,within_text,[start_num])
参数解释：
find_text：表示要查找的文本。
within_text：表示要在其中搜索find_text参数的值的文本。
start_num：可选。表示within_text参数中从之开始搜索的字符编号。
如图，从A2的市区信息中提取出区的信息。公式为：
常用的正则表达式
一、校验数字的表达式
1 数字：^[0-9]$
2 n位的数字：^d{n}$
3 至少n位的数字：^d{n,}$
4 m-n位的数字：^d{m,n}$
5 零和非零开头的数字：^(0|[1-9][0-9])$
6 非零开头的多带两位小数的数字：^([1-9][0-9])+(.[0-9]{1,2})?$
7 带1-2位小数的正数或负数：^(-)?d+(.d{1,2})?$
8 正数、负数、和小数：^(-|+)?d+(.d+)?$
9 有两位小数的正实数：^[0-9]+(.[0-9]{2})?$
10 有1~3位小数的正实数：^[0-9]+(.[0-9]{1,3})?$
11 非零的正整数：^[1-9]d$ 或 ^([1-9][0-9]){1,3}$ 或 ^+?[1-9][0-9]$
12 非零的负整数：^-[1-9][]0-9"$ 或 ^-[1-9]d$
13 非负整数：^d+$ 或 ^[1-9]d|0$
14 非正整数：^-[1-9]d|0$ 或 ^((-d+)|(0+))$
15 非负浮点数：^d+(.d+)?$ 或 ^[1-9]d.d|0.d[1-9]d|0?.0+|0$
16 非正浮点数：^((-d+(.d+)?)|(0+(.0+)?))$ 或 ^(-([1-9]d.d|0.d[1-9]d))|0?.0+|0$
17 正浮点数：^[1-9]d.d|0.d[1-9]d$ 或 ^(([0-9]+.[0-9][1-9][0-9])|([0-9][1-9][0-9].[0-9]+)|([0-9][1-9][0-9]))$
18 负浮点数：^-([1-9]d.d|0.d[1-9]d)$ 或 ^(-(([0-9]+.[0-9][1-9][0-9])|([0-9][1-9][0-9].[0-9]+)|([0-9][1-9][0-9])))$
19 浮点数：^(-?d+)(.d+)?$ 或 ^-?([1-9]d.d|0.d[1-9]d|0?.0+|0)$
二、校验字符的表达式
1 汉字：^[u4e00-u9fa5]{0,}$
2 英文和数字：^[A-Za-z0-9]+$ 或 ^[A-Za-z0-9]{4,40}$
3 长度为3-20的所有字符：^.{3,20}$
4 由26个英文字母组成的字符串：^[A-Za-z]+$
5 由26个大写英文字母组成的字符串：^[A-Z]+$
6 由26个小写英文字母组成的字符串：^[a-z]+$
7 由数字和26个英文字母组成的字符串：^[A-Za-z0-9]+$
8 由数字、26个英文字母或者下划线组成的字符串：^w+$ 或 ^w{3,20}$
9 中文、英文、数字包括下划线：^[u4E00-u9FA5A-Za-z0-9_]+$
10 中文、英文、数字但不包括下划线等符号：^[u4E00-u9FA5A-Za-z0-9]+$ 或 ^[u4E00-u9FA5A-Za-z0-9]{2,20}$
11 可以输入含有^%&',;=?$"等字符：[^%&',;=?$x22]+
12 禁止输入含有~的字符：[^~x22]+
三、特殊需求表达式
1 Email地址：^w+([-+.]w+)@w+([-.]w+).w+([-.]w+)$
2 域名：[a-zA-Z0-9][-a-zA-Z0-9]{0,62}(/.[a-zA-Z0-9][-a-zA-Z0-9]{0,62})+/.?
3 InternetURL：[a-zA-z]+://[^s] 或 ^
4 手机号码：^(13[0-9]|14[5|7]|15[0|1|2|3|5|6|7|8|9]|18[0|1|2|3|5|6|7|8|9])d{8}$
5 电话号码("XXX-XXXXXXX"、"XXXX-XXXXXXXX"、"XXX-XXXXXXX"、"XXX-XXXXXXXX"、"XXXXXXX"和"XXXXXXXX)：^((d{3,4}-)|d{3.4}-)?d{7,8}$
6 国内电话号码(0511-4405222、021-87888822)：d{3}-d{8}|d{4}-d{7}
7 号(15位、18位数字)：^d{15}|d{18}$
8 短号码(数字、字母x结尾)：^([0-9]){7,18}(x|X)?$ 或 ^d{8,18}|[0-9x]{8,18}|[0-9X]{8,18}?$
9 帐号是否合法(字母开头，允许5-16字节，允许字母数字下划线)：^[a-zA-Z][a-zA-Z0-9_]{4,15}$
10 密码(以字母开头，长度在6~18之间，只能包含字母、数字和下划线)：^[a-zA-Z]w{5,17}$
11 强密码(必须包含大小写字母和数字的组合，不能使用特殊字符，长度在8-10之间)：^(?=.d)(?=.[a-z])(?=.[A-Z]).{8,10}$
12 日期格式：^d{4}-d{1,2}-d{1,2}
13 一年的12个月(01～09和1～12)：^(0?[1-9]|1[0-2])$
14 一个月的31天(01～09和1～31)：^((0?[1-9])|((1|2)[0-9])|30|31)$
15 钱的输入格式：
16 1.有四种钱的表示形式我们可以接受:"10000.00" 和 "10,000.00", 和没有 "分" 的 "10000" 和 "10,000"：^[1-9][0-9]$
17 2.这表示任意一个不以0开头的数字,但是,这也意味着一个字符"0"不通过,所以我们采用下面的形式：^(0|[1-9][0-9])$
18 3.一个0或者一个不以0开头的数字.我们还可以允许开头有一个负号：^(0|-?[1-9][0-9])$
19 4.这表示一个0或者一个可能为负的开头不为0的数字.让用户以0开头好了.把负号的也去掉,因为钱总不能是负的吧.下面我们要加的是说明可能的小数部分：^[0-9]+(.[0-9]+)?$
20 5.必须说明的是,小数点后面至少应该有1位数,所以"10."是不通过的,但是 "10" 和 "10.2" 是通过的：^[0-9]+(.[0-9]{2})?$
21 6.这样我们规定小数点后面必须有两位,如果你认为太苛刻了,可以这样：^[0-9]+(.[0-9]{1,2})?$
22 7.这样就允许用户只写一位小数.下面我们该考虑数字中的逗号了,我们可以这样：^[0-9]{1,3}(,[0-9]{3})(.[0-9]{1,2})?$
23 8.1到3个数字,后面跟着任意个 逗号+3个数字,逗号成为可选,而不是必须：^([0-9]+|[0-9]{1,3}(,[0-9]{3}))(.[0-9]{1,2})?$
24 备注：这就是终结果了,别忘了"+"可以用""替代如果你觉得空字符串也可以接受的话(奇怪,为什么?)后,别忘了在用函数时去掉去掉那个反斜杠,一般的错误都在这里
25 xml文件：^([a-zA-Z]+-?)+[a-zA-Z0-9]+.[x|X][m|M][l|L]$
26 中文字符的正则表达式：[u4e00-u9fa5]
27 双字节字符：[^x00-xff] (包括汉字在内，可以用来计算字符串的长度(一个双字节字符长度计2，ASCII字符计1))
28 空白行的正则表达式：nsr (可以用来删除空白行)
29 HTML标记的正则表达式：<(S?)[^>]>.?|<.? /> (网上流传的版本太糟糕，上面这个也仅仅能部分，对于复杂的嵌套标记依旧无能为力)
30 首尾空白字符的正则表达式：^s|s$或(^s)|(s$) (可以用来删除行首行尾的空白字符(包括空格、制表符、换页符等等)，非常有用的表达式)
31 腾讯QQ号：[1-9][0-9]{4,} (腾讯QQ号从10000开始)
32 邮政编码：[1-9]d{5}(?!d) (邮政编码为6位数字)
33 IP地址：d+.d+.d+.d+ (提取IP地址时有用)
34 IP地址：((?:(?:25[0-5]|2[0-4]d|[01]?d?d).){3}(?:25[0-5]|2[0-4]d|[01]?d?d))
10分钟学会MID函数，提取你想要的所有字符！
提取括号里的内容？分离文字数字？分离省市区？一个MID函数3秒搞定！
先来看MID函数的基本用法：
函数定义： 返回文本字符串中从指定位置开始的特定数目的字符
使用格式：MID(text, start_num, num_chars)
参数定义：
Text：必需，包含要提取字符的文本字符串。
Start_num：必需，文本中要提取的个字符的位置，文本中个字符的 start_num 为 1，依此类推。
Num_chars ：必需，指定希望 MID 从文本中返回字符的个数。
说明：
如果 start_num 大于文本长度，则 MID 返回空文本 ("""")
如果 start_num 小于文本长度，但 start_num 加上 num_chars 超过了文本的长度，则 MID 只返回至多直到文本末尾的字符
如果 start_num 小于 1，则 MID 返回错误值 #VALUE!
如果 num_chars 是负数，则 MID 返回错误值 #VALUE!
光说不练把式，来看几个例子进一步理解：
例1：基本功能演示
ABC
1Excel精选技巧EMID(A1,1,1)
2Excel精选技巧ExMID(A2,1,2)
3Excel精选技巧xMID(A3,2,1)
C1公式功能为提取A1单元格的第1个位置开始的1个字符，得到结果E
C2公式功能为提取A2单元格的第1个位置开始的2个字符，得到结果Ex
C3公式功能为提取A1单元格的第2个位置开始的1个字符，得到结果x
例2：根据省份证号获取生日
AB
13607241990121222341990年12月12日
B1中的公式为：TEXT(MID(A1,7,8),"0000年00月00日")
其中，MID(A1,7,8)是提取A1单元格中的第7个位置开始8个字符，结果为19901212
再用TEXT 函数将其格式化为xxxx年xx月xx日
例3：文本拆分
ABCDEF
1EXCELEXCEL
在B1单元格中输入公式：MID($A$1,COLUMN(A:A),1)，再往右拖拽即可
其中，COLUMN(A:A)往右拖拽得到1、2、3、4、5，结合MID函数，巧妙将文本拆分开来
例4：提取括号内容
富士康精密电子（廊坊）有限公司廊坊
富士康精密电子（深圳）有限公司深圳
尚品宅配（广州）家居股份有限公司广州
公式为：MID(A1,FIND("（",A1)+1,FIND("）",A1)-FIND("（",A1)-1)
其中：FIND函数分别找出左右括号的位置，两个位置相减得到括号内文本的长度，即可用MID提取出左括号开始的N个字符
例5：分离省市区
广东省河源市龙川县广东省河源市龙川县
江西省赣州市上犹县江西省赣州市上犹县
公式为：
分离省：MID(A1,1,FIND("省",A1))
分离市：MID(A1,FIND("省",A1)+1,FIND("市",A1)-FIND("省",A1))
分离县：MID(A1,FIND("市",A1)+1,FIND("县",A1)-FIND("市",A1))
这3个公式以及例4所示的括号内容提取均是一脉相承，可用下列公式统一：
mid(String,find("首标识",String)+1,find(尾标识)-find(首标识))
用于提取两个标识符内的N个字符
例6：分离快递公司与快递单号
圆通12132342324241213234232424
申通1242435545466646412424355454666464
邮政EMS2542352523525325225423525235253252
公式为：MID(A1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A22),999)),LEN(A1))
并且用Ctrl+Shift+Enter输入
其工作原理是：找出所有数字的位置→取小数字位置为起始点→提取右侧数字
例7：提取文本中的连续数字
我的工号是7758258，来撩吧13988888888
公式为：
MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A28&1234567890)),2LEN(A1)-LENB(A1))
其原理是：String&“1234567890”得到：
我的工号是7758258，来撩吧1234567890
然后再分别查找0~9所在的位置，取小值即得到左边数字的位置
2LEN(A1)-LENB(A1)得到连续数字的长度
以左边数字位置为起始点，连续数字长度为字符长度，即可提取连续数字
关注技巧菌公众号和我一起学习EXCEL吧，有任何疑问，请留言和技巧菌一起探讨吧，若给您带来帮助记得收藏点赞转发哦！
在EXCEL中如何将MID函数提取的字符转换为日期格式？
哪有那么麻烦，使用TEXT函数公式
＝TEXT(你MID提取的8位数的单元格,“0000-00-00”就可以了，注意，跟“”需要在英文格式下输入，这是基于提取后的8位数的公式，如果没有提取例：120105197610120034(A1单元格)
使用TEXT公式
＝TEXT(MID(A1，7，8)，“0000-00-00”)即可，如上需要在英文下输入标点符号
怎样使MID函数运算后的数值格式保持为文本格式
A2单元格写入公式
="1-"&TEXT(ROW(A2),"000")
或=LEFT(A1,2)&TEXT(MID(A1,3,99)+1,"000")
=LEFT(A1,2)&TEXT(RIGHT(A1,LEN(A1)-2)+1,"000")
下拉公式
Excel中进行使用mid函数的作技巧
Mid是Microsoft Excel中的一个字符串函数，作用是从一个字符串中截取出指定数量的字符，如何使用mid函数。今天，我就教大家在Excel中进行使用mid函数的作技巧。
Excel中进行使用mid函数的作步骤 例子：学号中的第3位和第4位是班号，现在要用mid函数提取班号。
打开要编辑的函数。
点击B2单元格，在点击f。
函数中选择mid。
MID(text, start_num, num_chars)，根据这个，个框选择要使用的字符，第二个框左起第几位开始截取，第三个框从左起向右截取的长度是多少(用数字表达)。
点击确定即可，点击右下角，长按向下拉。