修复粘贴的文本

Question

我有我从信用卡帐单

样品1号线复制这个文本文件：

August 18  August 18  Balance Conversion  :02/06   4,671.30 

样品线2：

August 1  August 2  *Php-Anytiefit-Ezypay Kuala Lumpur 2,300.00 

我将其从PDF文件复制到MS Excel文件中。我会得到下面的文本双空格，每行只是粘贴到一个单元格，如下所示。

我尝试使用文本函数=RIGHT(B73,LEN(B73)-E73+2)和阵列=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B73&""))等。我从研究中得到的数组，但我仍然会调整公式，因为月份字符数字每个月都在变化，而且还是单位或双位数日。

使用逗号和点分隔符，金额恒定为小数点后两位。除非有安装线，例如01/24，这个“二十四”之一将会出现在数额2,916.25之前，如0 1/2 4 2 , 9 1 6 . 2 5

我正在寻找使用VBA解决方案或函数来修复粘贴的值。

A u g u s t 1 8 A u g u s t 1 8 P o w e r M a c C e n t e r r G b 3：0 1/2 4 2，9 1 6。 2 5
A u g u s t 1 8 A u g u s t 1 8 B a l c n n e c o n v e r s i o n：0 2/0 6 4，6 7 1。 3 0
A u g u s t 1 A u g u s t 2 * P h p - A n y t i m e f i t - E z y p a y K u a l a L u p u r r 2，3 0 0。 0 0
A u g u s t 1 3 A u g u s t 1 5 S t a r b u c k s C o n s e s s s Q c 2 7 5。 0 0

Answer 1

这是通过运行它通过MSWORD

Sub pdf2excel() 

    ' import pdf file text into excel, using msWord as a proxy 

    ' set reference to microsoft word object library 

    Dim wdApp As Word.Application 
    Set wdApp = New Word.Application 

    Dim file As String 
    file = "C:\statements\statement.pdf" 

    Dim wdDoc As Word.Document 
    Set wdDoc = wdApp.Documents.Open(_ 
        Filename:=file, ConfirmConversions:=False, _ 
        ReadOnly:=True, AddToRecentFiles:=False, _ 
        PasswordDocument:="", PasswordTemplate:="", Revert:=False, _ 
        WritePasswordDocument:="", WritePasswordTemplate:="", _ 
        Format:=wdOpenFormatAuto, XMLTransform:="") 

' wdApp.Visible = false     ' can make msWord visible if you want ... would help in determining location of data 

    Dim cel As Range 
    Set cel = Range("d2")     ' put paragraph text in column D 

    Dim prgf As Paragraph 
    For Each prgf In wdDoc.Paragraphs 
     cel = prgf.Range.Text    ' put paragraph into worksheet cell 
     Set cel = cel.offset(1)    ' point to next cell down 
    Next prgf 

    Set cel = Range("b2")     ' put word text in column D 

    Dim wrd As Word.Range 
    For Each wrd In wdDoc.Words 
     cel = wrd.Text 
     Set cel = cel.offset(1) 
    Next wrd 

    wdDoc.Close False 
    Set wdDoc = Nothing 

    wdApp.Quit 
    Set wdApp = Nothing 

End Sub