Converting text to columns when columns are varying widths

[johnmcpa]

Below is an excerpt of a file that I converted from pdf to a single column Excel file. I need this file separated into 3 columns. The first column is the account number. The second column is the description and the third column is the amount. Because the amounts are varying lengths I haven't been able to figure out how to make the selection for the second and third columns.

Thanks for any help.

John

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 192pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 width=256 colSpan=4>Account No Description Amount</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=4>0001 TEMPORARY ACCOUNT 826.18</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=4>1000 Investors Bank - Operating 80,430.09</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=4>1010 Investors Bank- Payroll 96,229.62</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=4>1020 Investors Bank - ADP 3,095.60</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=5>1030 Investors Bank - Adv. MMA 1014 5,003,629.57</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=4>1040 Signature Bank - Operating 8,781.14</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=5>1050 Signature Bank - Payroll 0064 540.00</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=5>1060 Signature Bank - MMA- 3200 15,669.39</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=4>1070 Profit Sharing Plan - NY LIFE 500.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=4>1100 Contract Receivables 1,495,588.40</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=4>1101 Retainages Receivable 1,363,391.23</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=5>1102 Retainage Receivable - Other 450,000.00</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=5>1110 Work in Process-Costs in Exces 216,289.00</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=4>1200 Officer Loans Receivable 288,225.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=4>1210 Employee Advances 8,048.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=5>1230 Due from AJAC Development, LLC 937,049.76</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=5>1235 Int. Rec - AJAC Development LL 44,322.00</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=5>1240 Due from Eastern Rental Corp. 66,861.00</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=18 colSpan=5>1245 Due from Seal/Oliveira JV 158,884.00</TD></TR></TBODY></TABLE>

 

[Haseeb Avarakkan]

Hello, Welcome to the board! try,

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Account No Description Amount</td><td style="font-weight: bold;;">Account No</td><td style="font-weight: bold;;">Description</td><td style="font-weight: bold;;">Amount</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">0001 TEMPORARY ACCOUNT 826.18</td><td style="text-align: right;;">0001</td><td style=";">TEMPORARY ACCOUNT</td><td style="text-align: right;;">826.18</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">1000 Investors Bank - Operating 80,430.09</td><td style="text-align: right;;">1000</td><td style=";">Investors Bank - Operating</td><td style="text-align: right;;">80430.09</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">1010 Investors Bank- Payroll 96,229.62</td><td style="text-align: right;;">1010</td><td style=";">Investors Bank- Payroll</td><td style="text-align: right;;">96229.62</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">1020 Investors Bank - ADP 3,095.60</td><td style="text-align: right;;">1020</td><td style=";">Investors Bank - ADP</td><td style="text-align: right;;">3095.6</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">1030 Investors Bank - Adv. MMA 1014 5,003,629.57</td><td style="text-align: right;;">1030</td><td style=";">Investors Bank - Adv. MMA 1014</td><td style="text-align: right;;">5003629.57</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">1040 Signature Bank - Operating 8,781.14</td><td style="text-align: right;;">1040</td><td style=";">Signature Bank - Operating</td><td style="text-align: right;;">8781.14</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">1050 Signature Bank - Payroll 0064 540.00</td><td style="text-align: right;;">1050</td><td style=";">Signature Bank - Payroll 0064</td><td style="text-align: right;;">540</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">1060 Signature Bank - MMA- 3200 15,669.39</td><td style="text-align: right;;">1060</td><td style=";">Signature Bank - MMA- 3200</td><td style="text-align: right;;">15669.39</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">1070 Profit Sharing Plan - NY LIFE 500.00</td><td style="text-align: right;;">1070</td><td style=";">Profit Sharing Plan - NY LIFE</td><td style="text-align: right;;">500</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">1100 Contract Receivables 1,495,588.40</td><td style="text-align: right;;">1100</td><td style=";">Contract Receivables</td><td style="text-align: right;;">1495588.4</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">1101 Retainages Receivable 1,363,391.23</td><td style="text-align: right;;">1101</td><td style=";">Retainages Receivable</td><td style="text-align: right;;">1363391.23</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">1102 Retainage Receivable - Other 450,000.00</td><td style="text-align: right;;">1102</td><td style=";">Retainage Receivable - Other</td><td style="text-align: right;;">450000</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">1110 Work in Process-Costs in Exces 216,289.00</td><td style="text-align: right;;">1110</td><td style=";">Work in Process-Costs in Exces</td><td style="text-align: right;;">216289</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">1200 Officer Loans Receivable 288,225.00</td><td style="text-align: right;;">1200</td><td style=";">Officer Loans Receivable</td><td style="text-align: right;;">288225</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">1210 Employee Advances 8,048.00</td><td style="text-align: right;;">1210</td><td style=";">Employee Advances</td><td style="text-align: right;;">8048</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">1230 Due from AJAC Development, LLC 937,049.76</td><td style="text-align: right;;">1230</td><td style=";">Due from AJAC Development, LLC</td><td style="text-align: right;;">937049.76</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">1235 Int. Rec - AJAC Development LL 44,322.00</td><td style="text-align: right;;">1235</td><td style=";">Int. Rec - AJAC Development LL</td><td style="text-align: right;;">44322</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">1240 Due from Eastern Rental Corp. 66,861.00</td><td style="text-align: right;;">1240</td><td style=";">Due from Eastern Rental Corp.</td><td style="text-align: right;;">66861</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">1245 Due from Seal/Oliveira JV 158,884.00</td><td style="text-align: right;;">1245</td><td style=";">Due from Seal/Oliveira JV</td><td style="text-align: right;;">158884</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=LEFT(<font color="Blue">A2,FIND(<font color="Red">" ",A2</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=TRIM(<font color="Blue">SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">A2,B2,""</font>),TEXT(<font color="Green">D2,"#,##0.00"</font>),""</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=LOOKUP(<font color="Blue">1E+307,--RIGHT(<font color="Red">A2,ROW(<font color="Green">A$1:INDEX(<font color="Purple">A:A,LEN(<font color="Teal">A2</font>)</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Copy down...

Change the Amount column cell format.

 

[johnmcpa]

Haseeb,

Thanks very much! That worked like a charm!

John