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

[µo°Ý] ¦³Ãö¸ê®Æ­åªR

[µo°Ý] ¦³Ãö¸ê®Æ­åªR

·Q­n±ÐColumn A(¾ãÄæ¦ì)ªº¸ê®Æ¤À³Î¨ìB/C/D Äæ¦ì
AÄæ¦ì¸ê®Æ¤º®e¬°¤é´Á®æ¦¡ ¨Ï¥ÎVB¼gªº¸ÜÀ³¸Ó«ç»ò¼g?¤À³Î¨Ì¾Ú¬°"/" ¦ý¬O¨Sªk¶¶§Q¤Á³Î¶}
¤£ª¾¹D§Ú¼gªº¬O§_¥¿½T·Q½Ð±ÐªO¤W±M®a

Sub AA()
Range("A:A").TextToColumns Destination:=Range("B"), OtherChar _
        :="/"

End Sub

¥»©«³Ì«á¥Ñ hugh0620 ©ó 2012-3-27 11:47 ½s¿è

¦^´_ 1# tommy.lin


    AÄæ¦ì¬O¤é´Á®æ¦¡~ ¥Nªíªº´N¬O¦~¤ë¤é
    §A­n©î¸ÑªºBCDÄæ¦ì¤]´N¬O¦~¤ë¤é~
    ¥Î¤@¯ë³Ì²³æªº¤è¦¡~¨Ó©î¸Ñ´N¥i¥H¤F~
    ­Y§AA¸ê®Æ¬O±q¸ê®Æ®w­Ë¥X¨Óªº~ ¥i¯à·|¬O¤å¦r®æ¦¡~¦³¥i¯à­n­×§ï¤@¤U~
    ¦pªG¥i¥H~ ½Ð´£¨Ñ¸ê®Æªº½d¨Ò~
  1. Sub EX_1()

  2. For Each a In Range("A:A")
  3.     If a <> "" Then
  4.        a.Offset(0, 1) = Year(a)
  5.        a.Offset(0, 2) = Month(a)
  6.        a.Offset(0, 3) = Day(a)
  7.     End If
  8. Next

  9. End Sub
½Æ»s¥N½X
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

¦^´_ 2# hugh0620

Àɮפw¤W¶Ç

TextToColumn.zip (6.88 KB)

TOP

¥»©«³Ì«á¥Ñ hugh0620 ©ó 2012-3-27 12:17 ½s¿è

¦^´_ 3# tommy.lin

BÄæ¦ì~ ½Ð§ï¦¨¼Æ­È®æ¦¡~
ÃD¨Ñ¨âºØ¤è¦¡µ¹§A°Ñ¦Ò~
  1. Sub EX_1()
  2. For Each a In Range("A2:A65536")
  3.     If a <> "" Then
  4.        a.Offset(0, 1) = Year(a)
  5.        a.Offset(0, 2) = Month(a)
  6.        a.Offset(0, 3) = Day(a)
  7.     End If
  8. Next
  9. End Sub
½Æ»s¥N½X
  1. Sub xx()  '±N¦r¦ê¤À³Î¦¨¤l¦r¦ê
  2. For Each a In Range("A:A")
  3. h = Split(a, "/", 3, vbDatabaseCompare)
  4.     i = 1
  5.     For Each b In h
  6.     a.Offset(columnoffset:=i) = b
  7.     i = i + 1
  8.     Next
  9. Next
  10. End Sub
½Æ»s¥N½X
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

¥i¥H¤F...Thanks lots..¤£¹L¬Ý¤£À´»yªk

TOP

¥»©«³Ì«á¥Ñ mark15jill ©ó 2012-3-27 13:36 ½s¿è

¦^´_ 5# tommy.lin
ª½±µ§âµ{¦¡¥á¶iVBA¤º..



sub xxg
        Columns("a:a").Select
    Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2)), _
        TrailingMinusNumbers:=True
end sub

§@¥Î¡G¥Î / ¬°§PÂ_²Å¸¹ ¥[¥H¤Á³Î

TOP

§Ú¦³¸ÕµÛ¥Î¥¨¶°¿ý»s¦¨¤W­±³o¼Ë¤l¦ý¹ê»Ú°õ¦æ®É·|¥X²{error....

TOP

¥»©«³Ì«á¥Ñ mark15jill ©ó 2012-3-27 15:49 ½s¿è

¦^´_ 7# tommy.lin


    ³Â·Ð¶K¹Ï ¬Ý­þÃ䦳¿ù..



Sub xyw()
Columns("g:g").Select
    Selection.TextToColumns Destination:=Range("m1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(3, 2), Array(2, 2), Array(1, 2)), _
        TrailingMinusNumbers:=True
        
    sy = Columns("o:o").Value
    Columns("t:t") = sy
    sm = Columns("n:n").Value
    Columns("u:u") = sm
    sd = Columns("m:m").Value
    Columns("v:v") = sd
    Columns("m:o").Clear
End Sub

TOP

°ÝÃD¦n¹³¸Ñ¶}¤F...­ì¥»§Ú¦³¥|­Ó¤é´ÁÄæ¦ì»Ý­n¸ê®Æ­åªR­ì¥»¿ý»sªº¤è¦¡¬O¤@¦¸¥þ³¡°µ§¹
¦ý¬O¦n¹³·|¦³¿ù¶Ãªº²{¶H(°õ¦æ¨ì¤@¥b¥d¦í©Î¬O«á­±ªº´¡¤JÄæ¦ì¿ù¦ì)§Úªº¨BÆJ¦p¤U
¦b¨C­Ó¤é´ÁÄæ¦ì«á­±´¡¤J¤­­ÓªÅ¥ÕÄæ¦ì¸ê®Æ­åªR«á§R°£³Ì«á¨â­Ó¹w¯dÄæ¦ì.¥H¤W¨BÆJ­«ÂÐ¥|¦¸
«á¨Ó§Ú¤À¥|¦¸¿ý»s¦b¦ê±µ°_¨Ó¨S¦³¿ù»~µo¥Í.anyway ·PÁªO¤W°ª¤â¨ó§U

TOP

¦^´_ 4# hugh0620

¡@ ¤j¤j¤U­±¨âªº¥i¥H¸ÑÄÀ¤@¤U¶Ü
    vbDatabaseCompare  &¡@columnoffset
    ¤£¤Ó¾å±o¥Îªk
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¯Ê¤fªºªM¤l¡A¦pªG´«¤@­Ó¨¤«×¬Ý¥¦¡A¥¦¤´µM¬O¶êªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD