Excel 字符串中的查找和替换:REPLACE函数

REPLACE函数用于将一个字符串中的部分字符用另一个字符串替换,其语法是REPLACE(ole_text,start_num,num_chars,new_text)。其中,参数old_text为要将字符进行替换的文本;参数start_num为要替换new_text中字符在old_text中的位置;参数num_chars为要从old_text中替换的字符个数;参数new_text是来对old_text中指定字符串进行替换的字符串,下面通过实例具体讲解该函数的操作技巧。

步骤1:在实际应用过程中,财务人员需要使用REPLACE函数,将目录与页面之间的符号替换成“–”符号,按照要求输入本例的原始数据,如图11-55所示。

步骤2:选中A8单元格,在编辑栏中输入“=REPLACE(A2,11,10,”–“)”,然后按Enter键,替换文本,结果如图11-56所示。

图11-55 原始数据

图11-56 替换部分文本

步骤3:选中A8单元格,移动鼠标指针至单元格A12右下角处,当鼠标指针变成黑色“+”时,按住鼠标左键并拖动鼠标指针单元格A12,即替换所有文本,如图11-57所示。

图11-57 替换所有文本

注意:此函数适用于替换部分指定的字符串。替换与被替换的字符串属于同一工作表内。

Excel 实现文本替换:SUBSTITUTE函数

SUBSTITUTE函数用于将字符串中的部分字符串用新字符串替换,其语法是SUBSTITUTE(text,old_text,new_text,instance_num)。其中,参数text是包含要替换字符的字符串,或是对文本单元格引用;参数old_text是要被替换的字符串,如果原有字符串中的大小写不等于新字符串中的大小写,将不进行替换;参数new_text用于替换old_text的新字符串;参数instance_num是表示指定的字符串old_text在源字符串中出现几次,则用本参数指定要替换第几个,如果省略则全部替换,下面通过实例具体讲解该函数的操作技巧。

步骤1:某图书作者在编写例题步骤文本中含有“Enter”字符串,使用SUBSTITUTE函数,将“Enter”字符串替换成“回车”字符串,按照要求输入本例的原始数据,如图11-53所示。

图11-53 原始数据

步骤2:选中合并后的B11单元格,在编辑栏中输入“=SUBSTITUTE(B2,”Enter”,”回车”)”公式,单击Enter键,替换指定文本,如图11-54所示。

图11-54 替换后的文本

注意:此函数适用于将部分字符串以新字符串替换。

Excel 数据查找与替换

在日常办公中,可能随时需要从庞大数据库中查找相关记录或者需要对数据库中个别数据进行修改,如果采用手工方式来查找或修改数据其效率会非常低下。此时可以使用“查找与替换”功能来快速完成该项工作。

数据查找

要快速查找到特定数据,其操作如下。

步骤1:将光标定位到数据库的首行中,单击“开始”菜单,在“编辑”组中单击“查找和选择”按钮的下拉按钮,在展开的下拉菜单中单击“查找”选项,如图4-45所示。

步骤2:打开“查找和替换”对话框,在“查找内容”中输入查找信息,如图4-46所示。

提示:在Excel 2016中,单击“Ctrl+F”组合键,即可快速打开“查找和替换”对话框。

步骤3:单击“查找下一个”按钮,即可将光标定位到满足条件的单元格上。可依次单击“查找下一个”按钮查找满足条件的记录,如图4-47所示。

图4-45 单击“查找”选项

图4-46 在“查找内容”中输入查找信息

步骤4:在“查找内容”框中输入了查找内容后,单击“查找全部”按钮即可显示出所有满足条件的记录所在工作表、所在单元格以及其他信息,如图4-48所示。

步骤5:在进行查找时,默认查找范围为当前工作表,要实现在工作簿中进行查找,则在“查找和替换”对话框中单击“选项”按钮,激活选项设置,在“范围”框中可设置查找范围为“工作簿”,如图4-49所示。

图4-47 单击“查找下一个”按钮

图4-48 单击“查找全部”按钮

图4-49 设置查找范围为“工作簿”

提示:在查找过程中,也可以区分大小写和全/半角。只需要在“选项”设置中将“区分大小写”和“区分全/半角”复选框选中即可。

数据替换

如果需要从庞大数据库中查找相关记录并对其进行更改,此时可以利用替换功能来实现。

数据替换功能的使用

