EXCEL中的文本提取和替换

EXCEL中的文本提取和替换

今天我来说下文本包括概念使用过程中的拆合，讲之前我们来做个小试验A1=\

B1=\

然后在C1中输入=a1=b1（意思是让EXCEL判断A1等于B1吗）？出乎了我们的意料，EXCEL认为A=a 是成立的,这个意味着：在平常不为人知的应用中，EXCEL是‘模糊’匹配的,也就是不管是大写还是小写，都认为是相等的。工作中如果遇到这样的数据你就会得出错误值。精确比较：比如在单纯的比较上，我

们可以使用EXACT函数

大至说了，我们在处理文本数据时要考虑是否“精确处理”什么是文本：不可计算的字符是文本，就算是数字也可以是文本型

比如通过&文本连接符可以让两个文本合成一个

对于两个文本型数字相加，可以得到数值型数字结果，记住了是直接相加，

如果用SUM呢？

SUM的参数是忽略文本的，包括文本型数字，好多朋友会经常问，为什么我的表格好多数字加不上去呢？很多原因是SUM函数中的引用的区域中，是文本型数字

文本对SUM来说，就是0，如何不改变数据源的情况下，让SUM求出文本数字的

和？

这个式子很奇怪，首先前面多了大括号、中间的区域还*1，为了便于理解

我换个函数

如果用sum就要数组公式了，也就是在输入完=sum(1*a1:b1)后要按

CTRL+SHIFT+回车键结束，三键结合会在公式的两端产生大括号要用1*？意思是告诉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

LEFT是左的意思，从指定文本的左边第一个字符开始，向右截出多少个字符。参数的第一个是指定的文本，第二个是截取长度。截取长度可以忽略（忽略的话，

是截取一个）

所以如果只截取一个的话就不用写=LEFT（A1，1）了，呵呵

A1=123456是数值型，通过=LEFT（A1，3），我要从123456的左边开始截取3个字符长度的文本，结果为123。大家要注意，结果值为文本型在以后的后续处理中，一定要考虑这个因素。如果123456，我要截取LEFT（A1，20）会出现123456也就是截取长度超过字符数的时候，不会出错。下面我来说RIGHT，和LEFT一样，RIGHT也是截取，只是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为长度不等的数字而我需要截取十位上面的数字，结果是

我们在考虑一个问题时要仔细观察数据中的共同点，比如我们需要的结果都是十位，也就是倒数第2位，因为数据长度都不同,因此我们可以通过RIGHT截取,让截取出来的结果值长度都一样

通过这一步可以让数据的长度一样,而且结果的位置在各数据也一样

解题的思路过程是把原本不统一的数据统一起来，作为在统一的数据上进行再处理。为什么我们输入公式经过拖动后,会得出正确结果?那是因为公式是建立在一定规律的基础之上，而这个规律是数据源共有的没有规律的数据源,公式也就谈不上了，而我们编写公式的难点就是如何在一堆看似无序的数据中找出规

律来，通过该规律,构思其中的逻辑关系,再用函数拼接

在刚才的步骤上再进行一次判断，判断提取出来的>5的数字显示正确,否则就是错误。注意：截取出来的是文本

TEXT的第一参数如果是文本型数字是不需要转数值型的。如果是IF的话就必须转了，因为IF文本数字不能直接与数值型数字比较，TEXT的第二参数就是格式代码,类同于自定义格式

首先公式的核心部分是left(right(a2,2))也是思路的第一步了，通过截取出来的数字再进行比较得出最终的答案，条件格式符的编写规则,一定要记住,有时一个TEXT可以省却好多字符。

LEN函数：LEN是判断文本总共有多少个字符的；LENB就是判断字节长度

的函数

对len来说，不管是汉字还是字母数字都视为一个字符，但lenb不一样,后面的b是byte字节的意思，一个汉字是两个字节,一个半角数字或字母是一个字节，所以122我是谁判断字节是3个数字+3个汉字*2=9个字节

lenb的魅力

如果通过A列的数据,通过函数来分开，能用公式拆,说明数据源有规律仔细观察数据源,我们发现尽管汉字的个数不同但都在右侧,左侧都是半角的字母或数字通过这个规律,我们可以有这样的一个思路

第一步判断字节数：=lenb(a2)；第二步判断字符数：=len(a2)；第三步字节数-字符数会得到什么结果?=lenb(a2)-len(a2)

首先字节数是>=字符数的,字节数减去字符数是双字节字符的数量,本例中就是汉字的数量，汉字的个数都出来了,而且都在右侧；第四步取出汉字=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))

通过分步拆解分析,我们一步步的进行函数拼装,最终完成了我们的要求。我们知道了len+b后变成了判断字节长了，同样left也可以有leftb、midb、rightb

substitute函数英文是替代的意思。这个函数的应用面很广，此函数有四个参数，如果用中文来描述应该是这样：substitute(原文本，原文本的部分字符串，所替换的新字符串，替换位置）替换位置这个参数可以省略，省略的情况

下是所有的旧文本都替换成新文本

1、把上海替换成南京，如果用技巧查找替换即可，如果用函数substitute就有用了

把A1中所有的上海改成南京。从这个例子上看，我们应该了解了。2、如果把文本中的第二个上海替换成南京呢？

那就指定个替换位置。如果文本中有两个上海的话，那第二个上海就替换了。这个是查找替换办不到的。3、如果把第一个上海清空呢？

在替换的文本可以不写，你也可以写=SUBSTITUTE(A1,\上海\，好多函数如果不写参数的话，会默认为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形式，可以取得右起第二个字符再进行替换即可。

本文来源：https://www.bwwdw.com/article/td68.html