excel|精心整理Excel从文本中提取指定字符的4种案例!( 三 )



最后一个直接使用substitute函数替换即可 。
数组公式如下:
=SUBSTITUTE(A2B2&C2\"\")

如果你不想用上面这么长的公式 , 又或者地址信息没有这么完整 , 存在不清晰的问题!!
「那就使用方方格子插件来完成地址提取 , 速度飞快 。 」
操作路径:方方格子-高级文本处理更多-提取地址-选择地址范围-确定-点击结果存放单元格-确认

最终结果如下图 , 几秒钟就搞定了 。

即使地址不是特别规范 , 也能大概匹配拆分出来 。
当然 , 最终还需要仔细检查一下是否存在拆分出错的情况 。
3. 提取指定字符-分隔的指定个数字符如下图数据所示:

文本中用-分隔各个信息 , 其中第二个是产品名称 , 最后一个是核销状态 。
如果是比较规范的数据 , 就可以先手动提取一行 , 如下图:

然后按快捷键CTRL+E , 智能拆分提取 , 就能提取出对应的数据 。

但是 , 这个案例 , CTRL+E没法很好的拆分出来 , 比如产品名称 , 此时可以选择使用函数公式 , 将产品名称和核销状态提取出来 。
提取产品名称:
=MID(A2FIND(\"-\"A2)+1FIND(\"-\"A2    FIND(\"-\"A2)+1)-FIND(\"-\"A2)-1    )

提取最后一个核销状态:
=TRIM(RIGHT(SUBSTITUTE(A2\"-\"    REPT(\" \"LEN(A2))    )LEN(A2)))


「提取产品名称公式原理解析:」
  1. 使用find函数找到第一个指定字符-的位置数 , 此时find函数默认从字符串第一个字开始找 。
  2. 然后接着继续用find函数找指定字符- , 但是此时 , 我们find函数的第三个参数就写第一次find函数的结果值+1 , 也就是从第一个指定字符-的下一个字符位置开始查找 , 由此找到了第二个-的位置 。
  3. 至此mid函数的2、3参数已经完全找到了 。

「提取最后一个核销状态公式原理解析」
  1. 使用substitute函数 , 将所有的指定字符-替换为长度和字符串长度一致的空格 。 替换后图片如下图 。


  1. 使用right函数 , 从替换后的字符串右侧 , 提取长度为原本字符串长度的文本 。 此时的文本就是空格和最后一个核销状态数据
  2. 使用trim函数 , 去除文本中的空格
ps. 需要注意第一步中 , 我们使用rept函数 , 用来生成字符长度和原始字符串长度一致的空格
4. 只提取数字或者字母如下图所示 , 字母和数字在文本中的不同位置 , 现在想要将字母和数字全部提取 。

从文本中提取字母和数字的公式如下:
=SUBSTITUTE(    MIDB(        A2        SEARCHB(\"?\"A2)        2*LEN(A2)-LENB(A2))    \"-\"\"\")


「从字符串中提取字母数字公式原理解析:」
  1. 公式使用了字节和字符两种不同的计算方法 。 使用minb函数提取指定字节数的数据 , lenb和len分别统计字节和字符数量 。
  2. 由于字母数字和符号都是1个字节 , 中文是2个字符 , 通过searchb查找任意1个字节在字符串中的出现位置 , 就能定位字符串中第一个字母数字或者符号出现位置 , 进而通过midb函数提取 。
  3. 提取位数则通过len和lenb的计数来计算出来 , lenb减去len得到的就是中文字符的数量也就是2 , 换算就是4个字节 , 最后用lenb减去中文字符的字节数 , 就得到字母数字和符号的字节数 。