c高原X
S山谷1
没在一个单元格内是一列,共有6万行左右,其他列有字母和数字,不想用替换,太麻烦了。
åå¦æåå¨A1åå
æ ¼
å¯ä»¥å¨B1åå æ ¼è¾å ¥å ¬å¼åæSHIFT+CTRL+ENTERç»æå¾å°æ°ç»å ¬å¼
=INDEX(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1),SMALL(IF(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1)>"å",ROW(INDIRECT("1:"&LEN($A$1))),256),COLUMN(A1)))
ç¶ååå³å¡«å ,ç´å°åºç°é误å¼
ç¶åç¨ç»åå½æ°=CONCATENATEææ±åç»åå³å¯
2007çå¯ä»¥ç¨ä¸é¢å½æ°æ¥é误å¼
=IFERROR(INDEX(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1),SMALL(IF(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1)>"å",ROW(INDIRECT("1:"&LEN($A$1))),256),COLUMN(A1))),"")
2003çæ¥é误ç¨å ¬å¼
=IF(ISERROR(INDEX(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1),SMALL(IF(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1)>"å",ROW(INDIRECT("1:"&LEN($A$1))),256),COLUMN(A1)))),"",INDEX(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1),SMALL(IF(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1)>"å",ROW(INDIRECT("1:"&LEN($A$1))),256),COLUMN(A1))))
åå¤è¡¥å é®é¢:
å¦æé½æ¯åä½æ°,ä¸é´ä¸¤ä½æ¯æ±å,ç´æ¥å¨B1è¾å ¥å ¬å¼
=MID(A1,2,2)
ç¶åä¸æå¡«å å³å¯
è¦ççä½ çæ°æ®çå¤æç¨åº¦
å ¶å®åºè¯¥ç¨ä¸ä¸åé¢çæ°ç»å½æ°
é£ä¸ªå½æ°æ¯å å«ææçé乱交æçæ±åå ¨é¨æååºæ¥
å¦
âadsfå åºéä¸dfç«å»123dfsæ¯é¥â
ä¸é¢çå°±å¯ä»¥æè¿éé¢çæææ±åæååºæ¥
å¯ä»¥å¨B1åå æ ¼è¾å ¥å ¬å¼åæSHIFT+CTRL+ENTERç»æå¾å°æ°ç»å ¬å¼
=INDEX(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1),SMALL(IF(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1)>"å",ROW(INDIRECT("1:"&LEN($A$1))),256),COLUMN(A1)))
ç¶ååå³å¡«å ,ç´å°åºç°é误å¼
ç¶åç¨ç»åå½æ°=CONCATENATEææ±åç»åå³å¯
2007çå¯ä»¥ç¨ä¸é¢å½æ°æ¥é误å¼
=IFERROR(INDEX(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1),SMALL(IF(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1)>"å",ROW(INDIRECT("1:"&LEN($A$1))),256),COLUMN(A1))),"")
2003çæ¥é误ç¨å ¬å¼
=IF(ISERROR(INDEX(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1),SMALL(IF(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1)>"å",ROW(INDIRECT("1:"&LEN($A$1))),256),COLUMN(A1)))),"",INDEX(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1),SMALL(IF(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1)>"å",ROW(INDIRECT("1:"&LEN($A$1))),256),COLUMN(A1))))
åå¤è¡¥å é®é¢:
å¦æé½æ¯åä½æ°,ä¸é´ä¸¤ä½æ¯æ±å,ç´æ¥å¨B1è¾å ¥å ¬å¼
=MID(A1,2,2)
ç¶åä¸æå¡«å å³å¯
è¦ççä½ çæ°æ®çå¤æç¨åº¦
å ¶å®åºè¯¥ç¨ä¸ä¸åé¢çæ°ç»å½æ°
é£ä¸ªå½æ°æ¯å å«ææçé乱交æçæ±åå ¨é¨æååºæ¥
å¦
âadsfå åºéä¸dfç«å»123dfsæ¯é¥â
ä¸é¢çå°±å¯ä»¥æè¿éé¢çæææ±åæååºæ¥
温馨提示:答案为网友推荐,仅供参考
第1个回答 2012-08-24
假设数据在A列并从A2开始,则在B2输入:
=MID(A2,MATCH(2,LENB(MID(A2,ROW($1:$100),1)),),LENB(A2)-LEN(A2))
数组公式,按CTRL+SHIFT+回车结束,下拉填充
=MID(A2,MATCH(2,LENB(MID(A2,ROW($1:$100),1)),),LENB(A2)-LEN(A2))
数组公式,按CTRL+SHIFT+回车结束,下拉填充
第2个回答 2012-08-23
假设数据在a列(从a1开始,b列为空(若有数据,可在a列后插入一空白列)b1=MID(A1,MIN(IF(LENB(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=2,ROW(INDIRECT("1:"&LEN(A1))))),LENB(A1)-LEN(A1)),数组公式,以同时按ctrl+shift+回车三键结束,选b1,鼠标停在b1右下角变成小十字双击即可
第3个回答 2012-08-23
假设A1为:c高原XS山谷1
那么在B1输入=MID(A1,2,2)&MID(A1,6,2)
本回答被网友采纳