excel中截取字符串
excel\u4e2d\u6309\u67d0\u5b57\u7b26\u622a\u53d6\u5b57\u7b26\u4e32\u9009\u4e2d\u9700\u8981\u5904\u7406\u7684\u539f\u5355\u5143\u683c\uff1a\u5929\u6d25\u5206\u516c\u53f8/\u6e24\u6d77\u4f5c\u4e1a\u533a/\u5f00\u53d1\u90e8/\u5f20\u4e09\uff0c\u6570\u636e/\u5206\u5217/\u4e0b\u4e00\u6b65/\u5728\u51fa\u6765\u7684\u5206\u9694\u7b26\u53f7\u4e2d\u9009\u5176\u4ed6\uff0c\u5728\u53f3\u8fb9\u7684\u65b9\u6846\u4e2d\u8f93\u5165/\u4e0b\u4e00\u6b65/\u5b8c\u6210\uff0c\u5c31\u4f1a\u8fbe\u5230\u4f60\u8981\u7684\u6548\u679c
1.\u9884\u5148\u51c6\u5907\u7684\u6570\u636e\u6e90\u5982\u4e0b\u6240\u793a\uff08\u524d\u4e09\u884c\u662f18\u4f4d\uff0c\u6700\u540e\u4e00\u884c\u662f17\u4f4d\uff09\uff1a
2.\u5148\u5904\u7406A1\u4e2d\u7684\u6570\u636e\uff0c\u5728B1\u4e2d\u8f93\u5165\u516c\u5f0f\uff1a\u201c=if(len(A1)=18,Mid(A1,1,6)&Mid(A1,9,9),)\u201d(\u51fd\u6570\u89e3\u91ca\uff1aif\u7528\u4e8e\u5224\u65ad\u5f85\u5904\u7406\u7684\u5355\u5143\u683c\u7684\u4f4d\u6570\u662f\u5426\u4e3a18\u4f4d\uff0c\u5982\u679c\u6210\u7acb\uff0c\u6267\u884cMid(A1,1,6)&Mid(A1,9,9)\uff0c\u5982\u679c\u4e0d\u6210\u7acb\uff0c\u6267\u884c\u6700\u540e\u4e00\u4e2a\u53c2\u6570\uff0c\u8fd9\u91cc\u5df2\u7701\u7565(\u6700\u540e\u4e00\u4e2a\u9017\u53f7\u540e\u9762\u7684\u53c2\u6570\u5df2\u7ecf\u7701\u7565)\u3002\u5176\u4e2dMid(A1,1,6)\uff1aMid\u51fd\u6570\u5bf9A1\u8fdb\u884c\u5904\u7406\uff0c\u4ece\u7b2c1\u4f4d\u5f00\u59cb\uff0c\u622a\u53d6\u957f\u5ea6\u4e3a6\u7684\u4e00\u4e2a\u5b57\u7b26\u4e32\u3002&\uff1a\u4e3a\u8fde\u63a5\u5b57\u7b26\u3002Mid(A1,9,9)\uff1a\u7406\u89e3\u540c\u4e0a\u3002)
3.\u6309\u4e0b\u201cEnter\u201d\u952e\uff0cB1\u7684\u7ed3\u679c\u5df2\u7ecf\u751f\u6210\u3002
4.\u9009\u4e2dB1\uff0c\u5e76\u5c06\u9f20\u6807\u79fb\u5230B1\u5355\u5143\u683c\u7684\u53f3\u4e0b\u89d2\uff0c\u5f62\u6210\u201c\u5341\u5b57\u72b6\u201d\u540e\uff0c\u6309\u4e0b\u5de6\u952e\u4e0d\u677e\uff0c\u5411\u4e0b\u586b\u5145\u3002\uff08\u5176\u4e2d\u6700\u540e\u4e00\u4e2a\u503c\u4e3a0\uff0c\u8868\u660eif\u5224\u65ad\u4e0d\u6210\u7acb\u3002\uff09
=IF(ISERROR(FIND("http://www.appannie.com/apps/google-play/app/",A2)),"",MID(A2,LEN("http://www.appannie.com/apps/google-play/app/")+1,LEN(A2)))
然后下拉填充即可。
查找：短字符串
替换：空
或：=substitute("长字符串","短字符串","")
假设地址在A1
B1公式 = right(a1,len(a1)-find("@",substitute(a1,"/","@",len(a1)-len(substitute(a1,"/",""))-1)))
使用分列，间隔符号用“/”
用right函数就可以了通过len函数来返回相应的字符长度
绛旓細1銆侀鍏堟墦寮鐢佃剳閲岀殑excel杞欢鎵撳紑鏁版嵁锛屽涓嬪浘鎵绀轰竴涓崟鍏冩牸涓瓧绗︿覆寰堥暱锛屾垜鐜板湪鎯宠鎻愬彇鍗曞厓鏍煎墠3涓瓧绗︺2銆佸湪C2鍗曞厓鏍间腑杈撳叆鈥=LEFT(B2,3)鈥滐紝鎸夊洖杞﹂敭锛屽嵆鍙彁鍙栧墠3涓瓧绗︺3銆佸叧浜嶭EFT鍑芥暟鍏紡鐨勮В閲婏紝鍦ㄦ湰渚嬩腑灏辨槸灏咮2鍗曞厓鏍间腑鐨勫瓧绗︿覆涓殑鍓3涓瓧绗︽彁鍙栧嚭鏉ャ4銆佽嫢鏄垜鎯宠鎻愬彇瀛楃涓...
绛旓細琛ㄨ堪涓猴細棣栧厛浠嶦2鍗曞厓鏍肩涓涓瓧绗︿粠宸﹀線鍙冲彇5涓瓧绗︼紝鐒跺悗鍐嶅鍙栧嚭鏉ョ殑5涓瓧绗︿粠鍙冲悜宸﹀彇3涓瓧绗銆5銆佸幓鎺夊瓧绗﹀墠闈㈢殑鍑犱釜瀛楃 =RIGHT(E2,LEN(E2)-2)琛ㄨ堪涓猴細浠庡崟鍏冩牸E2瀛楃鐨勫彸杈硅捣锛屾彁璧锋墍鏈夌殑瀛楃鏁帮紝鍘绘帀鍚庨潰涓や釜瀛楃銆侺EN琛ㄧず杩斿洖瀛楃涓蹭腑鐨勫瓧绗︽暟銆6銆佸幓鎺夊瓧绗﹀悗闈㈢殑鍑犱釜瀛楃 =LEFT(E2,...
绛旓細1銆侀鍏堬紝鎵撳紑excel琛紝閫変腑瑕佺紪杈戠殑鍗曞厓鏍硷紱2銆佺偣鍑昏彍鍗曟爮鐨勫叕寮忊斺斺滄彃鍏ュ嚱鏁扳锛3銆佹帴涓嬫潵璁剧疆鍑芥暟鍙傛暟锛宼ext杈撳叆瑕佺紪杈戠殑鏂囨湰瀛楃涓诧紱4銆乶um_chars琛ㄧず鎴彇鐨勫瓧绗︿覆涓暟锛岀4-6浣嶅瓧绗︽湁3涓暟锛屾垜浠緭鍏3锛岀偣鍑荤‘瀹氬悗锛屾垜浠氨鑾峰緱浜嗙4-6浣嶆暟鐨勫瓧绗︺
绛旓細鍏朵腑锛歵ext琛ㄧず瑕佹埅鍙栫殑瀛楃涓诧紝start_num琛ㄧず浠庣鍑犱綅瀛楃涓插紑濮嬫埅鍙栵紝num_chars琛ㄧず瑕佹埅鍙栫殑瀛楃鏁銆侲ND 鍏蜂綋瀹炰緥 浠2鍗曞厓鏍肩殑鈥滀腑鍗庝汉姘戝叡鍜屽浗鈥濅负渚嬶紝鍒嗗埆浠庡乏锛屼粠鍙冲拰涓棿鎴彇閮ㄥ垎瀛楃涓层傚湪C3鍗曞厓鏍艰緭鍏=left(A2,2)C3鍗曞厓鏍兼埅鍙栧埌鈥滀腑鍗庘濆湪C5鍗曞厓鏍艰緭鍏=right(A2,3)C5鍗曞厓鏍兼埅鍙栧埌鈥滃叡鍜屽浗...
绛旓細鍦‥xcel涓紝鎴戜滑鍙互浣跨敤鍝簺鏂规硶鏉ユ埅鍙栧瓧绗︿覆鍛紵閫氬父鏈変袱绉嶆柟娉曪細浣跨敤MID鍑芥暟鍜孡EN鍑芥暟缁撳悎浣跨敤锛屾垨鑰呬娇鐢∕ID鍜孎IND鍑芥暟銆傞鍏堬紝浣跨敤MID鍑芥暟锛屽畠鍙互鎻愬彇鍑轰粠鎸囧畾浣嶇疆寮濮嬬殑鐗瑰畾闀垮害鐨勫瓧绗︿覆銆備緥濡傦紝鍋囪鎴戜滑鏈変竴涓崟鍏冩牸A1鍖呭惈涓涓暱瀛楃涓诧紝鎴戜滑鎯宠鎴彇浠庣5涓瓧绗﹀紑濮嬬殑5涓瓧绗︼紝鎴戜滑鍙互杩欐牱鎿嶄綔锛=...
绛旓細1銆侀鍏堢偣鍑诲崟鍏冩牸锛岃緭鍏鎴彇瀛楃涓鍑芥暟锛屾瘮濡傗=LEFT鈥濓紝鏄粠宸﹁竟绗竴涓紑濮嬫埅鍙栧瓧绗︺2銆佹牴鎹鎴彇鐨勫瓧绗﹀崟鍏冩牸锛岃緭鍏ュ崟鍏冩牸锛屽啀杈撳叆鏁伴噺銆3銆佸畬鎴愬悗锛岃〃鏍煎凡缁忔埅鍙栧瓧绗︿簡銆4銆佺浉鍚岀殑鏂规硶锛屽鏋滄槸瑕佹埅鍙栦粠鍙宠竟鏈鍚庝竴浣嶅紑濮嬬殑瀛楃锛岃緭鍏ュ嚱鏁扳=RIGHT鈥濄5銆佹牴鎹鎴彇鐨勫瓧绗﹀崟鍏冩牸锛岃緭鍏ュ崟鍏冩牸锛屽啀...
绛旓細1. 棣栧厛鎵撳紑鐢佃剳涓婃兂瑕佺紪杈戠殑Excel琛ㄦ牸锛屽鍥撅紝鎯宠鎻愬彇鈥.鈥濆瓧绗﹀墠闈㈢殑瀛楃 2. 濡傚浘锛屾垜浠彲浠ョ湅鍒帮紝.涔嬪墠鐨瀛楃涓闀跨煭涓嶄竴锛屾垜浠夋嫨浣跨敤FIND鍑芥暟鏉ョ‘瀹氬皬鏁扮偣鐨勪綅缃紝鐒跺悗鍐嶇‘瀹鎴彇鐨勫瓧绗︾殑涓暟銆侳IND鍑芥暟灏辨槸鍙互杩斿洖瀛楃鍦ㄥ瓧绗︿覆涓殑璧峰浣嶇疆銆3. 鎺ヤ笅鏉ワ紝鍦˙2鍗曞厓鏍硷紝杈撳叆鍏紡锛=LEFT(A2,FIND(...
绛旓細浣跨敤excel鏃讹紝鎯宠鎴彇瀛楃涓涓殑涓閮ㄥ垎锛屽彲浠ヤ娇鐢∕ID鍑芥暟鎴彇锛屼互涓嬩负璇︾粏姝ラ璇存槑锛氬伐鍏凤紡鍘熸枡锛氳仈鎯矴eekPro2021銆乄indows10銆Excel2021 1銆侀夋嫨鍏紡 鎵撳紑excel锛屽湪宸ュ叿鏍忕偣鍑诲叕寮忋2銆侀夋嫨鎻掑叆鍑芥暟 鍦ㄦ彃鍏ラ夐」涓嬶紝鐐瑰嚮鎻掑叆鍑芥暟銆3銆侀夋嫨MID鍑芥暟 鍦ㄦ彃鍏ュ嚱鏁扮晫闈紝鐐瑰嚮MID銆4銆侀夋嫨鍙傛暟 鍦ㄥ嚱鏁板弬鏁扮晫闈紝閫夋嫨...
绛旓細Excel涓埅鍙栧瓧绗︿覆鐨勫嚱鏁版垜鐭ラ亾鐨勬湁涓夌锛1銆丩EFT()鍑芥暟锛氫粠宸︽埅鍙栨寚瀹氫釜鏁板瓧绗 2銆丷IGHT()鍑芥暟锛氫粠鍙虫埅鍙栨寚瀹氫釜鏁板瓧绗 3銆丮ID()鍑芥暟锛氫粠鍙栨寚瀹氫綅缃埅鍙栨寚瀹氫釜鏁板瓧绗
绛旓細1.left鍑芥暟鐢ㄦ潵瀵瑰崟鍏冩牸鍐呭杩涜鎴彇銆備粠宸﹁竟绗竴涓瀛楃寮濮嬫埅鍙栵紝鎴彇鎸囧畾鐨勯暱搴︺2.left鍑芥暟鐨勮娉曟牸寮 =left(text,num_chars)锛宼ext浠ｈ〃鐢ㄦ潵鎴彇鐨勫崟鍏冩牸鍐呭锛宯um_chars浠ｈ〃浠庡乏寮濮嬫埅鍙栫殑瀛楃鏁般3.濡傚浘鎵绀猴紝瀵逛簬涓闀夸覆鍦板潃涓彁鍙栫渷浠斤紝杈撳叆鍏紡=LEFT(A1,3)锛屽湪A1涓粠宸︽彁鍙3涓瓧绗︼紝鍗冲箍涓滅渷...
