文本函数实战练习:REPLACE

2017-08-23 17:29 | 发布者: | 查看: 470 | 评论: 0

简介: 来一个文本函数讲解,这次上场的是REPLACE函数。 1、待处理数据和要求 如下图,A列为待处理文本,数据特征是:文本和数字混杂,最后出现的数字后面都没有单位。 数据处理要求:要求再C列返回A列文本添加上B列对应单位后的结果。 例如,A2单元格文本为“买了2.4猪肉”,B2单元格的单位为“斤”,要求在C列返回“买了2.4斤猪肉”。 2、思路分析 按照A列数据特征,可以用下列的思路完成: 1)首先利用函数公式获取A列每个单元格最后一个数字出现的位置,假设是X; 2)用文本函数从A列单元格文本第一个字符到第X个字符提取出来,然后&B列单元格的单位文本,然后再&从A列单元格文本第X+1个文本起999个字符。 上述思路也是很清晰的思路,实现起来也很容易,就是公式稍微会长一些,引用数字出现位置X判断的函数会引用两遍。 而纯粹完成示例数据的操作要求,可以用REPLACE函数来完成。 3、REPLACE函数的用法说明 REPLACE函数的基本用法如下: REPLACE(old_text,start…

来一个文本函数讲解,这次上场的是REPLACE函数。

1、待处理数据和要求

如下图,A列为待处理文本,数据特征是:文本和数字混杂,最后出现的数字后面都没有单位。

数据处理要求:要求再C列返回A列文本添加上B列对应单位后的结果。

例如,A2单元格文本为“买了2.4猪肉”,B2单元格的单位为“斤”,要求在C列返回“买了2.4斤猪肉”。

2、思路分析

按照A列数据特征,可以用下列的思路完成:

1)首先利用函数公式获取A列每个单元格最后一个数字出现的位置,假设是X;

2)用文本函数从A列单元格文本第一个字符到第X个字符提取出来,然后&B列单元格的单位文本,然后再&从A列单元格文本第X+1个文本起999个字符。

上述思路也是很清晰的思路,实现起来也很容易,就是公式稍微会长一些,引用数字出现位置X判断的函数会引用两遍。

而纯粹完成示例数据的操作要求,可以用REPLACE函数来完成。

3、REPLACE函数的用法说明

REPLACE函数的基本用法如下:

REPLACE(old_text,start_num,num_chars,new_text)

即“

REPLACE(包含要替换的文本的文本,起始位置,替换几个字符,替换成的文本)

例如:

=REPLACE(“我是中国人”,3,2,””)

返回结果是“我是人”。

函数的意思是:从”我是中国人”这个文本中的第3个字符(也就是“中”)起,将连续的2个字符(也就是“中国”),替换成空(最后一个参数文本)。

再比如:

=REPLACE(“我是中国人”,3,3,”大英雄”)

返回结果是“我是大英雄”。

函数的意思是:从”我是中国人”这个文本中的第3个字符(也就是“中”)起,将连续的3个字符(也就是“中国人”),替换成“大英雄”(最后一个参数文本)。

特别的:

=REPLACE(“我是中国人”,5,0,”好”)

返回结果是“我是中国好人”。

函数的意思是:从“我是中国人”这个文本中的第5个字符起,将0个字符替换成“好”。相当于在原文本第5个字符之前插入了一个“好”字(第三参数文本)。

利用函数的这个特点,我们可以完成前面提出的数据处理要求:将B列的单位插入到A列文本中。

4、输入函数公式

在C2单元格输入下列函数公式:

=REPLACE(A2,LOOKUP(,-MID(A2,ROW($1:98),1),ROW($2:99)),,B2)

然后下拉,则返回了期望的结果。

5、解题思路和函数用法解释

以A2单元格为例说明。

利用REPLACE函数的特点,判断A2文本中最后一个数字出现的位置,然后将这个数字出现位置后的字符开始,将0个文本替换成B2单元格的单位即可。

简单说,最外层的REPLACE函数用法如下:

=REPLACE(A2,最后一个数字出现的位置+1,,B2)

所以,现在关键是获取最后一个数字出现的位置,然后再+1。

返回这个结果方法很多,本次讲LOOKUP函数的用法。

=LOOKUP(,-MID(A2,ROW($1:98),1),ROW($2:99))

MID(A2,ROW($1:98),1)这部分是分别从A2文本中的第1~98个字符起,取一个字符,形成数组,返回结果如下:

={“买”;”了”;”2″;”.”;”4″;”猪”;”肉”;……;””}

然后加上一个减号,将文本数字转化成数字(相当于加了一个负号,变成了负数),文本返回错误值:

-MID(A2,ROW($1:98),1)

返回结果如下:

{#VALUE!;#VALUE!;-2;#VALUE!;-4;#VALUE!;……;#VALUE!}

然后=LOOKUP(,-MID(A2,ROW($1:98),1),ROW($2:99))的意思是:

在-MID函数返回的数组中查找小于等于LOOKUP第一参数0的最后一个值对应的位置,然后提取第3参数中对应位置的数据。

因为-MID部分返回的最后一个数字是-4,位置是第5个,所以再加1就6,也就是REPLACE函数的第2参数的值。

如果写成:=LOOKUP(,-MID(A2,ROW($1:98),1),ROW($1:98))函数会返回5,也就是最后一个数字4的位置,在这个结果上+1也可以,当然我们也可以把LOOKUP第三参数先加上1,于是就有:

=LOOKUP(,-MID(A2,ROW($1:98),1),ROW($2:99))

这个部分就返回了A2单元格最后一个数字出现位置+1的结果。

这样,最外层再嵌套REPLACE函数就可以达到目的了。

=REPLACE(A2,LOOKUP(,-MID(A2,ROW($1:98),1),ROW($2:99)),,B2)

会员评论

相关分类

推荐阅读

返回顶部