ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¨D¤@¦¸­×¥¿©m¦W¿é¤J¼Ò¦¡ªº¤½¦¡

[µo°Ý] ¨D¤@¦¸­×¥¿©m¦W¿é¤J¼Ò¦¡ªº¤½¦¡

¥»©«³Ì«á¥Ñ hkyan ©ó 2018-9-5 16:05 ½s¿è

­n§â¸ê®Æ¤@¦¸¾ã²z,¨D¤½¦¡°£¥h¨§¸¹¤Î¥[¦^MR ¦b«e, ÁÙ¦³¤§«eÂà¤J¦³¤Ö¿ù»~(¨§¸¹«á¨S¦³space),¦³¯à¤@¦¸ªÈ¥¿¶Ü?
[attach]29345[/attach] §Æ±æ®ÄªG¦p¦¹

NAME.rar (6.07 KB)
name update.JPG
§V¤O¾Ç²ß¤¤,ÁÂÁ«e½ú

¦^´_ 1# hkyan


="MR"&SUBSTITUTE(MID(A1,4,99),",","")
¤U©Ô
¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

¦^´_ 2# faye59

³B²z¹Lµ{¤¤,°ÝÃD¨Ó¤F
1) ­Õ­Y¥~ÂǫȨS¦³¤¤¤å©m¦Wªº, «K·|¥X¿ù. ¨Ò¦p
CHOU, QUANTAI ­×¥¿«áÅܧ@ MR U QUANTAI

2) ¤W¤å´£¨ì³¡¥÷©m¦W¦b,¸¹¤§«á¨S¦³spaceªº¤p¿ù»~, ¬O§_´N¤£¯à­×¥¿?
²{Åܧ@³o¼ËMR YIMWAI  LEUNG, ¯à©ó¤½¦¡¤º¤@¦}­×¥¿¬° MR YIM WAI LEUNG ¶Ü?
§V¤O¾Ç²ß¤¤,ÁÂÁ«e½ú

TOP

¦^´_ 3# hkyan


¤å¦rÖߦb¤@°_, µL¤À¹j²Å¸¹, ¤½¦¡¬OµLªk§P§Oªº!!

TOP

¦^´_ 3# hkyan


    ¤£µM¥Îvba¸Õ¸Õ¡A
NAME.rar (22.13 KB)
¥Î¤F²Â¤@ÂIªº¤èªk¹F¨ì§A­nªº®ÄªG¡C
  1. Private Sub CommandButton1_Click()
  2. For Each aa In Sheets("¤u§@ªí1").Range([A1], [A1].End(xlDown)) 'Ū¨úAÄæ¥þ³¡¤å¦r
  3. For i = 1 To Len(aa)
  4. If Mid(aa, i, 1) = "," And Mid(aa, i + 1, 1) <> " " Then '³rÂI«á¨S¦³ªÅ¥Õ°õ¦æ¥[¤JªÅ¥Õ
  5. ar = ar & ", "
  6. Else
  7. ar = ar & Mid(aa, i, 1)
  8. End If
  9. Next
  10. aa.Offset(, 1) = StrType(ar)
  11. ar = ""
  12. Next
  13. End Sub
  14. Function StrType(Mystr)
  15.    For i = 1 To Len(Mystr)
  16.      k = AscW(Mid(Mystr, i))
  17.      Select Case k
  18.      Case 32 'ªÅ®æ
  19.      Textstring = Textstring & Mid(Mystr, i, 1)
  20.      Case 48 To 57 '¼Æ¦r
  21.      Textstring = Textstring & Mid(Mystr, i, 1)
  22.      Case 65 To 90, 97 To 122 '­^¤å
  23.      Textstring = Textstring & Mid(Mystr, i, 1)
  24.      Case 0 To 47, 58 To 64, 91 To 96, 122 To 255 '²Å¸¹
  25.       Textstring = Textstring & ""
  26.     End Select
  27.     Next
  28.    StrType = "MR " & Textstring
  29.    If Mid(StrType, 4, 1) = " " Then StrType = Mid(StrType, 1, 3) & Mid(StrType, 5, 99) '§PÂ_²Ä¥|½X¬O§_¬°ªÅ¥Õ
  30. End Function
½Æ»s¥N½X
¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

¦^´_ 5# faye59

¹Á¸Õ¤¤, ¥t¤@°Ý¦³Ãö¤W¦^±Ð¨ú´À,ªº¤½¦¡
=SUBSTITUTE(MID(A1,1,99),",","")

­Õ­Y¦WºÙ¦³¿é¤J¬° CHING, LOK-MAN»Ý­×¥¿¬° CHING LOK MAN,¨º»ò¦h±ø¥ó¨ú´À­n¦p¦ó³]¸m¤~¯à¦P®É³B²z , ¤Î -
§V¤O¾Ç²ß¤¤,ÁÂÁ«e½ú

TOP

¦^´_ 6# hkyan


CHING, LOK-MAN»Ý­×¥¿¬° CHING LOK MAN¤~¬O
§V¤O¾Ç²ß¤¤,ÁÂÁ«e½ú

TOP

¦^´_ 6# hkyan


    ¥i¥H·j´MAsc¨ç¼Æ¦r¤¸½X¹ï·Óªí¬Ý¬Ý¡A
¤U­±¬O­×§ï-³o­Ó²Å¸¹¡A¦r¤¸½X¬°45¡A¦bFunction¤¤ªºSelect Case¤¤¥[¤J¤@­Ó±ø¥ó§Y¥i¡C
µù¸Ñ§Ú¦A¥´¸Ô²Ó¤@ÂI¡C
  1. Private Sub CommandButton1_Click()
  2. For Each aa In Sheets("¤u§@ªí1").Range([A1], [A1].End(xlDown))'°j°éŪ¨úAÄæ©Ò¦³¸ê®Æ¡A©¹¤UŪ¨ú
  3. For i = 1 To Len(aa)'°j°é¸ÓÄæ¦ì¤å¦r¤¤ªø«×
  4. If Mid(aa, i, 1) = "," And Mid(aa, i + 1, 1) <> " " Then'¦pªGŪ¨ì³r¸¹¨Ã½T»{«á¤è¬O§_¤£¬°ªÅ¥Õ¡A¨âªÌ±ø¥ó§¡²Å¦X°õ¦æ³r¸¹«á­±¥[¤JªÅ¥Õ¡A¦pªG±ø¥ó§¡¤£¦¨¥ß·í«e¤å¦r¥[¤Jar¦r¦ê¤¤
  5. ar = ar & ", "
  6. Else
  7. ar = ar & Mid(aa, i, 1)
  8. End If
  9. Next
  10. aa.Offset(, 1) = StrType(ar)'§PŪ¦r¦ê§¹²¦«áar¤¤ªº±ø¥ó¡A¸õ¤JFunctionµ{§Ç¡Aµ²ªG¦^¶Çaa©¹¥k¥[¤@®æ¦ì¸m
  11. ar = ""'²MªÅ¦r¦ê
  12. Next
  13. End Sub
  14. Function StrType(Mystr)
  15.    For i = 1 To Len(Mystr)'°j°é¸ÓÄæ¦ì¤å¦r¤¤ªø«×
  16.      k = AscW(Mid(Mystr, i))'¨ú±oªº¸Ó¤å¦rAsc¨ç¼ÆÂà´«¦r¤¸½X
  17.      Select Case k'¿ï¨ú¦r¤¸½X²Å¦XCase¤¤­þ¤@­Ó±ø¥ó
  18.      Case 32 'ªÅ®æ
  19.      Textstring = Textstring & Mid(Mystr, i, 1)
  20.      Case 48 To 57 '¼Æ¦r
  21.      Textstring = Textstring & Mid(Mystr, i, 1)
  22.      Case 65 To 90, 97 To 122 '­^¤å
  23.      Textstring = Textstring & Mid(Mystr, i, 1)
  24.      Case 0 To 44, 46 To 47, 58 To 64, 91 To 96, 122 To 255 '²Å¸¹
  25.       Textstring = Textstring & ""
  26.      Case 45 '¹³¬O³o¸Ì - ³o­Ó²Å¸¹ªº¦r¤¸½X¬°45¡A¦b¦r¦ê¤¤§â¥¦§ï¬°ªÅ®æ´N¥i¥H
  27.      Textstring = Textstring & " "
  28.     End Select
  29.     Next
  30.    StrType = "MR " & Textstring'¦r¦ê§¹²¦«á¦b¦r¦ê«e¥[¤JMR+ªÅ®æ¡A¦^¶Ç§¹¾ã¦r¦ê
  31.    If Mid(StrType, 4, 1) = " " Then StrType = Mid(StrType, 1, 3) & Mid(StrType, 5, 99)'¦pªG¤å¦r¤¤²Ä¥|½X³o¤@­Ó¦r¬°ªÅ¥Õ¡A­«·s¦r¦ê1~3¤Î5~³Ì«á¤@½X¡Aµ¥¦P©ó§R°£²Ä¥|½XªÅ®æ
  32. End Function
½Æ»s¥N½X
³o¤äµ{§Ç¦³ÂI°½Ãi¡A¤£¦n·N«ä...ÁÙ¦³«Ü¦h¦a¤è¥i¥Hºë²ªº¦a¤è...
¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

        ÀR«ä¦Û¦b : ½_ÁJµ²±o¶V¹¡º¡¡A¶V·|©¹¤U««¡A¤@­Ó¤H¶V¦³¦¨´N¡A´N­n¶V¦³Á¾¨Rªº¯ÝÃÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD