EXCEL字段里面有数字和英文如何利用公式做到只保留英文字母.

示意图如下:

如数据在A2:A100,

在C2中输入公式:=SUBSTITUTE(A2,IFERROR(LOOKUP(9^9,--MID(A2,MIN(FIND({1;2;3;4;5;6;7;8;9},A2&123456789)),ROW(A$1:A$99))),""),"") , 公式下拉.

B1=MID(A1,SMALL(IF((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>64)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<123),ROW(INDIRECT("1:"&LEN(A1))),""),1),LARGE(IF((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>64)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<123),ROW(INDIRECT("1:"&LEN(A1))),""),1)-SMALL(IF((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>64)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<123),ROW(INDIRECT("1:"&LEN(A1))),""),1)+1)

=LEFT(A1,MIN(FIND(ROW($1:$10)-1,A1&"0123456789")-1))
然后 Ctrl+Shift+Enter