如何提取信息中的數字

Ⅰ excel濡備綍鎻愬彇瀛楃︿覆涓鐨勬暟瀛

Excel涓鎻愬彇瀛楃︿覆鐨勫父瑙佸艦寮忔湁浠ヤ笅涓夌嶏細絎涓錛屼粠鍘熸湁鏂囨湰涓鎴鍙栦竴閮ㄥ垎鐢ㄤ簬褰㈡垚鏂扮殑鏂囨湰;絎浜岋紝鏂囨湰鍜屾暟瀛楀垎紱;絎涓夛紝鎻愬彇鏂囨湰涓鐨勬暟瀛椼

銆銆鎻愬彇瀛楃︿覆鐨勫父鐢ㄥ嚱鏁版湁left(rihgt)銆乵id銆乻ubstitute絳夌瓑銆侺EFT鍑芥暟鏄浠庡乏鍚戝彸鎻愬彇錛孯IGHT鍑芥暟鏄浠庡彸鍚戝乏鎻愬彇錛孧ID鍑芥暟涔熸槸浠庡乏鍚戝彸鎻愬彇錛屼絾涓嶄竴瀹氭槸浠庣涓涓瀛楃﹁搗錛屽彲浠ヤ粠涓闂村紑濮嬫彁鍙栥

銆銆絎涓棰橈紝浠庝笅鍥炬墍紺虹殑E-mail鍦板潃涓鎻愬彇鈥淍鈥濆瓧絎︿互鍓嶇殑鏂囨湰浣滀負璐﹀彿銆

銆銆涓婂浘鎵紺虹殑E-mail鍦板潃涓鍖呭惈鐢ㄦ埛鐨勮處鍙鳳紝浣嗘槸璐﹀彿闀跨煭涓嶄竴錛屽崟鐙浣跨敤LEFT鍑芥暟鏃犳硶鎻愬彇錛岄渶瑕侀厤鍚團IND鍑芥暟鏉ュ疄鐜般

銆銆FIND鍑芥暟鐨勭敤娉曟槸榪斿洖涓涓瀛楃︿覆鍦ㄥ彟涓涓瀛楃︿覆涓鍑虹幇鐨勮搗濮嬩綅緗銆侳IND鍑芥暟鐨勬牸寮忔槸錛欶IND(瑕佹煡鎵劇殑鏂囨湰錛屽寘鍚瑕佹煡鎵炬枃鏈鐨勬枃鏈, 鎸囧畾寮濮嬭繘琛屾煡鎵劇殑瀛楃)銆

銆銆鏈棰樼殑鎬濊礬鏄浣跨敤FIND("@",A19)榪斿洖"@"鍦ˋ19鍗曞厓鏍肩殑浣嶇疆錛岀劧鍚庡啀浣跨敤LEFT鍑芥暟浠嶢19鍗曞厓鏍間粠鏈宸﹁竟寮濮嬫彁鍙栧埌"@"涔嬪墠鐨勫瓧絎︺

銆銆鍥犳わ紝絎涓棰樼殑鍏寮忎負錛=LEFT(A19,FIND("@",A19)-1)錛屼笅鎷夊嵆鍙瀹屾垚銆

銆銆鍙﹀栦篃鍙浠ヤ嬌鐢ㄦ暟緇勫叕寮忥細=LEFT(A19:B25,FIND("@",A19:B25)-1)錛屼笁閿緇撴潫錛屼笅鎷夈

銆銆絎浜岄橈紝灝嗕笅鍥炬墍紺虹殑鐢佃瘽鍙風爜鐨勫尯鍙峰拰鍙風爜鍒嗗埆鎻愬彇鍑烘潵銆

銆銆鍖哄彿鐨勬彁鍙栨柟娉曞拰絎涓棰樹竴鏍鳳紝鍏寮忎負錛=LEFT(A29,FIND("-",A29)-1)銆

銆銆鍚庨潰鐨勫彿鐮佺殑鎻愬彇灝辨槸鐢↙EN鍑芥暟鏉ュ緱鍒癆29鍗曞厓鏍肩殑闀垮害鍑忓幓鍖呮嫭鈥-鈥濈﹀彿涔嬪墠鐨勫煎仛涓簉ight鍑芥暟鐨凬um_chars鍊間負闇瑕佹彁鍙栫殑瀛楃︽暟銆傚叕寮忎負錛=RIGHT(A29,LEN(A29)-FIND("-",A29))銆

銆銆涓婇潰鐨勫叕寮忔秹鍙婂埌涓涓寰堢畝鍗曚絾寰堝疄鐢ㄧ殑鍑芥暟錛屽氨鏄疞EN鍑芥暟銆傚畠鐨勫姛鑳藉氨鏄榪斿洖鏂囨湰瀛楃︿覆涓鐨勫瓧絎︿釜鏁般侺EN鍑芥暟鐨勮娉曟槸錛歀EN(text)銆俆ext灝辨槸瑕佹煡鎵懼叾闀垮害鐨勬枃鏈銆傜┖鏍煎皢浣滀負瀛楃﹁繘琛岃℃暟銆

銆銆絎涓夐橈細浠庝笅鍥炬墍紺虹殑15浣嶅拰18浣嶈韓浠借瘉鍙風爜涓鍒嗗埆鎻愬彇鍑虹敓鏃ユ湡錛屾у埆錛屽勾榫勩

銆銆絎涓錛岃韓浠借瘉鍙風爜瀵瑰簲鐨勬у埆錛
銆銆15浣嶅彿鐮佺殑鏈鍚庝竴浣嶄負濂囨暟鏄鐢鳳紝鍋舵暟鏄濂;18浣嶅彿鐮佺殑絎17浣嶄負濂囨暟鏄鐢鳳紝鍋舵暟鏄濂熾
銆銆絎浜岋紝韜浠借瘉鍙風爜瀵瑰簲鐨勫嚭鐢熸棩鏈燂細
銆銆15浣嶅彿鐮佺殑錛7~8浣嶄負鍑虹敓騫翠喚(2浣)錛9~10浣嶄負鍑虹敓鏈堜喚錛11~12浣嶄負鍑虹敓鏃ユ湡銆
銆銆18浣嶅彿鐮佺殑錛7~10浣嶄負鍑虹敓騫翠喚錛11~12浣嶄負鍑虹敓鏈堜喚錛13~14浣嶄負鍑虹敓鏃ユ湡銆

銆銆鎬у埆鐨勬彁鍙栭渶瑕佸祵濂楀氫釜鍑芥暟瀹屾垚銆傜敤IF鍑芥暟鏉ュ垽鏂璏OD鍑芥暟鐨勫礆紝浠庤屽垽鏂鍑烘у埆鏄鐢鋒垨濂熾傚叕寮忎負錛=IF(MOD(RIGHT(LEFT(A37,17)),2),"鐢","濂")

銆銆鍏朵腑錛孖F鍑芥暟鐨勮娉曠粨鏋勶細IF(鏉′歡,緇撴灉1,緇撴灉2)銆傚規弧瓚蟲潯浠剁殑鏁版嵁榪涜屽勭悊錛屾潯浠舵弧瓚沖垯杈撳嚭緇撴灉1錛屼笉婊¤凍鍒欒緭鍑虹粨鏋2銆傚彲浠ョ渷鐣ョ粨鏋1鎴栫粨鏋2錛屼絾涓嶈兘鍚屾椂鐪佺暐銆

銆銆IF()鍑芥暟錛屾槸涓涓閫昏緫鍒ゆ柇鍑芥暟銆傚傛灉鏉′歡涓虹湡錛岃繑鍥 TRUE錛孴RUE 鍊間負1錛屽惁鍒欏氨榪斿洖FALSE 錛孎ALSE 鍊間負0銆

銆銆鎬у埆鐨勬彁鍙栧彟澶栬繕鏈夊嚑縐嶅叕寮忛兘鍙浠ュ疄鐜般

銆銆絎涓錛=IF(ISODD(MID(A37,8+LEN(A37)/2,1)),"鐢","濂")

銆銆絎浜岋紝=IF(ISODD(MID(A37,IF(LEN(A37)=15,15,17),1)),"鐢","濂")

銆銆絎涓夛紝=IF(MOD(MID(A37,15,3),2),"鐢","濂")

銆銆鍑虹敓鏃ユ湡鐨勬彁鍙栵紝鍙浠ヤ嬌鐢ㄥ叕寮忥細=TEXT(IF(LEN(A37)=15,19,"")&MID(A37,7,6+IF(LEN(A37)=18,2)),"#-00-00")錛岀劧鍚庯紝涓嬫媺瀹炵幇銆

銆銆騫撮緞鍏寮忎負錛=DATEDIF(TEXT(MID(A37,7,6+2*(LEN(A37)=18)),"#-00-00"),NOW(),"y")錛屼笅鎷夈

銆銆絎鍥涢橈細鍒嗗埆鎻愬彇涓嬪浘鎵紺鴻仈緋繪柟寮忎腑鐨勫撳悕鍜岀數璇濄

銆銆絎涓縐嶆柟娉曪細
銆銆鎻愬彇鐢佃瘽錛屽湪E3鍗曞厓鏍艱緭鍏ュ叕寮忥細=MID(A3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&"0123456789")),LEN(A3))錛岀劧鍚庝笅鎷夈
銆銆濮撳悕鎻愬彇錛屽氨鐩存帴浣跨敤錛=SUBSTITUTE(A3,E3,"")錛屼笅鎷夛紝灝監K浜嗐

銆銆絎浜岀嶆柟娉曪細
銆銆鎻愬彇濮撳悕鐨勫叕寮忎負錛=LEFT(A3,LENB(A3)-LEN(A3))
銆銆鎻愬彇鐢佃瘽鐨勫叕寮忎負錛=RIGHT(A3,2*LEN(A3)-LENB(A3))銆

銆銆絎浜旈橈細鎻愬彇涓嬪浘鎵紺哄崟鍏冩牸涓鐨勬暟瀛椼

銆銆鏈変袱縐嶆柟娉曢兘鍙浠ュ疄鐜幫細絎涓錛屼嬌鐢ㄨ嚜瀹氫箟鍑芥暟銆傜浜岋紝浣跨敤宓屽楀嚱鏁板叕寮忋

銆銆浣跨敤涓嬮潰涓や釜鍏寮忎箣浠繪剰涓涓錛岄兘鍙浠ュ疄鐜幫細絎涓錛=-LOOKUP(,-MID(A11,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A11&1234567890)),ROW($1:$15)))
銆銆絎浜岋細=-LOOKUP(,-MIDB(A11,SEARCHB("?",A11),ROW($1:$15)))

銆銆浣嗘槸涓婇潰鍏寮忎腑錛屽傛灉婧愭暟鎹鐨勬暟瀛椾箣鍓嶆湁0錛孡OOKUP鍦ㄦ煡鎵炬椂灝變細琚蹇界暐鎺夈傛瘮濡侭11鍗曞厓鏍間腑鐨235鍙鳳紝濡傛灉鏄0235鍙鳳紝閭ｄ箞浣跨敤涓婇潰鐨勫叕寮忥紝0灝變細琚蹇界暐銆

Ⅱ 濡備綍浠巈xcel涓鎻愬彇鏂囨湰涓鐨勬暟瀛楋紵

鏈渚嬩腑錛屽湪excel涓鎻愬彇鏂囨湰褰撲腑鐨刋XXg,濡傛彁鍙50g 1000g錛屾柟娉曞備笅錛

• 鍋囧畾鏁版嵁鍦ˋ鍒楋紝闇瑕佹彁鍙栫殑鏁版嵁鏀綛鍒椼

• 鍦˙2杈撳叆100g銆

• 閫夊畾B2錛欱18錛岃鋒寜瀹為檯鏁版嵁琛岄夊畾銆

• 鎸夋櫤鑳介敭CTRL+E銆傚嵆瑙佺粨鏋溿

• 娉ㄦ剰錛屾櫤鑳芥彁鍙栨暟鎹鍙鑳芥寜B2鐨勬暟鎹妯″瀷鎻愬彇姣忚屼竴涓鏁版嵁錛屼笉鑳芥彁鍙栨瘡琛屼腑娣峰悎鏁版嵁銆

• 寤鴻錛屽規ょ被鏁版嵁鍏堣繘琛屽墠鏈熷勭悊涓哄崟琛屽崟涓鏁版嵁錛屽啀鐢ㄦ櫤鑳介敭銆

• 濡傛灉闇瑕佹彁鍙栨販鍚堢被鍨嬫暟鎹錛岀敤鍏跺畠鏂規硶銆