Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting account details
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting account details
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) ))) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting account details
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? -- Rick (MVP - Excel) "SundayGirl" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting account details
On Wed, 22 Jul 2009 19:57:13 +0100, "SundayGirl"
wrote: 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
account # entry needs to return the account description | Excel Worksheet Functions | |||
Freezing Details | Excel Discussion (Misc queries) | |||
Can a service account be embedded in Workbook so that by default the macros are run with service account credentials and not the user credentials??? | Excel Discussion (Misc queries) | |||
details of month | Excel Worksheet Functions | |||
AVI details | Excel Programming |