excel怎么截取字符串 excel中怎么截取字符串？
EXCEL,\u600e\u4e48\u6837\u53ef\u4ee5\u4ece\u4e00\u4e32\u5b57\u7b26\u4e2d\u7684\u67d0\u4e2a\u6307\u5b9a\u4f4d\u7f6e\u622a\u53d6\u5b57\u7b26.Excel\u4e2d\u622a\u53d6\u5b57\u7b26\u4e32\u7684\u65b9\u6cd5\u53ca\u6b65\u9aa4\uff1a
1. left\u51fd\u6570\u7528\u6765\u5bf9\u5355\u5143\u683c\u5185\u5bb9\u8fdb\u884c\u622a\u53d6\u3002\u4ece\u5de6\u8fb9\u7b2c\u4e00\u4e2a\u5b57\u7b26\u5f00\u59cb\u622a\u53d6\uff0c\u622a\u53d6\u6307\u5b9a\u7684\u957f\u5ea6\u3002
2. left\u51fd\u6570\u7684\u8bed\u6cd5\u683c\u5f0f\uff0c=left(text,num_chars)\uff0ctext\u4ee3\u8868\u7528\u6765\u622a\u53d6\u7684\u5355\u5143\u683c\u5185\u5bb9\u3002num_chars\u4ee3\u8868\u4ece\u5de6\u5f00\u59cb\u622a\u53d6\u7684\u5b57\u7b26\u6570\u3002
3. \u5982\u56fe\u6240\u793a\uff0c\u5bf9\u4e8e\u4e00\u957f\u4e32\u5730\u5740\u4e2d\u63d0\u53d6\u7701\u4efd\u3002\u8f93\u5165\u516c\u5f0f=LEFT(A1,3)\uff0c\u5728A1\u4e2d\u4ece\u5de6\u63d0\u53d63\u4e2a\u5b57\u7b26\uff0c\u5373\u5e7f\u4e1c\u7701\u3002
4.left\u51fd\u6570\u5728\u7efc\u5408\u8fd0\u7528\u3002=IF(LEFT(A6,1)="B","\u5317\u4eac",IF(LEFT(A6,1)="S","\u4e0a\u6d77","\u91cd\u5e86"))\uff0c\u5982\u56fe\uff0c\u516c\u5f0f\u542b\u4e49\u5982\u679cA6\u5355\u5143\u683c\u7684\u7b2c\u4e00\u4e2a\u5b57\u7b26\u662fB\uff0c\u5219\u8fd4\u56de\u5317\u4eac\uff0c\u5426\u5219\u6267\u884c\u6761\u4ef6A6\u5355\u5143\u683c\u7684\u7b2c\u4e00\u4e2a\u5b57\u7b26\u662fS\uff0c\u5219\u8fd4\u56de\u4e0a\u6d77\uff0c\u5426\u5219\u8fd4\u56de\u91cd\u5e86\u3002\u6709\u5173if\u51fd\u6570\u7684\u8be6\u7ec6\u7528\u6cd5\u53ef\u4ee5\u89c2\u770b\u5c0f\u7f16\u7684\u7ecf\u9a8cExcel If\u51fd\u6570\u600e\u4e48\u7528\u3002
5. =left(text,num_chars)\u56fe\u4e2d\u53ef\u4ee5\u770b\u51fa\uff0cnum_chars\u7684\u503c\u5fc5\u987b\u5927\u4e8e\u7b49\u4e8e0.\u5c0f\u4e8e0\u65f6\u76f4\u63a5\u62a5\u9519\uff0c\u7b49\u4e8e0\u65f6\u4e3a\u7a7a\u503c\u3002
6. \u5982\u56fe\uff0c\u5982\u679c\u7701\u7565num_chars\u7684\u8bdd\uff0cnum_chars\u7684\u503c\u5c31\u9ed8\u8ba4\u4e3a1\u3002
7. \u5982\u679c num_chars \u5927\u4e8e\u6587\u672c\u957f\u5ea6\uff0c\u5219 \u8fd4\u56de\u6240\u6709\u6587\u672c\u3002
8. \u5982\u56fe\u6240\u793a\uff0c\u4e00\u4e2a\u7a7a\u683c\u4e5f\u7b97\u4e00\u4e2a\u5b57\u7b26\uff0c\u8fd9\u91cc\u8981\u63d0\u53d6\u5230D,num_chars\u7684\u503c\u4e3a5\u800c4\u3002
\u5982\u679c\u6570\u636e\u683c\u5f0f\u56fa\u5b9a\u7684\u8bdd\uff0c\u5219\u7528\u5982\u4e0b\u516c\u5f0f
\u5047\u5b9a\u65f6\u95f4\u6240\u5728\u5355\u5143\u683c\u4e3aA1\uff0cB1=IF(A1="","",IFERROR("20"&MID(A1,FIND("-",A1,5)+1,2)&"-"&MID(A1,FIND("-",A1,1)+1,1)&"-"&MID(A1,1,2)&" "&MID(A1,FIND("\u65f6",A1,1)-2,2)&":"&MID(A1,FIND("\u5206",A1,1)-2,2),"\u8f6c\u6362\u51fa\u9519"))\uff0c\u7136\u540e\u5f80\u4e0b\u4e00\u62c9\u5373\u53ef
\u5982\u679cA\u5217\u4e3a\u7a7a\u5219\u4e3a\u7a7a\uff0c\u5982\u679cA\u5217\u65f6\u95f4\u683c\u5f0f\u9519\u8bef\uff0cB\u4f1a\u663e\u793a\u201c\u8f6c\u6362\u51fa\u9519"
\u7528\u516c\u5f0f\u7684\u65f6\u5019\u6709\u4e24\u5927\u524d\u63d0\u5fc5\u987b\u6ce8\u610f\uff0c\u4e00\u4e2a\u662f\u7a7a\u503c\uff0c\u518d\u4e00\u4e2a\u5c31\u662f\u51fa\u9519\u600e\u4e48\u529e
Excel中截取字符串的函数我知道的有三种：
1、LEFT()函数：从左截取指定个数字符
2、RIGHT()函数：从右截取指定个数字符
3、MID()函数：从取指定位置截取指定个数字符
Sub 数据截取保存为txt()
Dim MyRowNo As Long, MySplitNum As Long, MyStartNo As Long, MyI As Long, MyOutDir As String
Dim MyCh, MyRows, MyCols, i
MySplitNum = 100 '截取每100行保存为txt文件，可自行修改该数字
If MySplitNum < 1 Then MySplitNum = 100
MyI = 1
MyCh = 65
MyOutDir = "C:\Documents and Settings\Administrator\桌面\11\" '截取的内容保存为txt时的输出目录，可自行修改
If Dir(MyOutDir, vbDirectory) = "" Then
MsgBox "输出目录不存在，请重新指定或者新建该目录后重试！", vbCritical, "警告"
Exit Sub
End If
ActiveSheet.UsedRange.Select
MyRows = ActiveSheet.UsedRange.Rows.Count
MyCols = ActiveSheet.UsedRange.Columns.Count
If MyRows = 1 And MyCols = 1 Then
MsgBox "目标表为空表，操作被取消！", vbInformation, "消息"
Exit Sub
ElseIf MyRows <= MySplitNum Then
MsgBox "目标表行数过少，无须分切，操作被取消！", vbInformation, "消息"
Exit Sub
End If
Open MyOutDir & "1A.txt" For Output As #11
For i = 1 To ActiveSheet.UsedRange.Rows.Count
Print #11, ActiveSheet.UsedRange.Cells(i, 1)
'Print # 语句将数据写入一个文件
If i Mod MySplitNum = 0 Then
Close #11
MyI = MyI + 1
MyCh = MyCh + 1
Open MyOutDir & Trim(str(MyI)) & Chr(MyCh) & ".txt" For Output As #11
'#11文件号，就是标记打开了哪个文件,打开多个的时候区分使用哪个文件11标号(1-512随意取)
'str将一个数字转成字符串,当数字转成字符串时，字符串的第一个位一定是空格或是正负号。
'Chr 函数来返回指定字符码所代表的字符。
'LTrim没有前导空白,RTrim没有尾随空白,Trim没有前导和尾随空白。
If MyCh = 90 Then MyCh = 64
End If
Next
On Error Resume Next
Close #11
MsgBox "数据截取保存完毕！", vbInformation, "消息"
End Sub
用mid或left或right函数就可以了。
绛旓細1銆侀鍏堟墦寮鐢佃剳閲岀殑excel杞欢鎵撳紑鏁版嵁锛屽涓嬪浘鎵绀轰竴涓崟鍏冩牸涓瀛楃涓寰堥暱锛屾垜鐜板湪鎯宠鎻愬彇鍗曞厓鏍煎墠3涓瓧绗︺2銆佸湪C2鍗曞厓鏍间腑杈撳叆鈥=LEFT(B2,3)鈥滐紝鎸夊洖杞﹂敭锛屽嵆鍙彁鍙栧墠3涓瓧绗︺3銆佸叧浜嶭EFT鍑芥暟鍏紡鐨勮В閲婏紝鍦ㄦ湰渚嬩腑灏辨槸灏咮2鍗曞厓鏍间腑鐨勫瓧绗︿覆涓殑鍓3涓瓧绗︽彁鍙栧嚭鏉ャ4銆佽嫢鏄垜鎯宠鎻愬彇瀛楃涓...
绛旓細閫氬父鏈変袱绉嶆柟娉曪細浣跨敤MID鍑芥暟鍜孡EN鍑芥暟缁撳悎浣跨敤锛屾垨鑰呬娇鐢∕ID鍜孎IND鍑芥暟銆傞鍏堬紝浣跨敤MID鍑芥暟锛屽畠鍙互鎻愬彇鍑轰粠鎸囧畾浣嶇疆寮濮嬬殑鐗瑰畾闀垮害鐨勫瓧绗︿覆銆備緥濡傦紝鍋囪鎴戜滑鏈変竴涓崟鍏冩牸A1鍖呭惈涓涓暱瀛楃涓诧紝鎴戜滑鎯宠鎴彇浠庣5涓瓧绗﹀紑濮嬬殑5涓瓧绗︼紝鎴戜滑鍙互杩欐牱鎿嶄綔锛=MID(A1, 5, 5)銆傚彟澶栵紝鎴戜滑涔熷彲浠ヤ娇鐢‵IND鍑芥暟鎵...
绛旓細鍙互鐢↙eft鍑芥暟鎴栬卪id鍑芥暟 鍏蜂綋鎿嶄綔鏂规硶濡備笅锛1.left鍑芥暟鐢ㄦ潵瀵瑰崟鍏冩牸鍐呭杩涜鎴彇銆備粠宸﹁竟绗竴涓瓧绗﹀紑濮嬫埅鍙栵紝鎴彇鎸囧畾鐨勯暱搴︺2.left鍑芥暟鐨勮娉曟牸寮 =left(text,num_chars)锛宼ext浠ｈ〃鐢ㄦ潵鎴彇鐨勫崟鍏冩牸鍐呭锛宯um_chars浠ｈ〃浠庡乏寮濮嬫埅鍙栫殑瀛楃鏁般3.濡傚浘鎵绀猴紝瀵逛簬涓闀夸覆鍦板潃涓彁鍙栫渷浠斤紝杈撳叆鍏紡=LEFT(...
绛旓細鍏朵腑锛歵ext琛ㄧず瑕佹埅鍙栫殑瀛楃涓诧紝start_num琛ㄧず浠庣鍑犱綅瀛楃涓插紑濮嬫埅鍙栵紝num_chars琛ㄧず瑕佹埅鍙栫殑瀛楃鏁銆侲ND 鍏蜂綋瀹炰緥 浠2鍗曞厓鏍肩殑鈥滀腑鍗庝汉姘戝叡鍜屽浗鈥濅负渚嬶紝鍒嗗埆浠庡乏锛屼粠鍙冲拰涓棿鎴彇閮ㄥ垎瀛楃涓层傚湪C3鍗曞厓鏍艰緭鍏=left(A2,2)C3鍗曞厓鏍兼埅鍙栧埌鈥滀腑鍗庘濆湪C5鍗曞厓鏍艰緭鍏=right(A2,3)C5鍗曞厓鏍兼埅鍙栧埌鈥滃叡鍜屽浗...
绛旓細1銆侀鍏堬紝鎵撳紑excel琛紝閫変腑瑕佺紪杈戠殑鍗曞厓鏍硷紱2銆佺偣鍑昏彍鍗曟爮鐨勫叕寮忊斺斺滄彃鍏ュ嚱鏁扳濓紱3銆佹帴涓嬫潵璁剧疆鍑芥暟鍙傛暟锛宼ext杈撳叆瑕佺紪杈戠殑鏂囨湰瀛楃涓诧紱4銆乶um_chars琛ㄧず鎴彇鐨勫瓧绗︿覆涓暟锛岀4-6浣嶅瓧绗︽湁3涓暟锛屾垜浠緭鍏3锛岀偣鍑荤‘瀹氬悗锛屾垜浠氨鑾峰緱浜嗙4-6浣嶆暟鐨勫瓧绗︺
绛旓細1銆侀鍏堢偣鍑诲崟鍏冩牸锛岃緭鍏鎴彇瀛楃涓鍑芥暟锛屾瘮濡傗=LEFT鈥濓紝鏄粠宸﹁竟绗竴涓紑濮嬫埅鍙栧瓧绗︺2銆佹牴鎹鎴彇鐨勫瓧绗﹀崟鍏冩牸锛岃緭鍏ュ崟鍏冩牸锛屽啀杈撳叆鏁伴噺銆3銆佸畬鎴愬悗锛岃〃鏍煎凡缁忔埅鍙栧瓧绗︿簡銆4銆佺浉鍚岀殑鏂规硶锛屽鏋滄槸瑕佹埅鍙栦粠鍙宠竟鏈鍚庝竴浣嶅紑濮嬬殑瀛楃锛岃緭鍏ュ嚱鏁扳=RIGHT鈥濄5銆佹牴鎹鎴彇鐨勫瓧绗﹀崟鍏冩牸锛岃緭鍏ュ崟鍏冩牸锛屽啀...
绛旓細excel鎴彇瀛楃涓涓殑涓閮ㄥ垎鎬庝箞鎿嶄綔鎴彇瀛楃涓插父鐢ㄥ嚱鏁板父鐢ㄧ殑鍑芥暟鏄細left鍑芥暟銆乺ight鍑芥暟銆乵id鍑芥暟left鍑芥暟璇硶锛歭eft(text锛宯um_chars)锛屼粠宸﹁竟鍒囧嚭瀛楃涓茬殑涓閮ㄥ垎right鍑芥暟璇硶锛歳ight(text锛宯um_chars)锛屼粠鍙充晶鍒囧嚭瀛楃涓茬殑涓閮ㄥ垎杩欓噷锛宼ext琛ㄧず瑕佸垏鍑哄瓧绗︿覆锛宯um_chars琛ㄧず瑕佸垏鍑虹殑瀛楃鏁癿id鍑芥暟璇硶锛...
绛旓細1. 棣栧厛鎵撳紑鐢佃剳涓婃兂瑕佺紪杈戠殑Excel琛ㄦ牸锛屽鍥撅紝鎯宠鎻愬彇鈥.鈥濆瓧绗﹀墠闈㈢殑瀛楃 2. 濡傚浘锛屾垜浠彲浠ョ湅鍒帮紝.涔嬪墠鐨勫瓧绗︿覆闀跨煭涓嶄竴锛屾垜浠夋嫨浣跨敤FIND鍑芥暟鏉ョ‘瀹氬皬鏁扮偣鐨勪綅缃紝鐒跺悗鍐嶇‘瀹氭埅鍙栫殑瀛楃鐨勪釜鏁般侳IND鍑芥暟灏辨槸鍙互杩斿洖瀛楃鍦ㄥ瓧绗︿覆涓殑璧峰浣嶇疆銆3. 鎺ヤ笅鏉ワ紝鍦˙2鍗曞厓鏍硷紝杈撳叆鍏紡锛=LEFT(A2,FIND(...
绛旓細1.棣栧厛鍙屽嚮鎵撳紑鎴戜滑闇瑕佸鐞嗙殑excel琛ㄦ牸锛屽鍥撅紝鍋囧鎴戜滑闇瑕佹彁鍙栦竴涓嬪湴鍧鐨勬墍鍦ㄥ競銆2.濡傚浘鎴戜滑鍦≒2鍗曞厓鏍艰緭鍏モ=鈥濈劧鍚庡湪涓婃柟閫夋嫨鈥渇x鎻掑叆鍑芥暟鍥炬爣銆3.濡傚浘鎴戜滑鍙互鍦ㄤ笅鏂规粦鍔ㄦ壘鍒扳滃父鐢ㄥ嚱鏁---LEFT鈥濓紝涔熷彲浠ュ湪鏌ユ壘鍑芥暟澶勭洿鎺ヨ緭鍏eft锛岀劧鍚庡啀閫夋嫨LEFT锛屾渶鍚庘滅‘瀹氣濆嵆鍙4. 鎺ヤ笅鏉ュ湪瀛楃涓杈撳叆鈥淥2:O...
绛旓細1銆佹墦寮闇瑕佹搷浣滅殑EXCEL琛ㄦ牸锛岀偣鍑诲伐鍏锋爮鐨勨滃叕寮忊濄2銆佹壘鍒板苟鐐瑰嚮鈥滄枃鏈濄3銆佺劧鍚庡湪涓嬫媺閫夐」涓夋嫨鈥淢ID鈥濄4銆佸湪寮瑰嚭鐨勫弬鏁拌缃獥鍙ｏ紝绗竴涓弬鏁版槸闇瑕佹彁鍙栫殑鍘瀛楃涓鎵鍦ㄥ崟鍏冩牸锛岀浜屼釜鍙傛暟杈撳叆鈥1鈥濓紝鏈鍚庝竴涓緭鍏モ3鈥濓紝鐒跺悗鐐瑰嚮纭畾鎸夐挳鍗冲彲銆傘怣ID鍑芥暟鎵ц缁撴灉浠嶢1鍗曞厓鏍肩涓涓瓧绗﹁捣鎻愬彇3...