步骤1:将光标定位到数据库的首行中,单击“开始”菜单,在“编辑”组中单击“查找和选择”按钮的下拉按钮,在展开的下拉菜单中单击“替换”选项,如图4-50所示。

图4-50 单击“替换”选项

步骤2:打开“查找和替换”对话框,在“查找内容”中输入要查找的内容,在“替换为”中输入要替换的内容,如图4-51所示。

图4-51 “查找和替换”对话框

步骤3:设置好“查找内容”与“替换为”内容之后,单击“查找下一个”按钮,光标定位到第一个找到的单元格中,如图4-52所示。

步骤4:单击“替换”按钮,即可将查找的内容替换为所设置的“替换为”内容,如图4-53所示。

设置让替换后的内容显示特定格式

设置让替换后的内容显示特定的格式可以达到特殊标释的作用,下面举例介绍如何实现让替换后的内容显示特定的格式。

图4-52 单击“查找下一个”按钮

图4-53 单击“替换”按钮

步骤1:打开“查找和替换”对话框,分别在“查找内容”与“替换为”框中输入要查找的内容与替换的内容。单击“选项”按钮,展开“选项”设置。单击“替换为”框后面的“格式”按钮,如图4-54所示。

图4-54 单击“格式”按钮

步骤2:打开“替换格式”对话框,在“字体”选项卡中,可以设置文字字体、字号、颜色等格式,如图4-55所示。

步骤3:切换到“填充”选项卡下,可以设置填充颜色等格式,如图4-56所示。

提示:在设置替换格式时,还可以设置让替换后的内容满足特定的数字格式(在“数字”选项卡下设置),或设置替换后的内容显示特定边框(在“边框”选项卡下设置),只需要选择相应的选项卡按照与上面相同的方法进行设置即可。

步骤4:单击“确定”按钮,返回到“查找和替换”对话框中,原“未设定格式”显示为“预览”格式,如图4-57所示。

步骤5:设置好“查找内容”、“替换为”内容以及“替换为”内容的格式后,单击“全部替换”按钮,Excel会自动进行查找并替换,替换后的内容显示为所设置的格式,如图4-58所示。

图4-55 可以设置文字字体、字号、颜色等格式

图4-56 设置填充颜色等格式

图4-57 原“未设定格式”显示为“预览”格式

图4-58 替换后的内容显示为所设置的格式

Excel 使用查找与替换功能更新旧科目表

在更新旧会计科目表时,我们不可能一个个地输入新的数据,进行更新,这时候我们就可以使用查找与替换功能来更新旧科目表,这样既省时又省力。比如,相比于旧的会计科目表,新的会计科目表“资产类”科目中的“现金”科目改名为“库存现金”,“短期投资”科目改名为“交易性金融资产”,“低值易耗品”科目改名为“消耗性生物资产”。接下来我们就具体讲解一下怎么使用查找与替换功能。

步骤01:打开“旧会计科目表.xlsx”工作簿,主页功能区切换到“开始”选项卡,单击“编辑”组中的“查找和选择”下拉按钮,在下拉列表中选择“替换”选项,如图1-73所示。

步骤02:在弹出的“查找和替换”对话框中,切换到“替换”选项卡,在“查找内容”文本框中输入“现金”,在“替换为”文本框中输入“库存现金”,然后单击“全部替换”按钮,就会完成文本的替换,如图1-74所示。此时,系统会弹出Microsoft Excel对话框,单击“确定”按钮便会返回“查找和替换”对话框。

步骤03:按照步骤02的操作方法依次将旧科目表中的“短期投资”“低值易耗品”分别替换为“交易性金融资产”“消耗性生物资产”,单击“关闭”按钮,完成对旧科目表的更新,如图1-75所示。

图1-73 选择查找和替换功能

图1-74 输入查找和替换内容

图1-75 查找和替换效果图

图1-75 (续)

Excel 通过REPLACEB函数将部分字符根据指定字节数用新字符串替换

如果需要将字符串中的部分字符用另一个字符串替换,可通过“REPLACEB”函数实现。REPLACEB函数的语法为:=REPLACEB(old_text,start_num,num_bytes,new_text),各参数的含义介绍如下。

※ old_text:指定成为替换对象的文本或文本所在的单元格。

※ start_num:指定开始替换的字符位置。

※ num_bytes:希望替换old_text参数中字节的个数。

