如何提取信息中的数字

Ⅰ 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镄勬暟鎹妯″瀷鎻愬彇姣忚屼竴涓鏁版嵁锛屼笉鑳芥彁鍙栨疮琛屼腑娣峰悎鏁版嵁銆

• 寤鸿锛屽规ょ被鏁版嵁鍏堣繘琛屽墠链熷勭悊涓哄崟琛屽崟涓鏁版嵁锛屽啀鐢ㄦ櫤鑳介敭銆

• 濡傛灉闇瑕佹彁鍙栨贩钖堢被鍨嬫暟鎹锛岀敤鍏跺畠鏂规硶銆