今天讲的是单元格内替换相关的函数。
LEN / LENB
https://support.microsoft.com/zh-cn/office/len%e3%80%81lenb-%e5%87%bd%e6%95%b0-29236f94-cedc-429d-affd-b5e33d2c67cb?ui=zh-cn&rs=zh-cn&ad=cn
语法:LEN(text) / LENB(text)
这个是计算文本长度的。上次讲过FIND,FINDB, SEARCH, SEARCHB。所以LEN和LENB也是一样的,B是计算字节。
下图中可以看到,英文一个字母是一个字节,中文一个汉字或者全角符号是2个字节,所以LENB只有在有中文或者全角符号的情况下才和LEN的结果有区别。
很少单用这个函数,一般都是和其它函数配合使用的。我能想到单纯用这个函数的地方就是找出有多少汉字了。
比如下图在B1中输入公式:=LENB(A1)-LEN(A1)
这样就可以快速得出A1单元格有2个汉字。
SUBSTITUTE
https://support.microsoft.com/zh-cn/office/substitute-%e5%87%bd%e6%95%b0-6434944e-a904-4336-a9b0-1e58df3bc332?ui=zh-cn&rs=zh-cn&ad=cn
语法: 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 被替换。否则,文本中出现的所有 old_text 都会更改为 new_text。
直接来看例子:
在B1输入公式:=SUBSTITUTE(A1,"a","c")
结果就是所有的a都替换成了c
输入公式:=SUBSTITUTE(A1,"a","c",1)
结果就是第一个a换成了c
同理把第四参数换成2,3就是把第二个和第三个a替换成c
有两点需要稍微注意一下:
1. 找不到就直接引用原单元格内容,不变化
比如把d换成c
比如把第四个a换成c
2. 对大小写有严格要求,复习一下还记得FIND 和 SERACH哪个是区分大小写的?
刚刚说了LEN不太单独使用,LEN和SUBSTITUE就可以组合。
比如说来计算一下有多少种水果。
公式:=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1
公式解读:
1. LEN(A1),计算原单元格字符长度。
2. LEN(SUBSTITUTE(A1,",","")),把分隔符逗号换成空值(也就是去掉逗号),再计算一次不包含逗号的长度。
3. 两者相减,算出逗号个数
4. 最后因为两个水果用一个逗号隔开,三个水果用两个逗号隔开,所以逗号个数比水果个数要少1,所以+1来得到正确的结果
REPLACE/ REPLACEB
https://support.microsoft.com/zh-cn/office/replace%e3%80%81replaceb-%e5%87%bd%e6%95%b0-8d799074-2425-4a8a-84bc-82472868878a?ui=zh-cn&rs=zh-cn&ad=cn
语法:
REPLACE(old_text,start_num, num_chars, new_text)
REPLACEB(old_text,start_num, num_bytes, new_text)
◾ old_text 必需。要替换其部分字符的文本。
◾ start_num 必需。old_text 中要替换为 new_text 的字符位置。
◾ num_chars 必需。old_text 中希望 REPLACE 使用 new_text 来进行替换的字符数。
◾ Num_bytes 必需。old_text 中希望 REPLACEB 使用 new_text 来进行替换的字节数。
◾ new_text 必需。将替换 old_text 中字符的文本。
下面来看例子。
讲“-”换成“/”
公式:=REPLACE(A1,2,1,"/")
参数:
第一参数:源单元格,A1
第二参数:从第几位开始替换,”-“是第二位,所以是2
第三参数:替换掉几个字符,“-“就占了一个字符,所以是1,
第四参数:替换成的内容,就是”/ ”
将”-“后的内容删除。
公式:=REPLACE(A1,2,1000,"")
参数:
第一参数:源单元格,A1
第二参数:从第几位开始替换,”-“是第二位,所以是2
第三参数:替换掉几个字符,我后面全都要替换掉,所以可以直接写个大点的数字,我这边就写了1000
第四参数:空值就是双引号””
然后顺带说下REPLACEB,反正之前说了只要结尾带B的都是和汉字全角字符有关的。
来看下面这个例子
中文汉字你好,占了四个字节,你(1,2),好(3,4)
我们看到替换第一个字节和替换第二个字节是不一样的。我们替换成数字,由于数字只占1个字节。所以替换第一个字节的时候,就变成"1空格好",替换第二个字节的时候就变成"空格1好"),不够的用空格来补齐字节的位数。
这个知道一下就好,实际用到的情况不多。
我们再来回过头看一下前面那个例子“a-b”,有强迫症的我总觉得不舒服,为什么呢,因为2是我数出来的。如果”-“位置不确定,数据量又很大你肯定不能一个一个去数。
这里我们可以用到上次的学过的函数FIND。
公式:=REPLACE(A1,FIND("-",A1),1,"/")
第二参数都用FIND函数来找到,这样是不是方便了很多?