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

¦p¦ó³]©wREGEXP PATTERN¤~¯à¨ú¥X¥¿½T¸ê®Æ©O¡H

¦p¦ó³]©wREGEXP PATTERN¤~¯à¨ú¥X¥¿½T¸ê®Æ©O¡H

¦U¦ì¤j¤j¦n¡G

  ¤p§Ì¤Þ¥ÎREGEXP¨Ó¨ú¥XAÄ檺¦r¦ê
¨Ã³]©wPATTERN¨Ó±NA1¨ú¥X¤­­Ó¦r¦ê¡C
¦ý¥Ø«e·Q±N¦³³r¸¹¥]§t¦b¦r¦ê¤º¡A
¹ï7,852¤Î119,258,226¦Uµø¬°¤@­Ó
¦r¦ê¨Ã¶ñ¤JA¦Cªº«ü©wÄæ¦ì¤º¡C
¦ýPATTERN ³]©w¤@ª½µLªk逹¦¨¡C
½Ð¦U¦ì¤j¤j¯à¬Û§U¡A¦p¦ó­×¥¿
PATTERNªº³]©w­È©O¡H

AÄæ¸ê®Æ¦p¤U¡G
22  2 PCE   7,852  119,258,226
106  2 PCE   10,117,852  226
10  11 PCE   17,852  119,258,226
1080  118 PCE   1,117,852  119,258
109  1429 PCE   17,852  19,258
110  51511 PCE   17,852  119,258,226
11125  515 PCE   517,852  9,258,226


»yªk¦p¤U¡G
Sub aa()   
    Dim mSht As Worksheet
    Dim mRng As Range, mRng1 As Range
    Dim mRegexp As New RegExp
    Dim mPtn$, mStr$
    Dim matchCol, matchA
    Dim s%   
    Set mRegexp = CreateObject("vbscript.regexp")
    Set mSht = Worksheets("temp")
    s = 1   
    mPtn = "\d+|PCE|\d+,[0-9]{3}" '¦p¦ó­×¥¿mPtn
    With mSht
        Set mRng1 = .Range("a1", .Range("a" & .Rows.Count).End(xlUp))
        For Each mRng In mRng1
            mStr = mRng.Value
            With mRegexp
                .Global = True
                .IgnoreCase = False
                .Pattern = mPtn
                Set matchCol = .Execute(mStr)
            End With
            For Each matchA In matchCol
              mRng.Offset(, s) = matchA.Value
              s = s + 1
            Next            
            s = 1
        Next
    End With   
End Sub

ÁÂÁ¦U¦ì¤j¤j¡I

regTest.rar (8.27 KB)

ª½«ö¥Î¸ê®Æ­åªR·|¤£·|²³æ¤@ÂI?
¥ý¿ï¤WAÄæ
  1.     Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
  2.         TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
  3.         Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
  4.         :=" ", FieldInfo:=Array(Array(1, 1), Array(2, 9), Array(3, 1), Array(4, 1), Array(5, _
  5.         9), Array(6, 9), Array(7, 1), Array(8, 9), Array(9, 1)), TrailingMinusNumbers:=True
½Æ»s¥N½X
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¦^´_ 2# kimbal


    ª©¥D¤j¤j±z¦n¡G

   ÁÂÁª©¥D¤j¤j´£¨Ñ¦¹¤è¦¡¬O³Ì²z·Qªº¸ÑÃD¤F¡C
¹ê¤£¬Ûº¡¡A¤p§Ìªñ¤é°g¤WREGEXP¦¹
¤å¦r³B²zµ{¦¡¡A¦¹¤u¨ãµ{¦¡¦b³B²z¤å¦r
¦r¦ê¤è­±¹ê¦b¾Ö¦³·¥¤j¥\¯à¡C¦]¦¹¡A
¤p§Ì¦b¤u§@¤W¦p¹J¤W¤å¦r³B²z®É¡A
¶}©l§ï¥ÎREGEXP¨Ó¸ÑÃD¡C
¤p§Ì¥Ø«e¹ï¥»ÃDªº¸Ñ´£¤è¦¡
¬O¥ý±N¡u¡A¡v¨ú¥N§Y¥i¨ú¥X
AÄæ¦ìªº©Ò¦³¸ê®Æ¡C¦ý¤p§Ì¬O
­É¦¹°ÝÃD¨Ó¿i½m¦Û¤w¡C§Æ±æ¯à
¦bPATTERN³]©w¤W¦³©Ò¬ð¯}©O¡H

