小伙伴最近做网络问卷调查,共收集了1000多份问卷。将问卷导入Excel时才发现每份问卷的问题和答案混在一个单元格内,每个答案和问题换行显示。小伙伴想知道如何从这样的表格中提取想要的信息。 一、案例 如下图所示,B2单元格为一份问卷调查收集的信息。要求提取被调查者的职业。 二、计算步骤 在C2单元格输入公式 =TRIM(MID(SUBSTITUTE(B2,CHAR(10),REPT(" ",100)),500,100)) 公式解析: (1)CHAR(10)指换行符。 如下图所示,在E2单元格输入公式=C2&CHAR(10)&D2,该公式在单元格C2和单元格D2内容之间插入换行符。 需要注意的是,点击“自动换行”后,才会显示在指定位置换行的效果。 (2)REPT函数用于按指定次数重复文本。REPT(" ",100)指返回100个空格(注意引号之间是一个空格)。 (3)SUBSTITUTE函数将字符串中的部分字符串用新字符串代替。SUBSTITUTE(B2,CHAR(10),REPT(" ",100))将B2单元格中的每个换行符替换为100个空格。 (4)MID函数用于从文本字符串指定位置起返回指定长度的字符串。 如下图所示,从C2单元格的文本第3个位置起(即“E”)提取5个字符,公式返回“Excel”。 MID(SUBSTITUTE(B2,CHAR(10),REPT(" ",100)),500,100),从换行符替换为空格后的文本的第500个位置起,提取100个字符。提取的100个字符中一定包含“程序员”三个字符,其他字符为空格。使用Trim函数将提取的字符两端的空格剔除。 (5)为什么MID函数提取的100个字符中一定包含“程序员”三个字符呢? 将B2单元格中的每个换行符替换为100个空格后的文本如下图所示(每个空格以红字“100个空格”表示): 在文本“程序员”之前的字符个数为(500个空格+问题1-3文本长度)。鉴于本例中B2单元格的文本长度,MID函数提取字符串的起始位置“500”一定在文本“3、您的职业是?”和“程序员”之间的100个空格处。 MID函数提取的“100”个字符也一定会提取到文本“程序员”和“4、您的性别是?”中间的100个空格处。因此公式中需要使用Trim函数剔除MID函数返回结果中的空格。 这也说明,REPT(" ",100)不一定非要重复100次空格,而是要根据单元格内的文本长度而定,建议空格重复次数要远远大于单元格内的文本长度。 拓展(如何提取单元格内第一行文本): 如果提取单元格内第一行的文本,在C2单元格输入公式 =LEFT(B2,FIND(CHAR(10),B2)-1) 提取第一行文本只需要使用Find函数找到第1个换行符的位置,然后使用LEFT函数提取文本即可。 更多关于文本提取、统计和匹配的文章: |
|