Excel 不規(guī)則中英文分離

hdzgx 2019-10-23

展開全文

Sub lqxs()
Dim Arr, i&, s1$, s2$
Arr = Sheet1.UsedRange
With CreateObject("VBSCRIPT.REGEXP")
.Global = True
For i = 1 To UBound(Arr)
If Arr(i, 1) <> "" Then
.Pattern = "\D" '數(shù)字模式
s1 = .Replace(Arr(i, 1), "")
.Pattern = "[^\u4e00-\u9fa5]" '漢字模式
s2 = .Replace(Arr(i, 1), "")
If s1 <> "" Then
Cells(i, 3) = s1 & " " & s2
Cells(i, 4) = Mid(Arr(i, 1), Len(s1 & " " & s2) + 2)
Else
Cells(i, 3) = s2
Cells(i, 4) = Mid(Arr(i, 1), Len(s2) + 2)
End If
End If
Next
End With
End Sub

************************

資產類 Assets	資產類	Assets
3流動資產 Current assets	3 流動資產	urrent assets
4貨幣資金 Cash and cash equivalents	4 貨幣資金	ash and cash equivalents
見金Cash	見金	ash
61002銀行存款 Cash in bank	61002 銀行存款	ash in bank
71009其他貨幣資金 Other cash and cash equivalents	71009 其他貨幣資金	ther cash and cash equivalents
8100外雄存款 Other city Cash in bank	8100 外雄存款	ther city Cash in bank
910020行本票 Cashier'scheque	910020 行本票	ashier'scheque
101000票 Bank draft	101000 票	ank draft
110904信用卡 Credit	110904 信用卡	redit
12100信用證保證金 U/C Guarantee depo	12100 信用證保證金	/C Guarantee depo
1310906年出投資款 Refundable deposits	1310906 年出投資款	efundable deposits
141101短期投資 Short-term investments	141101 短期投資	hort-term investments
151101股票Sh: rm investments:ok	151101 股票	: rm investments:ok
161101021##Short-term investments-corporate	161101021	Short-term investments-corporate
17110103基金 Short-term investments- corporate	17110103 基金	hort-term investments- corporate
18110110其他 Short-term investments- other	18110110 其他	hort-term investments- other
191102短期投資跌價準備 Short-term investments falling price reserves	191102 短期投資跌價準備	hort-term investments falling price reserves
20應收款 Account receivable	20 應收款	ccount receivable
1211181收票據(jù) Note] receivable	1211181 收票據(jù)	ote] receivable
22銀行承兌匯票 Bank acceptance	22 銀行承兌匯票	ank acceptance
23商業(yè)承兌匯票 Trade acceptance	23 商業(yè)承兌匯票	rade acceptance
241121應收股利 Dividend receivable	241121 應收股利	ividend receivable
251128收利息 terestreceivable ************************************* 效果圖	251128 收利息	erestreceivable