※ new_text:指定替换旧字符串的文本或文本所在的单元格。

下面以将单元格中的前面4个字节替换为“片片”为例,具体操作步骤如下。

01 在“B1”单元格中输入需要的文字。

02 在需要显示结果的单元格中输入公式:=REPLACEB(B1,1,4,”片片”),然后按下“Enter”键确认即可。

alt

Excel 通过REPLACE函数将部分字符用另一个字符串替换

如果需要将字符串中的部分字符用另一个字符串替换,可通过“REPLACE”函数实现。REPLACE函数的语法为:=REPLACE(old_text,start_num,num_chars,new_text),各参数的含义介绍如下。

※ old_text:指定成为替换对象的文本或文本所在的单元格。

※ start_num:指定开始替换的字符位置。

※ num_chars:希望替换old_text参数中的字符个数。

※ new_text:指定替换旧字符串的文本或文本所在的单元格。

下面以将单元格中的“长江”替换为“银河”为例,具体操作如下。

01 在“B1”单元格中输入需要的文字。

02 在单元格中输入公式:=REPLACE(B1,11,2,”银河”),按下“Enter”键确认即可。

alt

Excel 实战:英文人名的姓、名称呼提取

某高校聘请了几位外国教授进行短期讲学,因为其姓名方面比较混乱,现在需要对其信息进行统计,即将专家的名称分为3部分,并根据性别输出称呼。下面通过具体步骤来详细介绍如何对专家信息进行统计。

STEP01:新建一个空白工作簿,重命名为“专家信息统计表”,切换至“Sheet1”工作表,并输入原始数据,如图12-63所示。

图12-63 原始数据

STEP02:将“名”从全名中分离出来,在C2单元格中输入公式“=LEFT(A2,FIND(” “,A2)-1)”,然后按“Enter”键返回即可得到相应的专家名,如图12-64所示。在此公式中利用FIND函数查找第1个空格,然后返回空格前面的部分。

STEP03:选中C2单元格,使用填充柄工具向下复制公式至C6单元格,完成对C3:C6单元格区域的自动填充,结果如图12-65所示。

STEP04:对“姓”进行提取,在D2单元格中输入公式“=RIGHT(A2,LEN(A2)-FIND(“!”,SUBSTITUTE(A2,” “,”!”,LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)))))”,然后按“Enter”键返回,得到的结果就是外国专家的姓,如图12-66所示。

STEP05:选中D2单元格,使用填充柄工具向下复制公式至D6单元格,完成对D3:D6单元格区域的自动填充,结果如图12-67所示。

STEP06:通过性别输出称呼,在E2单元格中输入公式“=IF(B2=”男”,CONCATENATE(D2,”先生”),CONCATENATE(D2,”女士”))”,然后按“Enter”键返回即可得出称呼,结果如图12-68所示。

STEP07:选中E2单元格,使用填充柄工具向下复制公式至E6单元格,完成对E3:E6单元格区域的自动填充,最终结果如图12-69所示。

英文“名”的提取

图12-64 “名”的提取

图12-65 自动填充后的结果

英文“姓”的提取

图12-66 “姓”的提取

图12-67 D列填充结果

图12-68 通过性别输出称呼

图12-69 最终结果

Excel 函数实战:从身份证种提取公司员工出生日期

打开“员工信息表.xlsx”工作簿,切换至“Sheet1”工作表,本例中的原始数据如图12-56所示。某办公人员需要从该工作表中提取公司员工的出生日期,以便计算工龄。下面通过具体的操作步骤来详细讲解该综合应用案例。

从身份证号种提取生日

图12-56 原始数据

一般来说,身份证号码的第7位~14位数字为出生日期,可以先用MID函数返回身份证号码中出生日期数值,再使用TEXT函数把出生日期数值转换成数值文本格式,最后使用REPLACEB函数替换出生年份,得出具体的出生日期。

STEP01:选中B2单元格,在编辑栏中输入公式“=MID(A2,7,8)”,然后按“Enter”键即可返回数值“19831004”,如图12-57所示。

STEP02:选中B2单元格,利用填充柄工具向下复制公式至B20单元格,即可返回所有出生日期的数值,如图12-58所示。

返回出生日期数值

图12-57 返回出生日期数值

从身份证号种返回所有出生日期的数值

图12-58 返回所有出生日期的数值

STEP03:选中C2单元格,在编辑栏中输入公式“=TEXT(B2,”0000-00-00″)”函数,按“Enter”键即可返回“1983-10-04”数值文本,如图12-59所示。

STEP04:选中C2单元格,利用填充柄工具向下复制公式至C20单元格,即可返回所有出生日期的文本,如图12-60所示。

图12-59 返回出生日期文本

图12-60 返回所有出生日期文本

STEP05:选中D2单元格,在编辑栏中输入公式“=REPLACEB(C2,1,5,” “)”函数,按“Enter”键即可返回出生日文本“10-04”,如图12-61所示。

STEP06:选中D2单元格,利用填充柄工具向下复制公式至D20单元格,即可返回所有出生日文本,如图12-62所示。

图12-61 返回出生具体日期

图12-62 返回所有出生日文本

Excel 内容替换:SUBSTITUTE函数详解

SUBSTITUTE函数用于将字符串中的部分字符串用新字符串替换。其语法是:


SUBSTITUTE(text,old_text,new_text,instance_num)

其中,text参数是包含要替换字符的字符串,或是对文本单元格引用;old_text参数是要被替换的字符串,如果原有字符串中的大小写不等于新字符串中的大小写,将不进行替换;new_text参数用于替换old_text的新字符串;instance_num参数是表示指定的字符串old_text在源字符串中出现几次,则用本参数指定要替换第几个,如果省略,则全部替换。下面通过实例具体讲解该函数的操作技巧。

打开“SUBSTITUTE函数.xlsx”工作簿,本例中的原始数据如图12-51所示。在“Sheet1”工作表中可以看到在编写例题步骤文本中含有“Enter”字符串,为规范编辑格式,要求使用SUBSTITUTE函数,将“Enter”字符串替换成“回车”字符串。具体的操作方法如下。

图12-51 原始数据

选中合并后的B11单元格,在编辑栏中输入公式“=SUBSTITUTE(B2,””Enter””,”回车”)”,然后按“Enter”键返回即可替换指定文本,如图12-52所示。

REPLACE函数用于将一个字符串中的部分字符用另一个字符串替换。其语法是:


REPLACE(old_text,start_num,num_chars,new_text)

其中,old_text参数为要将字符进行替换的文本;start_num参数为要替换new_text中字符在old_text中的位置;num_chars参数为要从old_text中替换的字符个数;new_text参数是来对old_text中指定字符串进行替换的字符串。下面通过实例具体讲解该函数的操作技巧。

打开“REPLACE函数.xlsx”工作簿,本例中的原始数据如图12-53所示。在实际应用过程中,财务人员需要使用REPLACE函数,将目录与页面之间的符号替换成“— —”符号。具体操作步骤如下。

图12-52 替换后的文本

图12-53 原始数据

STEP01:选中A8单元格,在编辑栏中输入公式“=REPLACE(A2,11,10,”— —”)”,然后按“Enter”键返回即可完成A2单元格中的文本替换,结果如图12-54所示。

STEP02:选中A8单元格,利用填充柄工具向下复制公式至A12单元格,实现所有文本的替换,最终结果如图12-55所示。

替换部分文本

图12-54 替换部分文本

替换所有文本

图12-55 替换所有文本

Excel删除所有相同内容

【查找与替换】,是将输入在【查找内容】的字符串置换为【替换为】内容的功能,而当【替换为】是空白,则【查找内容】中的内容就会变成空白……也就是删除该内容。并且,这种置换功能,实际上还能用于由公式构成的字符串。

下表中,D 列的构成比率单元格中,包含有以分母为绝对引用的除法公式。在单元格 D3中含有以下公式:

=C3/$C$12

在单元格 D3中输入=C3/$C$12

将单元格 D3输入的公式一直复制到 D12,分母也不会从 D12偏离,还是正常的除法计算。

在单元格 D3:D12的范围内,想要去掉分母中的$符号,可以按照以下步骤操作。

➊ 选择想要进行替换的范围(此例中为 D3:D12)。

➋ 按Ctrl +H 打开【查找与替换】。

➌ 【查找内容】输入“$”,【替换为】则保持空白状态,点击【全部替换】。

➍ 可以看到公式中的$符号被删除了。

像这样,【查找与替换】功能需要先行选择范围,这样才能只在这个范围内进行替换操作。如果未选中范围,就会以整个工作表为范围进行替换,请务必注意。