Excel Replace函数和ReplaceB函数用法的10个实例,含从指定字符替换到末尾、位数升级和句首字母大写

亮术网 2019-10-05 本网原创

在 Excel 中,Replace函数和ReplaceB函数都用于从特定位置开始用指定字符替换掉指定数目的字符;但两个函数有区别:替换时,Replace函数以字符计,它把半角(如“数字和字母”)和全角(如“汉字”)字符都计作一个字符;ReplaceB函数以字节计,它把半角计作一个字节、全角计作两个字节。另外,Substitute函数与Replace函数功能相近,Substitute函数是用一个字符替换另一个字符,不存在从第几位开始替换。

Replace函数和ReplaceB函数常与 Find、Left、Upper、Lower 和 Rept函数组合使用,例如 Replace + Find + Rept 组合实现从任意指定字符开始替换到末尾,Replace + Find + Find 组合实现替换任意两个字符之间的字符串,Replace + Upper + Left 组合实现句首字母大写。

 

 

一、Excel Replace函数和ReplaceB函数的语法

1、Replace函数表达式:REPLACE(Old_Text, Start_Num, Num_Chars, New_Text)

中文表达式:Replace(要替换部分字符的文本, 开始替换位置,替换字符数,替换字符)

 

2、ReplaceB函数表达式:REPLACEB(Old_Text, Start_Num, Num_Bytes, New_Text)

中文表达式:ReplaceB(要替换部分字符的文本, 开始替换位置,替换字节数,替换字符)

 

3、说明:

Replace函数和ReplaceB函数都用于从指定位置起替换指定数目的字符,但它们有区别:替换字符时,Replace函数以字符计,它把全角(如“汉字”)和半角(如“数字和字母”)字符都计作 1 个字符;ReplaceB函数以字节计,它把全角字符计作 2 个字节、半角字符计作 1 个字节。

 

 

二、Excel Replace函数的使用方法及实例

(一)替换文本中杂乱字符的实例

1、假如要把“长袖965grf609白衬衫”中的一串字母和数字替换掉。双击 B1 单元格,把公式 =REPLACE(A1,3,9,"") 复制到 B1,按回车,返回“长袖白衬衫”;操作过程步骤,如图1所示:

 

图1

2、公式说明:

公式 =REPLACE(A1,3,9,"") 中,A1 为要替换部分字符的文本,3 为开始替换位置,9 为替换字符数,空文本 "" 为要替换为的字符,公式的意思是:把 A1 中 965grf609 用 "" 替换掉。

 

(二)同时替换数字、字母和汉字的实例

1、假如要把“Excel 05g/c和教程”中的“05g/c和”替换为“函数”。双击 B1 单元格,把公式 =REPLACE(A1,7,6,"函数") 复制到 B1,按回车,返回“Excel 函数教程”;操作过程步骤,如图2所示:

 

图2

2、公式说明:

A、公式 =REPLACE(A1,7,6,"函数") 的意思是,从 A1 的文本第 7 个字符开始替换 6 个字符,且用“函数”二字替换,即 6 字符被替换为“函数”。

B、被替换的 6 个字符“05g/c和”包含数字、字母、斜杠和汉字,它们的总字符数为 6,说明 Replace 函数把全角和半角字符都算一个字符。

 

(三)把数字(如“手机号码”)后4位替换为 * 的实例

1、双击 B1 单元格,把公式 =REPLACE(A1,8,4,"****") 复制到 B1,按回车,返回“1986391****”;选中 B1,把鼠标移到 B1 右下角的单元格填充柄上,鼠标变为加号后,双击左键,则剩余数字的后四位也被替换为 *;操作过程步骤,如图3所示:

 

图3

2、公式说明:

公式 =REPLACE(A1,8,4,"****") 的意思是,把 A1 中的数字从第 8 位开始用 **** 替换且仅替换四位。

 

(四)替换任意指定字符后的所有字符实例

1、如果要把“Excel教程1dg9sh9gfd863rt91g788d587gf2kd0f2”中“教程”后的所有字符替换掉。双击 B1 单元格,把公式 =REPLACE(A1,8,32699,"") 复制到 B1,按回车,返回“Excel教程”;操作过程步骤,如图4所示:

 

图4

2、公式说明:

公式 =REPLACE(A1,8,32699,"") 中,32699 是Replace函数允许的最大字符数,公式的意思是:把 A1 的文本从第 8 位开始(即从“程”后的第一个字符起),用 "" 替换 32699 个字符。

 

 

三、Excel ReplaceB函数的使用方法及实例

(一)替换数字、字母的实例

1、假如要把“890675gk963”中的“gk963”替换为 0。双击 B1 单元格,把公式 =REPLACEB(A1,7,5,"00000") 复制到 B1,按回车,返回 89067500000;操作过程步骤,如图5所示:

图5

 2、公式说明:

公式 =REPLACEB(A1,7,5,"00000") 中,A1 为要替换部分字符的文本,7 为开始替换的位置,5 为替换字节数,00000 为要替换为的文本,公式的意思是:从 A1 中第 7 个字符开始,替换 5 个字符为 5 个 0。

 

(二)替换半角字符(数字、字母)和全角字符(汉字)的实例

1、同样以把“Excel 05g/c和教程”中的“05g/c和”替换为“函数”为例。双击 B1 单元格,把公式 =REPLACEB(A1,7,7,"函数") 复制到B1,按回车,返回“Excel 函数教程”;操作过程步骤,如图6所示:

 

图6

2、公式说明:

A、公式 =REPLACEB(A1,7,7,"函数") 的意思是:从 A1 中第 7 个字符开始替换 7 个字节且用“函数”替换。

B、字符串“05g/c和”共有 6 个字符,其中半角字符 5 个、全角字符 1 个,即只有“和”为全角字符,它占两个字节,因此“05g/c和”共 7 个字节;ReplaceB函数把全角计为 2 个字节,所以公式中的替换字节数要写 7,而 Replace 函数把全角字符计为 1 个字符,因此 Replace 的公式中写 6,即 =REPLACEB(A1,7,6,"函数") 。

 

 

四、Excel Replace函数和ReplaceB函数的应用实例

(一)Replace + Find + Rept函数组合实现从任意指定字符开始替换

1、假如要把 A 列的文本 k 及后面的数字全替换为 0。双击 B1 单元格,把公式 =REPLACE(A1,FIND("k",A1),32699,REPT("0",3)) 复制到 B1,按回车,返回 890675000;选中 B1,用双击单元格填充柄的方法替换剩余的单元格内容;操作过程步骤,如图7所示:

图7

 

2、公式 =REPLACE(A1,FIND("k",A1),32699,REPT("0",3)) 说明:

A、FIND("k",A1) 用于返回字母 k 在 A1 中的位置,结果为 7;Find函数把全角和半角字符都计作一个字符。

B、REPT("0",3) 用于把 0 重复 3 次,结果为 000,Rept函数的作用是把任意指定字符重复指定次数,当要把某个字符或词组重复多次时常常用该函数。

C、则公式变为 =REPLACE(A1,7,32699,"000"),最后用 Replace 从 A1 中第 7 个字符开始用 000 替换 32699 个字符,32699 在上文已经解释过。

另外,用 ReplaceB + FindB + Rept 也能实现同样功能,公式可以这样写:=REPLACEB(A1,FINDB("k",A1),32699,REPT("0",3)),值得注意的是:全角字符占 2 个字节。

 

(二)Replace + Find + Find函数组合实现替换任意两个字符之间的字符串

1、假如要替把文本“890675[k963*kj3u3tirt4u0gj7G]697896”的括号 [] 内的字符串及括号都替换掉。双击 B2 单元格,把公式 =REPLACE(A1,FIND("[",A1),FIND("]",A1)-FIND("[",A1)+1,"") 复制到 A2,按回车,返回被替换掉的文本;操作过程步骤,如图8所示:

图8

 

2、公式 =REPLACE(A1,FIND("[",A1),FIND("]",A1)-FIND("[",A1)+1,"") 说明:

A、FIND("[",A1) 返回左括号 [ 在 A1 中的位置,结果为 7;FIND("]",A1) 返回右括号 ] 在 A1 中的位置,结果为 29。

B、FIND("]",A1)-FIND("[",A1)+1 计算要截取的字符串长度,用后一个括号 ] 的位置减前一个括号 [ 的位置再加 1,即 29 - 7 + 1,结果为 23,恰好为从左括号 [ 到右括号 ] 的字符数,并且包含两个括号。

C、则公式变为 =REPLACE(A1,7,23,""),最后用 Replace 从 A1 的第 7 个字符开始用 "" 替换 23 个字符。

 

(三)用 Replace 函数把电话号码升级 1 位

1、假如要把电话号码由 7 位升级到 8 位。双击 B2 单元格,把公式 =REPLACE(A2,8,1,3) 复制到 B2,按回车,返回升级 1 位的电话号码;用双击单元格填充柄的方法把剩余的号码升级;操作过程步骤,如图9所示:

 

图9

2、公式 =REPLACE(A2,8,1,3) 说明:

A2 中的电话号码只有 7 位,但公式 =REPLACE(A2,8,1,3) 可以从第 8 位开始替换,只需替换 1 位,则电话号码升级到 8 位;如果要批量生成,最后 1 位只能用同一数字替换。

 

(四)Replace + Upper + Left函数组合实现句首字母大写

1、假如要把 A 列的英文句首字母大写。双击 B2 单元格,把公式 =REPLACE(A2,1,1,UPPER(LEFT(A2,1))) 复制到 B2,按回车,返回 A2 中的句首字母由小写转为大写的句子;再用双击单元格填充柄的办法把剩余的英文句首字母大写;操作过程步骤,如图10所示:

 

图10

 

2、公式 =REPLACE(A2,1,1,UPPER(LEFT(A2,1))) 说明:

A、LEFT(A2,1) 用于从 A2 的句子左边第一位开始截取 1 个字母,返回结果为 e。Upper函数用于把小写字母转为大写字母,则 UPPER(e) 返回 E。

B、则公式变为 =REPLACE(A2,1,1,"E"),最后用 Replace 把 A2 中的句子从第 1 位开始用 E 替换 1 个字母,则小写 e 被替换为大写 E,也就实现句首字母大写。