技巧篇：Excel文本处理全攻略

技巧一：认识文本

1、Excel单元格的数据类型主要有：文本、数值、日期。

2、默认情况下，文本型数据左对齐显示；数值和日期右对齐显示；而错误值和逻辑值居中显示。

3、当数值以文本格式存储时，在单元格左上角会出现一个绿色小三角标记。

不同数据类型默认对齐方式

技巧二：文本型数字转换为数值

通过减负运算将文本型数值转换为真正的数值。

转换方法：假定A1为文本型数值“1”，公式 = --A1 (减负运算，也就是前置两个减号)

技巧三：文本的合并

方法1：使用 concatenate函数处理，公式=concatenate(A2,B2)，参数不能超过30个。

方法2：使用 ”&” 符号实现，公式=A2&B2

技巧四：文本的比较

方法1：使用比较运算符和IF函数

使用文本比较进行数据标识

公式=IF(A2<>B2,”不相同”, A2) 通过IF函数和不等于号判断B1和B2是否相同，不同返回B2值，相同显示A1列源数据。

方法2：使用EXACT函数判断

比较运算符并不区分字母大小写。如果使用方法1，比较Good 和good结果返回TRUE。公式=EXACT(A2,B2)

技巧五：大小写字母转换

LOWER函数：将所有字母转换为小写字母；UPPER函数，将所有字母转换为大写字母；PROPER函数，将所有单词转换为首字母大写的格式；

大小写字母转换示例图

技巧六：全角半角字符转换

WIDECHAR函数：将所有字符转换为全角字符；ASC函数，将所有字符转换为半角字符；公式=ASC("我喜欢头条办公室 X i a o c u i ") 结果为：我喜欢头条办公室Xiaocui

技巧七：字符串的提取

LEFT(text，字符数)：从字符串最左端位置提取指定数量的字符。

MID(text,开始位，字符数)：从字符串中间的任意位置提取指定数量的字符;

RIGHT(text,字符数)：从字符串的最右端位置提取指定数量的字符；

LEN(text)：返回字符串中的字符个数，即字符串的长度。

[what]经常用到的一个技巧1：提取A1单元格中第5个字符以后的所有字符？公式如下：

公式=MID(A1,5,LEN(A1)-5+1）；

[what]技巧2：关于身份证号提取的方法，参见我的文章：技巧篇：EXCEL身份证号全攻略，这里不再赘述。

技巧八：文本的查找

EXCEL中查找一个字符串在另一个字符串中的位置，主要用FIND、SEARCH函数，这两个函数使用方法基本类似，但是也有几点明显的区别。

fiind与search函数的区别

1. FIND函数用于在字符串中查找指定字符（串），并返回此字符（串）的起始位置的值。有三个参数，第一参数是要查找的字符串，第二参数是被查找的字符，第三参数是指示开始查找的位置，第三参数可以省略，省略后表示从第1个字符开始查找，多数情况下第三参数是省略的。如：=FIND("X",A2)，表示在A2单元格中查找字母X的位置。

2. SEARCH函数的查找功能与FIND函数类似，也是有三个参数，参数的含义也相同。但要注意，SEARCH函数是不区分大小写字母的，先找到哪个就返回哪个的位置，如同样的内容：=SEARCH("X",A2)，返回的值为8，而：=FIND("X",A2)返回的值为15，这是因为后者先找到了小写字母“X”所在的位置，就不再往下查找“X”所在位置，而前者find是区分大小写字母的，只有找到“X”才返回其位置值。

3. 除了不区分大小写外，另外一个区别就是，SEARCH函数可以使用通配符，“?”代表一个任意字符：=SEARCH("f?c",A2)

“*”代表任意个任意字符，如：=SEARCH("f*c",A2)，注意比较下，同样的内容，同样的公式，使用“?”和“*”返回的结果可能不同，使用“*”时，返回的是第一个“f”所在位置值，而使用“?”时，返回的是第二个“f”所在位置值，因为后者只能代表一个字符。

search函数适用于模糊查找

总结：查找位置一般情况下使用FIND函数较好，只有当查找内容不区分大小写以及需要使用通配符时，才需要使用SEARCH函数进行模糊查找。

技巧九：文本的格式化

Excel中自定义数字格式功能可以将单元格中的数值显示为自定义格式，Text函数也具有类似的功能，可以将数值转换为按照指定数字格式所表示的文本。

text的格式代码分为四个条件区段，默认情况下，为：[>0];[<0];[=0];[文本]

案例 1 使用公式对字符格式化

使用公式为：=TEXT(TEXT(B2,"[>=90]优秀;[>=80]良好；0"),"[>=70]一般；[>=60]及格;不及格")

案例2 使用text函数设置不同的数字格式

几种常用的符号如下：0：数字占位符，数量不足的需要补零，如"000"则返回的整数不能小于三位数子。#：数字占位符，数量不足的无须补齐；@：文本占位符，连续使用表示重复显示文本。！或者，强制显示符。

常用的数字格式例子

以上是几个非常重要的数字格式转换例子，也比较常用！

案例3 数值和中文数字之间的转换

在学校教务工作中，学生的毕业证书日期落款一般要求是中文数字。以下例子就是典型的应用。使用公式=TEXT(B21,"[DBnum1]yyyy年m月d日")

数值转换为中文数字

同时，Excel还提供了一个隐藏的转换中文数字的函数——Numberstring函数，该函数也可以实现上面的转换，如公式修改为：公式=NUMBERSTRING(A2,3)

numberstring函数的用法

技巧十：数字转换为中文大写金额

EXCEL有内置的数字大小写转换功能，在A1单元格输入一个数字，然后选择“设置单元格格式”，选择“特殊”，再选择右边的“中文大写数字”，就可以直接将小写的阿拉伯数字转换为大写的中文数字了。

数字转大写金额

不过这种方法只适合纯数字的转换，涉及到货币数字的转换就用不上了，因为货币有元角分，而EXCEL的这个内置功能没办法完美转换，这时候就需要用到函数了。公式比较长，不用理解，小崔提供现成的转换公式，可完美的将小写的阿拉伯数字转换为大写中文金额。用的时候直接复制即可：

公式=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[dbnum2]0角0分;;整"),),"零角",IF(A2^2<1,,"零")),"零分","整")