On Sat, 22 Mar 2008 14:38:38 -0400, "Jeff" wrote:

Hi

I am doing my taxes and need to find a way to convert my broker's 1099B
(stocks transactions during the year) from a text pdf form or Word
document into an excel format. Is there a way to do this?

I can receive my 1099B data from my broker as a pdf or as a printed
statement. But I need it in the form of a Excel spreadsheet with
multiple columns. As a small example (the list is much longer which is
why I am asking), I put a representative sample copied and pasted from
the pdf into Word the same data copied and pasted from the pdf to an
Excel document. They are at:
http://www.orthohelp.com/1099b.doc
http://www.orthohelp.com/1099b.xls

Is there a way to convert this into an excel spreadsheet format with
data columns that I can manipulate?

Thanks.

Jeff


Since you can get the data into the excel worksheet, a macro can parse it up
the way you would like.

Of course, you could use the Data/Text to columns wizard, but you'll have a
fair amount of cleanup to do after since you have an inconsistent "$", and also
no <space between the end of the amount and the beginning of the company name
(and I presume some company names could start with a digit).

I have assumed that all amounts are reported to two decimal digits, as was the
case on the example you posted.

If your example is not representative, some changes may need to be made.

In any event, once you copy the data into your worksheet, run the F1099b Macro
and it should parse things out.

To enter the macro, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this, first SELECT the cells that need to be Parsed. Then,<alt-F8
opens the macro dialog box. Select F1099b and <RUN.

=====================================
Option Explicit
Sub F1099b()
Dim rg As Range, c As Range
Dim Str As String, I As Long
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = _
"^(\S+)\s(\S+)\s(\S+)\s(\S+)[\s$]+(\S+)[\s$]+([\d,]+\.\d\d)(.*$)"
'could setup rg in a variety of ways
Set rg = Selection
For Each c In rg
Str = c.Value
If re.test(Str) = True Then
Set mc = re.Execute(Str)
For I = 1 To mc(0).submatches.Count
Select Case I
Case 1, 3
c.Offset(0, I).NumberFormat = "@"
Case 2
c.Offset(0, I).NumberFormat = "mm/dd/yyyy"
Case Else
c.Offset(0, I).NumberFormat = "General"
End Select
c.Offset(0, I).Value = mc(0).submatches(I - 1)
Next I
End If
Next c
End Sub
=========================
--ron