Extracting account details

S

SundayGirl

Hi

I've been given a pdf document and asked to extract the account number name
and address for printing on to a label.

I have adobe standard and have converted the pdf into accessible txt but I
am struggling to get the account number. Its a 10 digit number at the start
of the line but never the same line number or the same interval of lines
between it and the next account number. Is there any way I can search for a
10 digit string at the start of each line?


Thanks

Wendy
 
J

jasontferrell

If there are a lot of spaces or commas, then this might work. It just
checks to see if the first 10 characters has a space or a comma and
returns false to indicate that line doesn't have an account number.
If there are other common characters, then you could also search for
those, but to search for any non-number would be a little more complex
and likely require a custom function.

=AND(ISERROR(FIND(" ",LEFT(A20,10))),ISERROR(FIND(",",LEFT(A20,10))))
 
R

Rick Rothstein

Are you searching a text in a file? In a variable in VB code? The text in a
single cell? The text in a range of cells? Somewhere else?
 
R

Ron Rosenfeld

Hi

I've been given a pdf document and asked to extract the account number name
and address for printing on to a label.

I have adobe standard and have converted the pdf into accessible txt but I
am struggling to get the account number. Its a 10 digit number at the start
of the line but never the same line number or the same interval of lines
between it and the next account number. Is there any way I can search for a
10 digit string at the start of each line?


Thanks

Wendy
Click to expand...

I don't know what you want to do with the results, but here is a macro that
cycles through a selected range of cells, and extracts any 10 digit strings
that are at the start of a line. In adjacent columns it also prints the
original address and the full contents of that line.


To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro
by name, and <RUN>.

==============================
Option Explicit
Sub AcctNums()
Dim rg As Range, c As Range
Dim rDest As Range
Dim i As Long

Set rg = Range("A1:A100") 'or wherever your data is
Set rDest = Range("D1")
'note that we clear out possible destination cells
Range(rDest, rDest(rg.Rows.Count + 1, 3)).Clear
rDest.Value = "Account Numbers"
i = 1
For Each c In rg
If Left(c.Value & " ", 11) Like "##########[!0-9]" Then
i = i + 1
rDest(i, 1).NumberFormat = "@"
rDest(i, 1).Value = Left(c.Value, 10)
rDest(i, 2).Value = c.Address
rDest(i, 3).Value = c.Value
End If
Next c
End Sub
==============================
--ron
 
Top