使用Excel文本函数管理数据的7种方法

Question

如题所述

Answer 1

使用Excel文本函数管理数据的7种方法

如果您的工作需要管理海量数据,尤其是从其他来源导入的数据,则Excel的文本功能可以节省大量时间.幸运的是,所有ASCII数据都可以轻松导入,

但是从一个来源到另一个来源,导入数据的格式可能会大不相同.

例如,可以用制表符,空格,逗号或句点来分隔字段.其他任何字符都比空格更可取.这是因为空格不仅在字段之间存在,而且还在字段中分隔单词,

这使对字段进行排序成为真正的挑战.这就是以下文本功能非常有用的地方.

一.使用SUBSTITUTE函数将一个文本字符串替换为另一个

函数语法：SUBSTITUTE函数的语法（或句子结构）是这样的：

=SUBSTITUTE（文本,旧文本,新文本,[实例编号]）.

注意：如果未指定实例编号,则每次出现的“旧文本”都会更改为“新文本”.如果指定实例编号,则仅替换旧文本的出现.例如,输入数字“1”

表示您只想更改字符串中该单词的首次出现.

1.在A列中输入一些短语（从A2到A13）.

2.在B列中输入要更改的单词或短语.

3.在列C中输入您要替换旧文本的单词或短语.

4.在单元格D2至D7（或数据库的一半）中输入以下公式：=SUBSTITUTE（A2,B2,C2,1）

这仅将第一次出现的旧文本更改/替换为新文本.

5.接下来,在其余单元格中输入此公式（在本例中为D8至D13）：=SUBSTITUTE（A2,B2,C2）.这会将所有出现的旧文本更改/替换为新文本.

二.使用TRIM,RIGHT和SUBSTITUTE提取文本字符串中的最后一个单词

在此示例中,目标是从文本字符串（客户端列表的全名）中提取姓氏即姓氏.

1.在A列中输入一些名称：名字,姓氏和中间名或缩写.

2.在B2中输入以下公式：=TRIM（RIGHT（SUBSTITUTE（A2,“”,REPT（“”,50））,50））.

3.将公式从B2复制到B1000到B3（或数据库的末尾）.对于此示例,我们假设您的数据库有1,000条记录.

4.此公式有效,因为SUBSTITUTE函数查找文本字符串中的所有空格,然后用50个空格替换每个单个空格.RIGHT函数删除50个字符（从右到左）,而

TRIM函数删除所有多余的前导空格,仅保留最后一个单词.如果文本字符串较长,请尝试用100或100以上的值替换上式中的50个值.

三.使用LEFT＆SEARCH提取文本字符串中的第一个单词/名称

当您需要将客户名的中间名和姓氏分开时,此公式适用.

1.在A列中输入一些名称（或使用上一个练习中的相同名称）.

2.在B2到B1000中输入以下公式：=LEFT（A2,SEARCH（““,A2）-1）将每个客户的名字提取到单独的列中.

Excel教程哪家好？Excel教程

优质课程尽在>怎样选择Excel课程？四.使用TRIM,RIGHT,REPT和SUBSTITUTE提取文本字符串中除第一个单词以外的所有内容

本练习的目的是从客户名称列表中删除这些称呼.这些客户提供了机密的调查信息,因此公司不希望每个人的头衔和称呼影响到测量员.

1.在A列中输入更多名称（或使用上一个练习中的相同名称）.在每个名字前输入一些敬语；例如,先生,小姐,女士,夫人,博士,先生,领主,

夫人,上尉等.

2.在B2到B1000中输入以下公式：=TRIM（RIGHT（SUBSTITUTE（TRIM（A2）,”,,REPT（““,60））,180）））提取所有客户的全名

减去敬礼.

3.而且,如果您出于某种原因想要提取敬语,请在C2到C1000中输入以下公式：=LEFT（A2,SEARCH（““,A2）-1）.

五.使用LEFT,FIND和SUBSTITUTE从电子邮件地址中提取名称

一天中最糟糕的工作是花几个小时从电子邮件地址手动重新键入客户端名称或域名,尤其是当列表中的名称超过5,000个时.使用以下公式可以在

几分钟内完成此任务.

1.在A列中输入一些电子邮件地址.

2.在B2到B5000中输入以下公式：=LEFT（A2,FIND（“@”,a2）-1）以提取所有客户端的全名.

3.在C2到C5000中输入此公式,以删除名字和姓氏之间的下划线：=SUBSTITUTE（B2,”“,”_”）.

4.移至单元格F2.选择“公式”>“文本”>.在“函数自变量”对话框屏幕上的“文本”字段框中键入C2,或单击单元格C2,然后单击

“确定”.此公式将名称转换为适当的大小写（即,大写的名字和姓氏的首字母,其他所有字母均小写）.

5.将F2中的公式通过F5000复制到F3,然后按Enter键.

六.使用TRIM,LEFT,SUBSTITUTE,MID,FIND,LEN和REPT从电子邮件地址中提取域

1.在D2到D5000中输入以下公式,以从电子邮件地址中提取域名：

=TRIM（LEFT（SUBSTITUTE（MID（A2,FIND（“@”,A2）,LEN（A2））,“”,REPT（“”,100））,100））

2.最后,在E2到E5000中输入此公式,以从提取的域名中删除@符号：=SUBSTITUTE（D2,“@”,””）.