1、EXCEL 中的文本提取和替换今天我来说下文本包括概念使用过程中的拆合，讲之前我们来做个小试验 A1=“a”B1=“b“ 然后在 C1 中输入=a1=b1（意思是让 EXCEL 判断 A1 等于 B1 吗）？出乎了我们的意料，EXCEL 认为 A=a 是成立的,这个意味着：在平常不为人知的应用中，EXCEL 是模糊匹配的,也就是不管是大写还是小写，都认为是相等的。工作中如果遇到这样的数据你就会得出错误值。精确比较：比如在单纯的比较上，我们可以使用 EXACT 函数 大至说了，我们在处理文本数据时要考虑是否“精确处理”什么是文本：不可计算的字符是文本，就算是数字也可以是文本型 比如通过&文本连接
2、符可以让两个文本合成一个 对于两个文本型数字相加，可以得到数值型数字结果，记住了是直接相加，如果用 SUM 呢？ SUM 的参数是忽略文本的，包括文本型数字，好多朋友会经常问，为什么我的表格好多数字加不上去呢？很多原因是 SUM 函数中的引用的区域中，是文本型数字文本对 SUM 来说，就是 0，如何不改变数据源的情况下，让 SUM 求出文本数字的和？ 这个式子很奇怪，首先前面多了大括号、中间的区域还*1，为了便于理解我换个函数 如果用 sum 就要数组公式了，也就是在输入完=sum(1*a1:b1)后要按CTRL+SHIFT+回车键结束，三键结合会在公式的两端产生大括号要用 1*？意思是告诉
3、EXCEL，我的公式进行的是数组运算，仅仅是让文本型数字恢复为数值型*1，SUM 里面的参数如果是文本都视为 0，就得不到结果。文本型数字经过运算后可以得到可计算的数值型数字结果，如果不经过运算，比什么是数组运算：A1：B1*1 相当于 A1*1、B1*1 得到两个结果，由一个公式产生多个结果的我们称之为数组运算，像 1+1=2 一个式子得到一个结果普通公式，A1+1 也是，但A1：A10+2 相当于让 A1 至 A10 十个单元格的值都+2 产生十个新值。求和：单条件用 SUMIF，多条件用 SUMPRODUCT。现在我来说下我们平时常见的拆文本的函数，三剑客：LEFT、MID、RIGHT
4、LEFT 是左的意思，从指定文本的左边第一个字符开始，向右截出多少个字符。参数的第一个是指定的文本，第二个是截取长度。截取长度可以忽略（忽略的话，是截取一个） 所以如果只截取一个的话就不用写=LEFT（A1，1）了，呵呵 A1=123456 是数值型，通过=LEFT（A1，3），我要从 123456 的左边开始截取 3 个字符长度的文本，结果为 123。大家要注意，结果值为文本型在以后的后续处理中，一定要考虑这个因素。如果 123456，我要截取 LEFT（A1，20）会出现 123456 也就是截取长度超过字符数的时候，不会出错。下面我来说RIGHT，和 LEFT 一样，RIGHT 也是截取
5、，只是 RIGHT 是从右边往左截的 这个注意点和 LEFT 一样。接下来说 MID。MID 意思是中间即从中间开始截。事实上，MID 可以完成 LEFT 和 RIGHT 的功能，MID 有三个参数即 MID（文本，指定的文本截取开始位置，截取文本长度） 上面的公式是从 123456 的第 2 个字符位置开始截,往右截取三个字符长度，结果是 234。如何通过 MID 来摸拟 LEFT?LEFT(A1,3)用 MID 怎么整?其实就是 MID(A1,1,3)从左边第一个位置开始截 3 个字符长度 A1:A3 为长度不等的数字而我需要截取十位上面的数字，结果是 我们在考虑一个问题时要仔细观察数据中
6、的共同点，比如我们需要的结果都是十位，也就是倒数第 2 位，因为数据长度都不同,因此我们可以通过RIGHT 截取,让截取出来的结果值长度都一样 通过这一步可以让数据的长度一样,而且结果的位置在各数据也一样 解题的思路过程是把原本不统一的数据统一起来，作为在统一的数据上进行再处理。为什么我们输入公式经过拖动后,会得出正确结果?那是因为公式是建立在一定规律的基础之上，而这个规律是数据源共有的没有规律的数据源,公式也就谈不上了，而我们编写公式的难点就是如何在一堆看似无序的数据中找出规律来，通过该规律,构思其中的逻辑关系,再用函数拼接 在刚才的步骤上再进行一次判断，判断提取出来的5 的数字显示正确,否
7、则就是错误。注意：截取出来的是文本 TEXT 的第一参数如果是文本型数字是不需要转数值型的。如果是 IF 的话就必须转了，因为 IF 文本数字不能直接与数值型数字比较，TEXT 的第二参数就是格式代码,类同于自定义格式 首先公式的核心部分是 left(right(a2,2)也是思路的第一步了，通过截取出来的数字再进行比较得出最终的答案，条件格式符的编写规则,一定要记住,有时一个 TEXT 可以省却好多字符。 LEN 函数：LEN 是判断文本总共有多少个字符的；LENB 就是判断字节长度的函数 对 len 来说，不管是汉字还是字母数字都视为一个字符，但 lenb 不一样,后面的 b 是 byte
8、 字节的意思，一个汉字是两个字节,一个半角数字或字母是一个字节，所以 122 我是谁判断字节是 3 个数字+3 个汉字*2=9 个字节lenb 的魅力 如果通过 A 列的数据,通过函数来分开，能用公式拆,说明数据源有规律仔细观察数据源,我们发现尽管汉字的个数不同但都在右侧,左侧都是半角的字母或数字通过这个规律,我们可以有这样的一个思路 第一步判断字节数：=lenb(a2)；第二步判断字符数：=len(a2)；第三步字节数-字符数会得到什么结果?=lenb(a2)-len(a2) 首先字节数是=字符数的,字节数减去字符数是双字节字符的数量,本例中就是汉字的数量，汉字的个数都出来了,而且都在右侧；
9、第四步取出汉字=right(a2,lenb(a2)-len(a2) 这样,我们通过上面的分析,一步拆出汉字，大家再进一步思路,前面的如何提取?汉字个数都知道了，那不是汉字的数量怎么求?是不是字符数-汉字数?1 判断字节数 =lenb(a2)2 判断字符数 =len(a2)3 判断汉字数 =lenb(a2)-len(a2)4 判断非汉字数 =len(a2)-(lenb(a2)-len(a2) 公式简化下=2*len(a2)-lenb(a2)前面套用个 left 即可=left(a2,2*len(a2)-lenb(a2)通过分步拆解分析,我们一步步的进行函数拼装,最终完成了我们的要求。我们知道了
10、len+b 后变成了判断字节长了，同样 left 也可以有leftb、midb、rightb substitute 函数英文是替代的意思。这个函数的应用面很广，此函数有四个参数，如果用中文来描述应该是这样：substitute(原文本，原文本的部分字符串，所替换的新字符串，替换位置）替换位置这个参数可以省略，省略的情况下是所有的旧文本都替换成新文本 1、把上海替换成南京，如果用技巧查找替换即可，如果用函数substitute 就有用了 把 A1 中所有的上海改成南京。从这个例子上看，我们应该了解了。2、如果把文本中的第二个上海替换成南京呢？ 那就指定个替换位置。如果文本中有两个上海的话，那第二
11、个上海就替换了。这个是查找替换办不到的。3、如果把第一个上海清空呢？ 在替换的文本可以不写，你也可以写=SUBSTITUTE(A1,“上海“,“,1)，好多函数如果不写参数的话，会默认为 0。而这个函数是“”即空值：原因估计是文本函数吧。 比如 132312 十位数为 1 结果全替换掉值为 3232，4664 这是下一个的值 132312 右起第二个数字是 1，那么整个文本都把所有的 1 替换掉，第二个是 456654 右起第 2 个是 5，把 5 替换掉就是 4664，判断右起第二个数字，把文本中所有等于右起第二个数字替换掉 这个右起第二个是 1 就合替换掉变成 3232，如果我们确定是 1 的情况下=substitute(a1,1,)把 A1 中所有的 1 替换成无，那这个 1 怎么得来？之前的LEFT+RIGHT 得来的 通过 left+right 形式，可以取得右起第二个字符再进行替换即可。
