¤p«L«È

¦^´_  ¤p«L«È
^ = ¤Ç°t¦r¦ê¶}ÀY
\d{1,2} = ¬O«ü«e­±ªº \d¥X²{¤@¦¸¨ì¨â¦¸
        ¥t¥~±`¥Î²¤Æ¼gªk
        \d? = \d ...
stillfish00 µoªí©ó 2016-9-2 11:17

    ÁÂÁ§A¡A§Ú¤w°µ¹L´ú¸Õ¡A¦n¹³¦¨¥\¤F¡C
§Ú¤]¨Ì¼Ë¸Õ¼g¤F¤@­ÓÀˬd¦r¦ê¤¤¦³¨S¦³¦~¥÷ªºfunction
True :
1) ¦p¦³¡u¦~¡v¦r®É¡A«e­±¦³4­Ó¼Æ¦r
2) ¦p¨S¦³¡u¦~¡v¦r¡A«e­±¦³4­Ó¼Æ¦r

1. Function IsYear (ByRef Target As String) As Boolean
   
2.     Dim x, oRegexp As Object: Set oRegexp = CreateObject("vbscript.regexp")
   
3.     With oRegexp
   
4.         .Pattern ="[0-9]{4}[¦~]?(?![0-9])"
   
5.         IsYear = .test(Target)
   
6.     End With
   
7. End Function

½Æ»s¥N½X

°µ¤F´ú¸Õ¡A¦n¹³®ÄªG¤£¤Ó¦n¡C
1234
12345
1234¦~
12345¦~
abc1234
abc1234¦~
abc12345
abc12345¦~

³o8²Õ¤]¬OTRUE¡A¦ý§Ú¥u§Æ±æ²Ä1¡B3¡B5¡B6²Õ¬OTRUE¡C¥Î¤F{4}¤]µLªk­­¨î¼Æ¦r¦r¦ê¥u¯à¬O4­Ó¡C
§Ú¤w¸g¥[¤Fnegative lookahead (?![0-9]) ¡A­­¨î¼Æ¦r¦r¦ê«á¤£¥i¦A¸ò¼Æ¦r¡A¦ýVBA¦n¹³¤£¤ä´©negative lookbehind¡AµLªk­­¨î¼Æ¦r¦r¦êªº¶}©l¤£¬O¼Æ¦r.....

­ã´£³¡ªL

¥¿«h¯uªº¤£®e©ö¡I¡I¡I

¸Õ¸Õ¤½¦¡¦p¦ó¡G
=SUM(--TEXT(1&RIGHT(LEFT(A1,FIND("¦~",A1&"¦~")-1),{4,5}),"[<11901]-9;[>12099]-9;1;!0"))>0

­­©w 1901~2099 ¦~¤§¶¡¬°¦³®Ä¦~¡A
02015¦~¡@Áö¬°¢±¢¯¢°¢´¦~¡A¦ý¦]§t¢´­Ó¼Æ¦r¡A§P¬°µL®Ä¡A
¤£¹L¡Aabcd2015ppuy ³o­Ó¤£§t"¦~"¦r¡A¢±¢¯¢°¢´´NµLªk¥Î³o¤½¦¡§PÂ_¡A
abcd2015¦~ppuy¡@´N¢Ý¢Ù¡I

zyzzyva

¦^´_ 11# ¤p«L«È
¤£ª¾¹D±zªº­ì©l¸ê®Æ¬O¤°»ò¼Ë¤l¡A¦pªG³o¨Ç¦r¦ê¤£·|¥X²{¦b¥y­º¸ò¥y§À¡A.test¨ºÃä´N¤£¥Î§ï

1. Function IsYear (ByRef Target As String) As Boolean
   
2.     Dim x, oRegexp As Object: Set oRegexp = CreateObject("vbscript.regexp")
   
3.     With oRegexp
   
4.         .Pattern = "\D(\d{4}¦~?)\D"
   
5.         IsYear = .test("-" & Target & "-")
   
6.     End With
   
7. End Function

½Æ»s¥N½X

stillfish00

¦^´_ 11# ¤p«L«È
³o­Ó­n¸ÑÄÀ¯uªº¤£¦n¸ÑÄÀ...
§Ú¬O·|³o¼Ë§ì°Õ¡A§A°Ñ¦Ò¬Ý¬Ý:

1. Sub Test()
   
2.     Dim oReg: Set oReg = CreateObject("vbscript.regexp")
   
3.     Dim x, oMatch
   
4.     Const EX1 = "1111 22222 3333¦~ 44444¦~ abc5555 abc6666¦~ abc77777 abc88888¦~"
   
5.     With oReg
   
6.         .Pattern = "(?:\b|\D)(\d{4})(?=¦~|\b)"
   
7.         .Global = True
   
8.         Set oMatch = .Execute(EX1)
   
9.         For Each x In oMatch
   
10.             Debug.Print "x=" & x, "x.submatches(0)=" & x.submatches(0)
    
11.         Next
    
12.     End With
    
13. End Sub

½Æ»s¥N½X

¿é¥X·|¬O³o¼Ë¡A§A¦n¦nÅé·|¤@¤U

1. x=1111     x.submatches(0)=1111
   
2. x= 3333     x.submatches(0)=3333
   
3. x=c5555    x.submatches(0)=5555
   
4. x=c6666    x.submatches(0)=6666

½Æ»s¥N½X