|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
那个自定义函数问题很大。
你大意了,没认真与源数据比较。
我花时间写了代码,你把待处理的“原始文件.txt”放在桌面上,然后运行下列代码试试:
Sub ljljlj()
Dim theStr$, my_TextFileFullName$, a As Variant, i&, j&
Dim theMatches As Object, theMatch As Variant, reg As Object
'
my_TextFileFullName = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\原始文件.txt"
With CreateObject("ADODB.Stream")
.Type = 2
.Mode = 3
.Open
.LoadFromFile my_TextFileFullName
.Charset = "UTF-8"
.Position = 2
theStr = .ReadText
.Close
End With
Set reg = CreateObject("VBScript.RegExp")
With reg
.Global = True
.MultiLine = True
.Pattern = "^([^\r\n]*)(\r?\n|\r)?"
Set theMatches = .Execute(theStr)
End With
'
reg.Pattern = "\s"
With Sheet1
For Each theMatch In theMatches
theStr = theMatch.submatches(0)
If reg.test(theStr) Then theStr = reg.Replace(theStr, "")
'
If InStr(1, theStr, "|") > 0 Then
a = Split(theStr, "|")
If Left(a(0), 1) <> "-" Then
i = i + 1
For j = 0 To UBound(a)
theStr = a(j)
If InStr(1, theStr, ",") > 0 Or InStr(1, theStr, ".") > 0 Then theStr = my_replace(theStr)
.Cells(i, j + 1) = theStr
Next j
End If
End If
Next theMatch
.Cells(1).CurrentRegion.Columns.AutoFit
End With
Set reg = Nothing
End Sub
Function my_replace(theStr$) As Double
Dim reg As Object
'
theStr = Replace(theStr, ".", ",")
Set reg = CreateObject("VBScript.RegExp")
With reg
.Global = True
.Pattern = "[^\d\.,-]"
If .test(theStr) Then theStr = .Replace(theStr, "")
.Pattern = "-$"
If .test(theStr) Then theStr = "-" & .Replace(theStr, "")
.Pattern = ",(?=\d{2}[^\d]*$)"
If .test(theStr) Then theStr = .Replace(theStr, ".")
End With
my_replace = theStr
Set reg = Nothing
End Function |
评分
-
1
查看全部评分
-
|