您的位置: 首页 > EXCEL技巧 > Excel函数 >

数组公式提取单元格内第一个汉字前的字符串

时间:2013-12-14 整理:docExcel.net

有时需要在Excel中提取单元格内文本第一个汉字前的字符串,例如从“MD-T289(XQ01)黑色”中提取“MD-T289(XQ01)”。如果各单元格文本中首个汉字前的字符串长度不等,可用下面的一些数组公式。例如字符串在下图所示的A列单元格中,在B列提取第一个汉字前的字符串。

 Excel提取单元格内汉字前的字符串示例

要提取第一个汉字前的字符串,首先要确定文本中第一个汉字的位置。确定文本中第一个汉字位置的公式有许多,如下面的几个公式,假如文本在A2单元格中:

=MATCH(2,LENB(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),)

或:

=MATCH(TRUE,MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)>"吖",)

上述两个公式为数组公式,需按Ctrl+Shift+Enter结束,下同。

说明:

1.第一个公式先用MID函数将单元格内文本的各个字符放置到一个数组,再用LENB函数判断各字符的长度,LENB函数对于单字节字符返回“1,”,而对于汉字(双字节)返回“2”。最后用MATCH函数判断第一个“2”的位置,即第一个汉字的位置。

2.第二个公式中,先用MID函数将单元格内文本的各个字符放置到一个数组,然后将每个字符与“吖”字比较。对于常用汉字,比较后的结果会返回“TRUE”,因为在Excel中汉字也如同其他单字节字符一样可以进行比较,“吖”字在常用汉字中“最小”,因而用其与其他汉字比较。如

="吖"<"一"

返回TRUE。

最后用MATCH函数判断第一个“TRUE”的位置,即第一个汉字的位置。

对于一些特殊的文本,例如文本的右侧全部为汉字,如上图A10单元格,可用下面的简单公式得到第一个汉字的位置:

=2*LEN(A10)-LENB(A10)+1

说明:“2*LEN(A10)-LENB(A10)”返回A10单元格文本中单字节字符的数量。

如果第一个汉字前的字符串中不包含空格,下面的数组公式也可返回第一个汉字的位置,例如A2:A7区域中的文本。对于A2单元格,数组公式:

=MATCH(" ",MIDB(A2,ROW(INDIRECT("1:"&LEN(A2))),1),)

将返回“4”。

说明:MIDB函数对于汉字(双字节)按“2”计数,即按字节计数。MIDB函数对汉字每个字节会返回一个空格字符,如下面的公式:

=MIDB("一",1,1)

返回空格字符。而公式:

=MIDB("一",1,2)

返回汉字本身,即“一”字。

公式最后用MATC函数返回第一个空格字符的位置,即第一个汉字的位置。

取得第一个汉字的位置后,用LEFT函数即可提取第一个汉字前的字符串,如A2单元格,可用下面的数组公式:

=LEFT(A2,MATCH(2,LENB(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),)-1)

用自定义函数提取单元格内字符串中的数字 问题:用自定义函数提取单元格内字符串中的数字
回答:如果Excel单元格中包含一个混合文本和数字的符串,要提取其中的数字,通常可以用下面的公式,例如符串“隆平高科000998”在A1单元格中,在B1中输入数组公式: =MID(A1,MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT( 1: &LEN(A1))),1)),0),COUN...
用数组公式获取字符在字符串中最后出现的位置 问题:用数组公式获取字符在字符串中最后出现的位置
回答:如果要获取符串中的某个字符最后出现的位置,可以用下面的数组公式。假如A1单元格中的符串为“Microsoft Office Word”,要查找字符“o”最后出现的位置,在单元格B1中输入数组公式,公式输入完毕后按Ctrl +Shift +Enter结束。 ...
如何颠倒单元格内字符的排列顺序 问题:如何颠倒单元格内字符的排列顺序
回答:如果要颠倒某个单元格中符串的排列顺序,例如将A1单元格中的“ABCDEFG”颠倒顺序排列为“GFEDCBA”,可以用自定义函数来实现,方法如下。 1.按Alt+F11,打开VBA编辑器。 2.单击菜单“插入→模块”,然后在代码窗口中输入下...
统计字符串中不重复字符数量的公式 问题:统计字符串中不重复字符数量的公式
回答:要统计符串中不包括重复字符的数量,即符串中的字符种类数量,在Excel中可以用下面的一些公式。假如A1单元格中包含符串“Excel2010”,在B1单元格中输入公式: =SUMPRODUCT(--(FIND(MID(A1,ROW(INDIRECT( 1: &LEN(A1))),1),A1)=ROW(INDIRE...
用公式提取Excel单元格中的汉字 问题:用公式提取Excel单元格中的汉字
回答:对于一个混杂各种字母、数字及其他字符和汉字的文本符串,要提取其中的汉字,在Excel中通常可用下面的公式。例如下图A列中的符串,要在B列提取其中的汉字(或词语)。 如果汉字位于符串的开头或结尾,用LEFT或RIGHT...
按条件合并多个单元格内容到同一单元格的自定 问题:按条件合并多个单元格内容到同一单元格的自定
回答:Excel中的CONCATENATE函数可以将多个文本符串联接成一个文本符串,但有时需要按指定条件合并某些单元格的内容到一个单元格,这时CONCATENATE函数无法实现,而使用下面的自定义函数就十分方便了。如图所示,需要在F2单元格...
相关推荐: