Currently I have macros set up in my excel that pastes a list when clicked.

However I am encountering an issue where I have to paste the copied list (from a pdf) into notepad before pasting into excel, so that it separates into cells instead of trying to excel cram the entire list into one cell when done directly.

I have tried creating a macro that would open a cell directly paste into it then cut out before pasting (Which works when done manually) as well as a number of different methods that were all dead ends.

My procedure is currently: Open PDF, ctrl a, ctrl c paste into notepad then ctrl a, cut paste into excel

If I could get help removing the notepad part of the procedure, I would be incredibly happy!

CodePudding user response：

If you paste the whole thing inside a cell like this:

Then you can use this script to do a text to rows operation.

Option Explicit

Sub TextToRows()
    
    Dim RG As Range
    Dim RGValue As String
    Dim LinesArray
    Dim LineCount As Long
    Dim I As Long
    
    Set RG = Selection
    If RG.Cells.Count <> 1 Then Exit Sub
    RGValue = RG.Value
    
    LineCount = Len(RGValue) - Len(Replace(Replace(RGValue, Chr(13), ""), Chr(10), ""))   1
    
    If InStr(1, RGValue, Chr(10)) = 0 Then
        LinesArray = Split(RGValue, Chr(13))
    Else
        LinesArray = Split(RGValue, Chr(10))
    End If
    
    RG.Offset(1, 0).Resize(LineCount, 1).Value = Application.Transpose(LinesArray)
    
End Sub

Viola!

CodePudding user response：

Your aim is reduced notepad step however I suggest I would remove the pdf step since poppler or xpdf pdftotext -layout is usually good to add the needed white space to keep text tabular. That can be drag and drop pdf on a shortcut that calls open new spreadsheet with text. And here is the usual core issue with cut and paste plain text as a framework.

Most spreadsheets as far back as last century have several text import methods, most common is add commas for csv import, but space separated text is accepted too. (I still use MSeXcel 97 portable as its an old familiar) It often requires some intervention to check detection, so here in Modern Open Office I specified combine spaces. Thus, introduces just one minor error here, "Addresss" is moved left-wards.

No Problem it has a spelling mistake so it's just that one that needs managing twice. 1st spell check and then move it right.

Every case can be different but if you write a macro to cover those corrections you repeat then it pays to run for the next import to include all the steps.

The simplest is plan ahead by tidy the text (here used tabs to replace spaces), then drag and drop, the results are usually cleaner data in = cleaner the cells are aligned.