From word to Excel?

[Jeff]

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

 

[Ron Rosenfeld]

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

 

[Jeff]

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

Dear Ron

Thank you so much for helping.

I tried it as described on the xls sample file I had posted above, but I
got a Microsoft Visual Basic error:
=========
Run-time error '432':
File name or class name not found during Automation operation
===========
Debug button took me to the following highlighted line (yellow):
Set re = CreateObject("vbscript.regexp")

For the record, I am using Excel 2002 with the latest Office updates.
It is possible I have the macros disabled from some past MS security
measure but am not sure.

Jeff

 

[Ron Rosenfeld]

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

Dear Ron

Thank you so much for helping.

I tried it as described on the xls sample file I had posted above, but I
got a Microsoft Visual Basic error:
=========
Run-time error '432':
File name or class name not found during Automation operation
===========
Debug button took me to the following highlighted line (yellow):
Set re = CreateObject("vbscript.regexp")

For the record, I am using Excel 2002 with the latest Office updates.
It is possible I have the macros disabled from some past MS security
measure but am not sure.

Jeff

I don't understand why you got that error. I've not read of that happening
before with createobject. I would have thought that if you had macros
disabled, you would have seen some message cluing you in.

In any event, try this:

1. When in the VB Editor, from the main menu bar select Tools/References and
then select "Microsoft VBScript Regular Expressions 5.5" from the dropdown
list.

2. Replace the code I gave you with the code below:

===================================================
Option Explicit
Sub F1099b()
'Requires reference to be set to
'Microsoft VBScript Regular Expressions 5.5
Dim rg As Range, c As Range
Dim Str As String, I As Long
Dim re As RegExp, mc As MatchCollection
Set re = New 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

 

[Jeff]

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

Dear Ron

Thank you so much for helping.

I tried it as described on the xls sample file I had posted above,
but I got a Microsoft Visual Basic error:
=========
Run-time error '432':
File name or class name not found during Automation operation
===========
Debug button took me to the following highlighted line (yellow):
Set re = CreateObject("vbscript.regexp")

For the record, I am using Excel 2002 with the latest Office updates.
It is possible I have the macros disabled from some past MS security
measure but am not sure.

Jeff

I don't understand why you got that error. I've not read of that
happening before with createobject. I would have thought that if you
had macros disabled, you would have seen some message cluing you in.

In any event, try this:

1. When in the VB Editor, from the main menu bar select
Tools/References and then select "Microsoft VBScript Regular
Expressions 5.5" from the dropdown list.

2. Replace the code I gave you with the code below:

===================================================
Option Explicit
Sub F1099b()
'Requires reference to be set to
'Microsoft VBScript Regular Expressions 5.5
Dim rg As Range, c As Range
Dim Str As String, I As Long
Dim re As RegExp, mc As MatchCollection
Set re = New 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

Thank you again Ron. Very kind of you.

Jeff

 

[Ron Rosenfeld]

Thank you again Ron. Very kind of you.

Jeff

You're welcome. I trust this latter method worked? Glad to help.
--ron