excel在批量数字+字母中间加横杠? 您好,请问我如何用excel批量把一串数字和字母中间加横杠 ...
\u5728EXCEL\u4e2d\uff0c\u5982\u4f55\u6279\u91cf\u7ed9\u6570\u5b57\u4e2d\u95f4\u52a0\u6a2a\u7ebf\uff1fExcel\u8868\u683c\u4e2d\u5185\u5bb9\u540e\u9762\u6279\u91cf\u6dfb\u52a0\u4e0b\u5212\u7ebf
\u9009\u4e2d\u90a3\u4e9b\u5355\u5143\u683c\uff0c\u8bbe\u7f6e\u5355\u5143\u683c\u683c\u5f0f\uff0c\u81ea\u5b9a\u4e49\uff0c\u8f93\u5165 0000-0000-00-00 \u5c31\u53ef\u4ee5\u4e86
=REPLACE(REPLACE(A1,6,,"-"),10,,"-")
=REPLACE(REPLACE(A1,6,,"-"),10,,"-")
=REPLACE(REPLACE(A1,6,,"-"),10,,"-")
=REPLACE(REPLACE(A1,6,,"-"),10,,"-")
=REPLACE(REPLACE(A1,6,,"-"),10,,"-")
绛旓細=REPLACE(REPLACE(A1,6,,"-"),10,,"-")
绛旓細瑕佸湪Excel涓壒閲忓湪鏁板瓧涓棿娣诲姞閫楀彿锛屽彲浠ユ寜鐓т互涓嬫楠よ繘琛屾搷浣滐細1. 纭繚浣犺娣诲姞閫楀彿鐨勬暟瀛椾綅浜庝竴涓崟鐙殑鍒椾腑銆2. 鍦‥xcel涓彃鍏ヤ竴涓柊鐨勫垪锛岀敤浜庡瓨鏀炬坊鍔犱簡閫楀彿鐨勬暟瀛椼3. 鍦ㄦ柊鍒楃殑绗竴琛屼腑杈撳叆浠ヤ笅鍏紡锛=TEXT(A1, "#,###")锛屽叾涓瑼1鏄師濮嬫暟瀛楁墍鍦ㄧ殑鍗曞厓鏍笺4. 灏嗚鍏紡搴旂敤鍒版暣涓柊鍒楃殑...
绛旓細=MID(A1,1,2)&" "&MID(A1,3,2)&" "&MID(A1,5,2)&" "&MID(A1,7,2)&" "&MID(A1,7,2)&" "&MID(A1,9,2)&" "&MID(A1,11,2)鍚戜笅鎷夊姩鍏紡濉厖
绛旓細濡傛灉鏄湪excel涓锛屾瘮濡傝浣犵殑08181A1306C8鍦ˋ1涓 =LEFT(A1,2)&"-"&MID(A1,3,2)&"-"&MID(A1,5,2)&"-"&MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&RIGHT(A1,2)
绛旓細=LEFT(A1,8)&"-"&RIGHT(A1,1) 澶嶅埗鍏紡鍗冲彲
绛旓細閫変腑閭d簺鍗曞厓鏍硷紝璁剧疆鍗曞厓鏍兼牸寮忥紝鑷畾涔夛紝杈撳叆 0000-0000-00-00 灏卞彲浠ヤ簡
绛旓細1銆侀鍏堝湪鐢佃剳妗岄潰涓婃墦寮EXCEL銆2銆侀夋嫨闇瑕佺紪杈戠殑琛ㄦ牸锛屽啀鐐瑰嚮榧犳爣鍙抽敭锛岄夋嫨鈥滃鍒垛濓紝鎴栬呯敤澶嶅埗鍔熻兘蹇嵎閿淐trl閿+鈥漜閿溿3銆佸啀鏂板缓涓涓獁ord鏂囨。锛屽啀鐐瑰嚮榧犳爣鍙抽敭閫夋嫨鈥濈矘璐粹滄寜閽4銆侀夋嫨word鏂囨。鏈涓婃柟鐨勫姛鑳借彍鍗曗滃紑濮嬧濅腑鐨勨滄浛鎹⑩濇寜閽5銆佸湪鈥滄煡鎵惧唴瀹光滃悗鐨勭┖鏍间腑杈撳叆鈥漑!0-9]鈥...
绛旓細Excel涓鐨勪竴涓瓧绗︿覆鍑芥暟锛屼綔鐢ㄦ槸浠庝竴涓瓧绗︿覆涓埅鍙栧嚭鎸囧畾鏁伴噺鐨勫瓧绗︺傛牸寮忥細MID(text,start_num,num_chars)鍙傛暟锛歵ext锛屽繀閫夈傚彉浣擄紙瀛楃涓诧級琛ㄨ揪寮忥紝瑕佽鎴彇鐨勫瓧绗︺傚鏋滆鍙傛暟涓篘ull锛屽垯鍑芥暟杩斿洖Null銆俿tart_num锛屽繀閫夈傛暟鍊艰〃杈惧紡锛屼粠宸﹁捣绗嚑浣嶅紑濮嬫埅鍙栥俷um_chars锛屽繀閫夈傚彉浣擄紙鏁板硷級琛ㄨ揪寮忥紝...
绛旓細Option ExplicitSub xxx() Dim c, s, s2, i For Each c In Selection.Cells If VarType(c) = vbString Then s = c.Value s2 = "" For i = 1 To Len(s) s2 = s2 & Mid(s, i, 1) & "*" Next i c.Value = Left(s2, Len(s2) - 1) End...
绛旓細濡傛灉鍙湁杩欏嚑绉嶆牸寮忕殑璇濆彲鐢ㄥ叕寮忥細A2=IF(ISERROR(MID(A1,2,1)+1),LEFT(A1,2)&MID(10^(11-LEN(A1)),2,10)&MID(A1,3,10),LEFT(A1,1)&MID(10^(11-LEN(A1)),2,10)&MID(A1,2,10))鐒跺悗鍚戜笅鎷栥傛敞锛欰鍒椾负鍘熸暟鎹垪銆