[µo°Ý] ¦U¦ì¦n¡A§Ú·Q½Ð°Ý¤@­Ó¸ê®Æ­åªRªº°ÝÃD

ss961471

°²¦p§Ú·Q§â¤@­Ó¦r¦ê¦p¤U
111.222.333.444
­Ó§O¤À¶}¦U¦Û¤@­ÓÀx¦s®æ¡A§Ú¥i¥H¥Î¸ê®Æ­åªR¨Ó³B²z
¦ý¦³¨S¦³¿ìªk¥i¥H¥Î¨ç¼Æ¨Ó³B²z?
§Ú·Q¨C¦¸±N¸ê®Æ¶K¶iA1«á¡A´N¦Û°Ê¯à¦bB1¡BC1¡BD1¡BE1¶K¤W¦U¦Ûªº¦r¦ê
½Ð°Ý¦³¿ìªk¶Ü?

sunnyso

¦^´_ 1# ss961471

¥Î¥H¤U¤½¦¡¨S¦³ªø«×­­¨î

=IF(LEN($A$8)-LEN(SUBSTITUTE($A$8,".",""))<COLUMN(A1)-1,"",MID($A$8,FIND(CHAR(1),SUBSTITUTE("." & $A$8&".",".",CHAR(1),COLUMN(A1))),FIND(CHAR(1),SUBSTITUTE("." &$A$8&".",".",CHAR(1),COLUMN(B1)))-FIND(CHAR(1),SUBSTITUTE("." & $A$8&".",".",CHAR(1),COLUMN(A1)))-1))

A8=12131231.123131.312.312.31223.123.12313213.fsdfjlsd.32123123.fsdalfjsf.fsadfjfs.afs.f.fsdfa

sunnyso

¸É¥R¸g¹L´ú¸Õ¥u­n¥h±¼ÂI¸¹ªº¦r¦êªø«×¤¤¤åboublebyte-963, ­^¤åsinglebyte-873
¤èªk¤@¤£·|¥X¿ù
=TRIM(MID(SUBSTITUTE($A1,".",REPT(" ",LEN(SUBSTITUTE($A$1,".","")))),LEN(SUBSTITUTE($A$1,".",""))*(COLUMN(A1)-1)+1, LEN(SUBSTITUTE($A$1,".",""))))

sunnyso

¦^´_ 6# hydraulic
¤èªk¤@, ¥u­nA1¥h±¼"." «áªºªø«×¤£¶W¹L301´N¤£·|¥X¿ù.©ÎªÌ¥Î¤U¦C¤½¦¡. ¥i¥HÁקK

=TRIM(MID(SUBSTITUTE($A1,".",REPT(" ",LEN(SUBSTITUTE($A$1,".","")))),LEN(SUBSTITUTE($A$1,".",""))*(COLUMN(A1)-1)+1, LEN(SUBSTITUTE($A$1,".",""))))

hydraulic

²Ä¤@ºØ¤èªk:TRIM(MID(SUBSTITUTE($A1,".",REPT(" ",301)),COLUMN(A1)*301-300,301)),¤£ª¾¹D¬Æ»ò¼Ëªº±¡ªp·|¥¢®Ä³á!
²Ä¤TºØ¤èªk:B1=MID($A1,1,3),¦n¹³¥u°w¹ï©T©w¦r¤¸¦³®Ä¦Ó¤w­Y¥|²Õ¼Æªº¦r¤¸¤£²Î¤@´N¥¢®Ä¤F

amaggie_h

Hi ~ §Ú¦³­Ó²³æªº¤è¦¡~ ¥i¥H¨Ï¥ÎMID¨ç¼Æ, ¥ý³]©w¦nB1=MID($A1,1,3) , C1=MID($A1,5,3), D1=MID($A1,9,3), E1=MID($A1,13,3), ·íA1¿é¤J©T©w¦r¤¸ªº¦r¦ê®É, B1~E1´N·|¦Û°Ê±a¥XÅo^^y

die78325

¦^´_ 2# sunnyso


    ¨ç¼Æ¯u¬O°ªÃø«×= ="
    ±K±K³Â³Âªº....¯u·Q¹³¤j¤j¤@¼Ë¼F®`!@@"  ÁöµM¬O¤Þ¥Î§O¤Hªº ¦ý ¯à²z¸Ñ¤]¬O°ª¤â!

die78325

¦^´_ 1# ss961471

½Ð¶}±ÒÀH«KEXCEL ÂI¿ïALT+F11  ¶}±Ò¥¨¶°½s¿èµ{¦¡   ¦A¥kÁä±z©Ò¶}±ÒªºEXCEL  ´¡¤J¼Ò²Õ §â¥H¤Uµ{¦¡½X½Æ»s¶i¥h
¦b¤u§@­¶¤º·s¼W¤@­Ó¹Ï®×((ÀH«K¬Ý­n¤è§Î¶ê§Î)) ¹ï¹Ï®×ÂI¥kÁä «ü©w¥¨¶°  ¿ï¾Ü "²³æªº°j°é­åªR"  ¦A§â¸ê®Æ¥á¤JA1 ¦AÂI¿ï¹Ï®×  ´N·|°õ¦æ¤F
¦¹¥¨¶°¦³°j°é³]­p  ©Ò¥H§A¥i¥H¶K¦hµ§¸ê®Æ A1 A2 A3 A4 A5  ¦ýA1¤@©w­n¦³¸ê®Æ  §_«h·|¥H¬°µL¸ê®Æ!  ¦p¦³°ÝÃD¦Aµo°Ý ·PÁ±z  §Ú¤]¬O·s¤â
¤£ª¾¬O§_¦³À°¤W¦£~~~

1. Option Explicit
   
2. Sub ²³æªº°j°é­åªR()
   
3. Dim g, i As Long
   
4. '°»´úA1¬O§_¦³¸ê®Æ¡A¦pµL¸ê®Æ«hÂ÷¶}
   
5. If Range("A1") = "" Then MsgBox "µL°»´ú¨ì¥ô¦ó¸ê®Æ¡A±ý­åªRªº¸ê®Æ½Ð©ñ¤JA¦C": End Sub
   
6. '°»´ú¦³´X¦æ­n­åªR  (Àɮ׳̫á¤@µ§ªº¦ì¸m)
   
7. g = [a65565].End(xlUp).Row
   
8. '¶}©l°j°é
   
9.         For i = 1 To g ' °j°é±q²Ä¤@¦Ü³Ì«á¤@µ§
   
10.             Range("A" & i).Select   '¿ï¾Ü­n­åªRªºÀx¦s®æ
    
11.             ' ¶}©l¸ê®Æ­åªR (¥H  " . "  ¨Ó°Ï¹j)
    
12.             Selection.TextToColumns Destination:=Range("A" & i), DataType:=xlDelimited, _
    
13.                 TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    
14.                 Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    
15.                 :=".", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
    
16.                 TrailingMinusNumbers:=True
    
17.         Next
    
18. '°j°éµ²§ô
    
19. 
    
20. MsgBox "­åªR§¹¦¨"
    
21. End Sub

½Æ»s¥N½X

sunnyso

¦^´_ 1# ss961471

111.222.333.444
=TRIM(MID(SUBSTITUTE($A1,".",REPT(" ",301)),COLUMN(A1)*301-300,301))
(­É¥ÎML089ªº¨ç¼Æ)