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

[µo°Ý] ½Ð°Ý¦p¦ó±q¤@¦æ¤å¦rªø¦r¦ê¤¤ºI¨ú»Ý­nªºµu¦r¦ê

[µo°Ý] ½Ð°Ý¦p¦ó±q¤@¦æ¤å¦rªø¦r¦ê¤¤ºI¨ú»Ý­nªºµu¦r¦ê

AOÄæ¦ì¤ºªº¦r¦ê¦³²Î¤@±Æ¦C¤è¦¡¡A¦b¨C¤@­Ó#¦r¸¹«á­±¦b±a¥X"¾ºÙ"»P"©m¦W"¡C¨C¤@­Ó¦r¦ê¥i¯à¦P®É¦³¤G­Ó¥H¤Wªº#¦r¸¹¡A¦p¦ó¥ÎVBA¤è¦¡¿z¿ï¥X§Ú¶È»Ý­nªº"©m¦W"¡A¨Ã±N"©m¦W"­Ó§O§ì¨ìBJ¡BBK¡BBL¡KÄæ¦ì

ÅU«È½Ç¼ú²Î­p-1.zip (6.44 KB)

¥»©«³Ì«á¥Ñ luhpro ©ó 2011-6-25 23:27 ½s¿è

¨Ì¾Ú§Aªº»Ý¨D§ì¨ú«ü©w¦r¦êªºÄpµ²¦b©ó :
1. §ä¨ì "#" »P "$" ªº¦ì¸m,
2. ½T©w¾ºÙ³£¬O¥u¦³¨â­Ó¦r, §_«h±N«ÜÃø½T»{¨ì©³¦W¦r¬O±q­þ¸Ì¶}©l§ìªº
­n§ä¨ì¬Y­Ó¦r¦êªº¦ì¸m Excel VBA ¦³­Ó«ü¥O instr ¥i¥H¹F¦¨¦¹¥Øªº.

­È±o¤@ÃDªº¬O,
¤£½× instr ÁÙ¬O mid(left¡Bright¡Blen ¬Ò¦P) ¹ï©ó¤¤¤å¦r³£¬Oµø¬° 1 ­Ó¦rªºªø«×,
³o¦b§ì¨ú³øªí§Î¦¡ªº¤å¦r®É¬O«D±`¤£¾A¦X¥Îªº«ü¥O.
¨Ò¦p :

2011/06/25 ±i¤T       16:33:18
2011/06/25 §õ¥|®Q     16:55:21

­Y¥Î mid(sstr,21,8) §ì²Ä¤@¦æ¥i¥H¥¿±`§ì¨ì®É¶¡ 16:33:18,
¦ý²Ä¤G¦æ«o·|§ì¦¨ 6:55:21 ,
©Ò¥H¤ñ¸û¦nªº°Ï¹j¤è¦¡·|¬O¦b­n§ìªº¦r¦ê¥k¤è©ñ¤W¥Î¯S©w²Å¸¹,
¥Î instr §ì¨ú¸Ó²Å¸¹¦A©¹«e±À¤@©wªø«×.

µM¦Ó¦b c »y¨¥¤¤¤@­Ó¤¤¤å¦r«h¬Oµø¬° 2 ­Ó¦rªºªø«×,
¦b¦¹Ãþ»Ý¨Dªº¨Ï¥Î¤W¯uªº¬O¤ñ Excel VBA ¤è«K¦h¤F.

©³¤U¬O§Ú·Q¨ìªºµ{¦¡ :
  1. Sub nn()
  2.   Dim sStr$, sColumn$
  3.   Dim iI%, iBegin%, iMark%, iEnd%, iRows%, iRow%
  4.   Dim iCol%, iCol1%, iNext%
  5.   
  6.   sColumn = "BJ"
  7.   iCol1 = 0
  8.   For iI = Len(sColumn) - 1 To 0 Step -1
  9.     iMark = Asc(Mid(sColumn, iI + 1, 1)) - 64
  10.     iCol1 = iCol1 + iMark * 26 ^ -(iI = 0)
  11.   Next iI
  12.   iRows = Cells(Rows.Count, "AO").End(xlUp).Row
  13.   For iRow = 2 To iRows
  14.     sStr = CStr(Cells(iRow, "AO").Value)
  15.     iBegin = InStr(1, sStr, "#") + 1
  16.     iNext = InStr(iBegin + 1, sStr, "#") + 1
  17.     If iNext < iBegin Then iNext = iBegin
  18.     iCol = iCol1
  19.     Do Until iBegin > iNext
  20.       iEnd = InStr(iBegin, sStr, "$") - 1
  21.       Do Until iBegin > iEnd
  22.         iMark = InStr(iBegin, sStr, "#") - 1
  23.         If iMark > iBegin + 6 Then iMark = iEnd
  24.         If iMark < iBegin Then iMark = iEnd
  25.         Cells(iRow, iCol).Value = Trim(Mid(sStr, iBegin + 2, iMark - iBegin - 1))
  26.         iBegin = iMark + 2
  27.         iCol = iCol + 1
  28.       Loop
  29.       iNext = InStr(iBegin + 1, sStr, "#") + 1
  30.       If iNext > iBegin Then iBegin = iNext
  31.     Loop
  32.   Next iRow
  33. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ diabo ©ó 2011-6-26 01:12 ½s¿è
  1. Sub ¸ê®Æ­åªR()

  2.    '¨ú±o¸ê®Æ³Ì«á¤@¦C
  3.     last_row = Sheet1.[AO65536].End(xlUp).Row   
  4.    '¨ú±o¶ñ¤J©m¦W¤§Äæ¼Æ
  5.     col_bgn = Sheet1.Range("BJ1").Column
  6.    
  7.     For r = 2 To last_row         
  8.        '±ý¸ÑªR¤§­ì©l¸ê®Æ¤º®e
  9.         raw_data = Sheet1.Range("AO" & r).Value               
  10.        '¥H#¤À³Î¦r¦ê¬°°}¦C(Array)
  11.         arr_Name = Split(raw_data, "#")
  12.         For n = 1 To UBound(arr_Name)
  13.            '²Än­Ó©m¦W¦^¶ñ¸ê®ÆÄæ¼Æ
  14.             c = col_bgn + n
  15.            '¨ú¥X¾ºÙ+©m¦W+....
  16.             raw_name = Trim(arr_Name(n))            
  17.            '¨úªº$¦ì¸m
  18.             pos = InStr(1, raw_name, "$")
  19.            '¨ú¥X©m¦W--°²³]¾ºÙ¬Ò¬°¨â­Ó¦r(½Ð¦Û¦æ±NAD2ªº¡u±µ±a«Ý¡v§ï¬°¡u±µ«Ý¡v)
  20.             Sheet1.Cells(r, c).Value = Mid(raw_name, 3, IIf(pos = 0, 255, pos - 3))
  21.         Next
  22.     Next

  23. End Sub
½Æ»s¥N½X
diabo

TOP

·PÁÂluhpro»PdiaboÀ°¦£

TOP

        ÀR«ä¦Û¦b : ¥¬¬I¦p¼½ºØ¡A¥HÅw³ß¤ß´þ¼íºØ¤l¡A¤~·|µoªÞ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD