现在的位置: 首页资源分享>正文
Excel实现批量替换中间字符为星号的几种方法
2011年12月27日 资源分享 暂无评论 ⁄ 被围观 13,467+

有时候我们需要在Excel里实现诸如电视节目中抽奖时用到的手机号或者是信用卡账号等的保密效果,这就需要把电话号码或者是银行卡号替换成对应的带星号的字符串,形如“150****8175”、“****-****-****-5187”的效果。下面我就总结一下我所知道的几种实现方式以及一些相关小技巧:

实现方式:

公式一: =MID(A1,1,3)&"****"&MID(A1,8,4)    (函数说明见文章末尾)

1.假设原数据列为A列(下举例同),需要显示为星号的列为B列,(注:设置好后可隐藏A列或做其他处理,见下文

2.选择B1单元格,然后在公式栏里输入以上公式,点击“√”。

公式输入完成后点击“对号”

公式输入完成后点击“对号”

3.将公式应用于整个列需要先选择B1单元格(已经编辑好公式的单元格),然后将鼠标放置到此单元格右下角待显示成黑色十字状时向下拖动,选中所有需要应用公式的列,复制填充即可。

填充方式选择复制单元格

填充方式选择复制单元格

公式二: =CONCATENATE(LEFT(A1,3),"****",RIGHT(A1,4))    (函数说明见文章末尾)

1.同公式一,选中B1单元格并复制公式填入公式栏,点击“√”。

公式输入完成后点击“对号”

公式输入完成后点击“对号”

2.仍然选择B1单元格并复制填充其余需要此公式格式化的单元格即可。

填充方式选复制单元格

填充方式选复制单元格

填充小技巧:有时候我们需要对庞大的数据进行格式化(批量替换手机电话号码或行用卡号为带星号的效果),此时如果单单考拖拽是很费时费力的,而且还容易在中途不小心出错。下面我就介绍一种比较实用的批量替换整列所有格式的方法:

1.选择已经写好公式的单元格B1,Ctrl+C复制B1单元格

右键单击已经写好公式的单元格选择复制

右键单击已经写好公式的单元格选择复制

2.点击需要批量替换公式格式化的列的第一个单元格B1,然后找到此列最后一个单元格Bn,按住Shift并单击鼠标左键以选择所有需要更改的数据。如果是修改整理数据的话可以直接单击表头B即可选择整列。

然后右键单击选择部分,在弹出的菜单中选择性粘贴公式即可。

选择性粘贴选择只粘贴公式

选择性粘贴选择只粘贴公式

以上裁图所用Excel版本为Office2010,其他版本请据此类推即可。

补充

1.设置好所有需要格式化的数据后就需要隐藏原数据单元格A1,这时只需要右键单击A列表头,选择隐藏列即可。

2.如何彻底删除原数据列A:我们只要把修改后的B列复制然后选择性粘贴时选择“数值”,然后再删除A列,即可。

3.函数说明:
函数名称:MID
主要功能:从一个文本字符串的指定位置开始,截取指定数目的字符。
使用格式:MID(text,start_num,num_chars)
参数说明:text代表一个文本字符串;start_num表示指定的起始位置;num_chars表示要截取的数目。
应用举例:假定A1单元格中保存了“我喜欢筑梦者博客”的字符串,我们在B1单元格中输入公式:=MID(A1,4,3),确认后即显示出“筑梦者”的字符。
特别提醒:公式中各参数间,要用英文状态下的逗号“,”隔开。
函数名称:CONCATENATE
主要功能:在Excel表格中常用的函数,即将几个文本字符串合并为一个文本字符串。
使用格式:CONCATENATE (text1,text2,...)
参数说明:Text1, text2, ... 为 1 到 30 个将要合并成单个文本项的文本项。这些文本项可以为文本字符串、数字或对单个单元格的引用。
应用举例:=CONCATENATE("我很喜欢",A2,",","因为",A3,"",A4,"!")    其中A2内容为“筑梦者博客”,A3内容为“他给我带来了知识”,A4内容为“和快乐”,将上述数据合并成一个语句(我很喜欢筑梦者博客,因为他给我带来了知识和快乐!)
特别提醒:也可以用 &(和号)运算符代替函数 CONCATENATE 实现文本项的合并。

筑梦者坚信,您的评论是最宝贵的!

(嘿嘿,只需要填写昵称和邮箱就OK)

留言无头像?

插入图片 留言不能超过500字,已输入字数: