正则表达式提取单号 VBA

中台税务管理 （样本）

AT_10000003_20180305_0007_附征税中转 CUX_TAX CNY User

VAT_10000003_20180204_0004_中台税务_税额扣款 CUX_TAX CNY User

ST_10000003_20180206_0006_中台税务_税额扣款 CUX_TAX CNY User

EIT_10000003_20180712_0001_所得税计提 CUX_TAX CNY User

IIT_10000003_201807_0032_中台税务_税额扣款 CUX_TAX CNY User

正则表达式：(AT|VAT|ST|EIT|IIT)[_0-9]+

正则表达式（优化）：(AT|VAT|ST|EIT|IIT)[_][0-9]+[_][0-9]+[_][0-9]+

 

中台支付申请（样本）

FK18010300000051：SQ2000201712260033 CUX_HAP CNY User

SQ3001201806120001-2018-06-12 00:00:00-中台支付申请-CNY CUX_HAP CNY User

正则表达式：FK\d{14}|SQ\d{16}

 

中台经纪人提成（样本）

提成支付凭证-2018-06-28-FK18062800001347 CUX_BONUS CNY User

TC18062700000002-提成成本凭证-2018-06-28 CUX_BONUS CNY User

FK18053100001978 CUX_BONUS CNY User

正则表达式：(FK|TC)\d{14}

 

中台工资（样本）

GZ18091010103756-工资成本凭证-2018-09-10 CUX_SALARY CNY User

FK18091000000987-工资付款凭证-2018-09-10 CUX_SALARY CNY User

工资成本凭证-GZ1805301010355489035 CUX_SALARY CNY User

正则表达式：(FK|TC|SQ|GZ)\d+

 

 

记账（样本）

2000201803060124-1 记账 CNY User

冲销 "20002018011632820006-1"14-03-2018 10:29:09

2000201803130008-1 记账 CNY User

200020180504SG9739-1 记账 CNY User

REV200020180412SG0004-1 记账 CNY User

正则表达式：\w+[-]\d{1}

 

费用报销（样本）

BX2000180200105-2018-03-27-费用报销-CNY 费用报销 CNY Corporate

JK2000180300013-2018-03-29-现金事务-CNY 现金事务 CNY Corporate

FK18040200000097-2018-04-02-现金事务-CNY 现金事务 CNY Corporate

正则表达式：(FK|TC|SQ|GZ|JK|BX)\d+

 

总正则表达式：(FK|TC|SQ|GZ)\d+|(AT|VAT|ST|EIT|IIT)[_][0-9]+[_][0-9]+[_][0-9]+

(FK|TC|SQ|GZ)\d+|(AT|VAT|ST|EIT|IIT)[_][0-9]+[_][0-9]+[_][0-9]+|\w+[-]\d{1}

(FK|TC|SQ|GZ|JK|BX)\d+|(AT|VAT|ST|EIT|IIT)[_][0-9]+[_][0-9]+[_][0-9]+

 

 

Excel VBA案例运用（提取相关单号）

Sub 凭证行字段提取()

 

Dim qhn01 As Long

Dim qhi01 As Long

Dim qhsh2, qhsh3, g, w, t As Long '进度条相关变量

 

t = Timer

 

qhn01 = Sheets("凭证行").Range("b1000000").End(xlUp).Row

 

Set regx = CreateObject("vbscript.regexp")

With regx

.Global = True

.Pattern = "(FK|TC|SQ|GZ|JK|BX)\d+|(AT|VAT|ST|EIT|IIT)[_][0-9]+[_][0-9]+[_][0-9]+"

For qhi = 3 To qhn01

' qhc = 2

Set qhk = .Execute(Sheets("凭证行").Cells(qhi, 2))

For Each qhm In qhk

' qhc = qhc + 1

' Sheets("凭证行").Cells(qhi, qhc) = qhm

 

If Left(qhm, 2) = "FK" Then

Sheets("凭证行").Cells(qhi, 4) = qhm

Else

Sheets("凭证行").Cells(qhi, 3) = qhm

End If

Next

 

qhsh2 = qhn01 - 2 '总条数

qhsh3 = qhi - 2 '变动条数

w = UserForm1.Label3.Width '获取文本框长度(QH)

UserForm1.Show 0 '显示窗口(QH)

g = g + w / qhsh2 '将文本框长度平均到条数(QH)

UserForm1.Label2.Width = g '实时更新文本框的长度(QH)

UserForm1.Label1 = "已完成" & Format(qhsh3 / qhsh2, "0.00%") '文本框中央显示百分比(QH)

UserForm1.Caption = "正在运行，已耗时" & Format(Timer - t, "0.00") & "秒，请稍后！！！阙辉提醒~_~" '标题栏显示耗(QH)

DoEvents

 

Next

End With

Unload UserForm1

 

End Sub