Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
From word to Excel?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
From word to Excel?
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
From word to Excel?
Ron Rosenfeld wrote:
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 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
From word to Excel?
On Sat, 22 Mar 2008 16:36:16 -0400, "Jeff" wrote:
Ron Rosenfeld wrote: 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 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
From word to Excel?
Ron Rosenfeld wrote:
On Sat, 22 Mar 2008 16:36:16 -0400, "Jeff" wrote: Ron Rosenfeld wrote: 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 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
From word to Excel?
On Sun, 23 Mar 2008 06:52:25 -0400, "Jeff" wrote:
Thank you again Ron. Very kind of you. Jeff You're welcome. I trust this latter method worked? Glad to help. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Word copying info from Excel to Word without gridlines / bor | Excel Discussion (Misc queries) | |||
Copy from Word to Excel, and retain indent, plus word wrap | Excel Discussion (Misc queries) | |||
Link table from excel to word using word VBA | Excel Discussion (Misc queries) | |||
Excel Hyperlink doesn't open Word doc the same way as Word does | Excel Discussion (Misc queries) | |||
Print labels by using Excel data in a Word mail into word | Excel Discussion (Misc queries) |