Hi,

I have been able to extract data from an ancient system (mainframe and terminal emulation programs) in PDF that runs to over 2,000 pages and over 120,000 lines of data but I get stuck with an anomaly in the output and would really appreciate some help please:

A set of data looks like this:

Month: Oct 2015 Nov 2015 Dec 2015 JAN 2016 FEB 2016 MAR 2016

INVOICES: 1230 4500 1000 2230 2345 3456

CREDITS: 123 10% 4500-100% 100- 10% 22- 1% 23- 1% 346- 10%

The VBA code I have written looks for spaces and every time it sees a space it treats the data as a single number and transfers the number to the worksheet in columns, increasing the row number each time.

The data uses trailing negatives and the code converts these.

On the CREDITS line there are spaces between the trailing negative and the percentage except in one case 4500-100% the code can’t translate these instances and they don’t always have trailing zeros (for some reason there canbe positive credits).

I should be getting:

123

-4500

-100

-22

-23

-346

Instead I am getting:

123

-100

-22

-23

-346

Thanks!

- Sub processData( data, mycolumn)
- myoutput = ""
- myRow = 1
- For a = 1 To Len(data)
- If Mid(data, a, 1) <> " " Then
- myoutput = myoutput + Mid(data, a, 1)
- Else
- If Right(myoutput, 1) <> "%" Then
- myRow = myRow + 1
- 'adjust for trailing - signs
- If Right(myoutput, 1) = "-" Then myoutput = "-" & Left(myoutput, Len(myoutput) - 1)
- Sheets("Output").Range(myColumn & myRow) = myoutput
- End If
- myoutput = ""
- End If
- Next a
- End Sub