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

[µo°Ý] ½Ð°Ý¦p¦ó¨ú¥XREGEXP«ü©wªºªÅ®æ¤§«á¦r¦ê©O¡H

[µo°Ý] ½Ð°Ý¦p¦ó¨ú¥XREGEXP«ü©wªºªÅ®æ¤§«á¦r¦ê©O¡H

¦U¦ì¤j¤j¦n¡G
¤p§Ì·Q§Q¥Îregexp¨Ó¨ú¥X¤U¦C¦r¦ê
¡A¦Ó«ü©w¦r¦ê°ß¤@ªº¦@¦PÂI¬O
²Ä¤T­ÓªÅ¥Õ¦r¦ê¬°°_ÂI¨Ã
¨ú¥XªÅ¥Õ¦r¦ê«áªº©Ò¦³¦r¦ê¡C
¤p§Ì¤Þ¥Î¤U¦Cpattern «oµLªk
¤Ç¹ï¦¨¥\¡C½Ð°ÝÀ³¦p¦ó­×¥¿pattern
³]©w©O¡H

mPtn = "\s{3,3}.*"
A Äæ¦r¦ê¤º®e¦p¤U¡G
A1¡÷aa babcdefg cc dd ee ff gg hh ii
A2¡÷aa bb cdefc dd ee ff gg hh
A3¡÷aijklma bb cc dd ee ff
A4¡÷aba bb cc dd ee ff gg

BÄ欰¨ú¥X¤º®e¦p¤U¡G
B1¡÷dd ee ff gg hh ii
B2¡÷dd ee ff gg hh
B3¡÷dd ee ff
B4¡÷dd ee ff gg

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

¸Õ¸Õ¬Ý

Pattern = "\s+\S+\s+\S+\s+(.*)"
B1=Execute(A1).Item(0).SubMatches(0)

TOP

¥»©«³Ì«á¥Ñ dechiuan999 ©ó 2015-3-6 10:59 ½s¿è

¦^´_ 2# diolin

¤j¤j±z¦n¡G
ÁÂÁ¤j¤jªº¬Û§U¡C
¦]µLªk§Q¥ÎEXCEL¸ê®Æ­åªR
¨Ó¤À³Î¦r¦ê¡F¦]¦Ó§ï¥ÎREGEXP
¨Ó¤À³Î¦r¦ê¡C
¨Ì¤j¤jªº¤è¦¡¡A¤p§ÌµLªk±NAÄæ
¤w¨ú¥Xªº¦r¦ê¦A§Q¥ÎREGEXP REPLACE
¨Ó²M°£¡C
»yªk¦p¤U¡G
  1. Sub mRegRepStr()   
  2.         
  3.     Dim mPtn As String
  4.     Dim mStr As String
  5.     Dim mRepStr As String
  6.     Dim mSht As Worksheet
  7.     Dim mRng As Range, mRng1 As Range   
  8.    
  9.     Application.ScreenUpdating = False        
  10.     mPtn = "\s+\S+\s+\S+\s+(.*)"
  11.     mRepStr = ""
  12.    
  13.     Set mSht = Worksheets("new")
  14.     With mSht
  15.         Set mRng1 = .Range("a1", .Range("a" & .Rows.Count).End(xlUp))
  16.         For Each mRng In mRng1
  17.             mStr = mRng.Value
  18.             mRng.Value = mRegexpReplace(mPtn, mStr, mRepStr)
  19.         Next
  20.     End With
  21.    
  22.     Set mSht = Nothing
  23.     Set mRng = Nothing
  24.     Set mRng1 = Nothing
  25.    
  26. End Sub

  27. Function mRegexpReplace(mPtn As String, mStr As String, mRepStr As String)
  28.    
  29.     'Regexp Replace
  30.     Dim mRegReplace As New VBScript_RegExp_55.RegExp
  31.    
  32.     Set mRegReplace = New VBScript_RegExp_55.RegExp
  33.     With mRegReplace
  34.         .Global = True
  35.         .IgnoreCase = True
  36.         .Pattern = mPtn
  37.     mRegexpReplace = .Replace(mStr, mRepStr)
  38.     End With
  39.    
  40.     Set mRegReplace = Nothing
  41. End Function
½Æ»s¥N½X

TOP

Âà­ÓÅs
tmp=.Execute(A1).Item(0).SubMatches(0)
mRegexpReplace=Replace(mStr, tmp, mRepStr)

TOP

¦^´_ 4# diolin


    ÁÂÁ¤j¤j¡I
¤p§Ì¤Þ¥Î¤j¤j´£¨Ñªº¤è¦¡
¦breplcaceÁÙ¬O¥¼¯à¦¨¥\¡C
¤p§Ì¥Ø«e¹ïregexp¤´¦b­è°_¨B¡C
¥i§_½Ð¤j¤j´£¨Ñ¤p§Ì¤@­Ó§¹¾ã½d¨Ò
¨Ñ¤p§Ì¾Ç²ß§_¡H

·P®¦¤j¤j¡I

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2015-3-6 18:46 ½s¿è

¦^´_ 1# dechiuan999
¨âºØ³£¥i¥H
  1. Sub TestAA()
  2.     Dim oReg As Object
  3.     s = "aa babcdefg cc dd ee ff gg hh ii"
  4. '    s = "aa bb cdefc dd ee ff gg hh"
  5. '    s = "aijklma bb cc dd ee ff"
  6. '    s = "aba bb cc dd ee ff gg"
  7.     Set oReg = CreateObject("vbscript.regexp")
  8.    
  9.     With oReg
  10.         .Pattern = "^\S+\s+\S+\s+\S+\s+(.*)"
  11.         Debug.Print .Replace(s, "$1")   '¨ú¥Nmatch patternªº³¡¤À¦¨¬° pattern ¤¤²Ä¤@­Ó¬A¸¹¤º¦r¦ê
  12.         
  13.         .Pattern = "^\S+\s+\S+\s+\S+\s+"      '¦P "^(\S+\s+){3}"
  14.         Debug.Print .Replace(s, "")     '§ì¥X¶}ÀY3­Óword , ±Nmatch patternªº³¡¤À¥Î""¨ú¥N±¼
  15.     End With
  16. End Sub
½Æ»s¥N½X
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 6# stillfish00


    ¤j¤j±z¦n¡G
¤p§Ì¤w´ú¸Õ¦¨¥\¤F¡C
¤p§Ì²`ª¾¹ï©óregexp³B²z¤å¦r¡A
¦³«Ü¤jªºPOWER¯à¤O¡C
¥i±¤¤p§Ì«o±`±`¨Ï¤£¤W¤O¡C

«Ü·P®¦¤G¦ì¤j¤j¡I

TOP

        ÀR«ä¦Û¦b : ¤@¥y·Å·xªº¸Ü¡A´N¹³©¹§O¤H¨­¤WÅx­»¤ô¡A¦Û¤v·|ªg¨ì¨â¤Tºw¡C
ªð¦^¦Cªí ¤W¤@¥DÃD