·P®¦¤j¤j¡I

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2013-8-20 09:19 ½s¿è

¦^´_ 3# dechiuan999
mPtn = "PCE|\d+(,\d{3})*"

TOP

¦^´_ 4# stillfish00


    ¤j¤jªº¯«¨Ó¤§µ§¦³¦pµeÀsÂI·ú¤@¼Ë¡C
·Q¤£¨ìpatternªº³]©w·|¦³¦p¦¹ªºÅܤơA
³o¬O¥O¤p§ÌµÛ°gªº¦a¤è¤F¡C
¤£ª¾¯à§_½Ð¤j¤j¯à¦A¶}¥Ü
¨ä¤¤ªº¦¹(  )À³¥Î¤è¦¡¬°¦ó¡A
¤p§Ì¥Ø«eÁÙµLªk²z¸Ñ©O¡H

·P®¦¤j¤j¡I

TOP

¦^´_ 5# dechiuan999
,\d{3}        ¤@³r¸¹¤T¼Æ¦r
(,\d{3})*   (¤@³r¸¹¤T¼Æ¦r)¥X²{¹s¦¸©Î¦h¦¸

¦]¬°*¬O¥Î¨Ó¤Ç°t«e¤@¦r¤¸ ¹s¦¸©Î¦h¦¸
³o¸Ìªº ( )* ¨Ï¬A¸¹¤º³W«h¤Ç°t ¹s¦¸©Î¦h¦¸

·íµM¤p¬A¸¹ÁÙ¦³¨ä¥L¯S®í¥\¯à¡C¡C¡C³o¸Ì¨S¥Î¨ì´N¤£¦h»¡¤F

TOP

¦^´_ 6# stillfish00


    ÁÂÁ¤j¤jªº¸Ô¸Ñ¡C
¤p§Ì·|¥ý¦¬¤U¡A¦AºCºC
¥hÅé·|¥¦ªº§t·N¡C

·P®¦¤j¤j¡I

TOP

¥Î ¡§¥¿³Wªí¥Ü¦¡¡¨¥i¥H°µ«Ü¦h¨Æ, ¨Ò¦p
´M§ä¦r¦ê¤¤ªº¤¤¤å¦r

Function CountDblChr(ByVal sCh As String) As Integer

    Set re = CreateObject("vbscript.regexp")

    're.Pattern = "[\u4e00-\u9fa5]" '§PÂ_¤¤¤å¦r(¥]¬A¯S®í¦r¡A¦ý¤£¥]¬A²Å¸¹¡C©M¼Æ¦r¦p¢°¡^

    re.Pattern = "[^\x00-\xff]" '§PÂ_©Ò¦³ªº¥þ§Î¦r

    re.Global = True

    re.IgnoreCase = True

    Set matches = re.Execute(sCh)

    CountDblChr = matches.Count

    Set re = Nothing

End Function
ss

TOP

¦^´_ 8# sunnyso


    ÁÂÁ¤j¤jªº½d¨Ò¡A
¤p§Ì¤w±N¥¦¯Ç¤J¬ÃÂáC

¤p§Ì¤]¬Oªñ¤é¤~µo²{
REGEXP EXPRESS ½T¹ê
¦b¦r¦ê³B²z¤W¦³¥¦ªº±j¶µ¡A
­È±oµo¤ß«ä¦n¦n¾Ç²ß¡C
§Úª¾¹D­n¾Ç¦n¥¦ªº¤£¤Gªkªù¡A
´N¬O­n¤£Â_ªº½m²ß¡A¤]¬è±æ
¯à§_»â·|¥X¥¦ªººëÀH¡C
¤£ª¾¤j¤j¯à§_±ÀÂ˦nªº
REGEXP敎¾Ç½d¨Òºô¯¸¶Ü¡H

·P®¦¤j¤j¡I

TOP

¦^´_ 9# dechiuan999
¦³­Ó¦nªººô¯¸,¦ý«Ü¤[¨S¦³¤W¤F.§ä¨ì¦Apo¤W
ss

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤H¤£©È¿ù¡A´N©È¤£§ï¹L¡A§ï¹L¨Ã¤£Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD