excel数据处理chapter-5精编.ppt

5.10.3 文本重复、清理和替换函数 3．文本替换函数 REPLACE(text, start_num, n, new_text) SUBSTITUTE(text, old, new, [num]) 在REPLACE函数中，将用new替换text文本中从start位置开始算起的n个字符. SUBSTITUTE 则用new替换掉text文本中的old，num 是可选项，用来指定要以 new 替换text中第几次出现的 old。如果指定了 num，则只有满足要求的 old 被替换；否则会将 Text 中出现的每一处 old 都更改为 new。 5.10.3 文本重复、清理和替换函数 【例5.29】有个人信息表如图5.34中A1:E10区域所示，其中A列的姓名中有许多不可识别的非打印字符，B列的地址以“/”间隔开有省份、地区和县名，身高是文本形式的数字，爱好列数据中有许多不恰当的空格字符，致使显示混乱，E列是用函数对身高高于170的判定结果，可以看出许多结果是错误的。 G3：=CLEAN(A3) K3：=TRIM(C3) L3：=TRIM(D3) H3：=TRIM(MID(SUBSTITUTE($B3,/,REPT( ,20)),COLUMN(A:A)*20-19,20)) 并将H3公式复制到I3、J3 5.10.3 文本重复、清理和替换函数 技术分析 （1）清取A列姓名中的非打印字符。 在G3中输入公式“=CLEAN(A3)”， 向下复制此公式即可删除姓名中的非打印字符，生成G列的姓名。 （2）清除K列身高和L列爱好中的多余空格 K3中输入公式“=TRIM(C3)” L3中输入公式“=TRIM(D3)” 向下复制上述公式，即可生成K、L列的数据 5.10.3 文本重复、清理和替换函数 （2）分离省份、地区和县名 在H3输入下面的公式，从B3提取省份名称 =TRIM(MID(SUBSTITUTE($B3,/,REPT( ,20)),COLUMN(A:A)*20-19,20)) “SUBSTITUTE($B3,/,REPT( ,20))”用REPT函数生成的连续20个空格 替换了B3中的间隔符“/”，生成“四川省 遂宁市 射洪县”字符串，其中的空格都是20个 COLUMN(A:A)是计列Excel工作表列对应的编号，A列为1，B列为2，C列为3……，在公式中常用该函数来生成需要的自然数，这里也是如此。所以“COLUMN(A:A)*20-19”的结果为1； MID再从SUBSTITUTE 生成的“四川省 遂宁市 射洪县”字符串，从第一个字符开始连续提取20字符，即为“四川省 ”,后面共有17个空白，空白被TRIM函数删除。 5.10.3 文本重复、清理和替换函数 将H3中的公式向右复制到I3和J3，对应的公式分别为： =TRIM(MID(SUBSTITUTE($B3,/,REPT( ,20)),COLUMN(B:B)*20-19,20)) 由于H3公式中COLUMN的列引用为相对引用，复制到I3时将变为COLUMN(B:B)=2。因此I3中的公式是从“四川省 遂宁市 射洪县”字符串的第21个字符开始，提取连续的20个字符，其结果为“ 遂宁市 ”形式的字符串。 将H3中的公式复到J3时，对应的公式为： =TRIM(MID(SUBSTITUTE($B3,/,REPT( ,20)),COLUMN(C:C)*20-19,20)) 由于COLUMN(C:C)=3，因此J3中的公式将从“四川省 遂宁市 射洪县”字符串的第41个字符开始，连续提取20个字符串，其中包括县名和空格。 5.10.3 文本重复、清理和替换函数 案例 【例5.补充】文本函数应用举例 某银行要建立下图所示的用户资料表。 其中的出生日期从身份证号码中提取； 如果是男性，其尊称就是“X先生”，如果是女性，其尊称就是“X女士”，X是用户的姓氏； 存款帐号是出生日期（去掉出生年的前两位数字）； 存款密码是身份证号码的最后6位数字； 最后要找出解放西路的用户姓名，以便给他们发新的银行卡。 5.10 字符函数 用文本类函数产生E2:I9区域的数据。 5.10 字符函数 （1）产生出生日期 在E2单元格输入公式，然后将它向下复制此公式就能够生成所有人的出生日期。 =MID(D2,7,4)年VALUE(MID(D2,11,2))月MID(D2,13,2)日“ （2）生成用户的尊称 在F2单元格输入下面的公式，并将此公式向下复制，生成所有人的尊称。 =LEFT(A2,1)IF(B2=女,女士,先生) （3）生成存款帐号 在G2单元格输入下面的公式，并将此公式向下复制，生成所有人的存款