Small Formating issue in excel

A

anandapani

New Member
Joined
Dec 15, 2008
Messages
13
i have data in the below format
<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 4.65pt; WIDTH: 407.95pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" cellSpacing=0 cellPadding=0 width=544 border=0><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>TOX<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>STAFF_CODE<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254><o:p> </o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>LIST_ID<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>CHEM_LIST_TYPE<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>L_CONTEXT<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 6"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>THRESHOLD_BASIS_CODE<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 7"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>STAFF_CODE<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 8"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254><o:p> </o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 9"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>LIST_ID<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 10"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>CAS<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 11; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>CAS_SEQ

I need it divided in 3 rows in excel sheet i.e. every blank row should be treated as next row.
I am in an urgent need.
any immediate kind help is highly required,
Thanks for any help to solve this

<o:p></o:p>
</TD></TR></TBODY></TABLE>
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Thanks Peter for ur reply,
data listed in one below other with one blank row among different sets

for which, i need to seperate different sets of data
 
Upvote 0
I would reccomend you do something similar to the below. Assuming the data is in column a. You put the data set number in column B (each data set will require 4 rows) and then replicate the formulas below as many times as neccessary. These formulas work around offsetting principles with column c calculating how many rows to offset by.


<table style="border-collapse: collapse; width: 743pt;" border="0" cellpadding="0" cellspacing="0" width="990"><col style="width: 96pt;" span="3" width="128"> <col style="width: 167pt;" width="222"> <col style="width: 96pt;" span="3" width="128"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 96pt;" width="128" height="20">2</td> <td style="width: 96pt;" width="128">=(B5-1)*13</td> <td style="width: 96pt;" width="128">=OFFSET(A1,C5,0)</td> <td style="width: 167pt;" width="222">=OFFSET(A2,C5,0)</td> <td style="width: 96pt;" width="128">
</td> <td style="width: 96pt;" width="128">
</td> <td style="width: 96pt;" width="128">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">2</td> <td>=(B6-1)*13</td> <td>=OFFSET($A4,C6,0)</td> <td>=OFFSET($A5,C6,0)</td> <td>=OFFSET($A6,C6,0)</td> <td>=OFFSET($A7,C6,0)</td> <td>=OFFSET($A8,C6,0)</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">2</td> <td>=(B7-1)*13</td> <td>=OFFSET($A10,C7,0)</td> <td>=OFFSET($A11,C7,0)</td> <td>=OFFSET($A12,C7,0)</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">2</td> <td>=(B8-1)*13</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>
 
Upvote 0
anandapani said:
i have data in the below format
<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 4.65pt; WIDTH: 407.95pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" cellSpacing=0 cellPadding=0 width=544 border=0><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>TOX<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>



</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>STAFF_CODE<o:p></o:p>



</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254><o:p></o:p>



</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>LIST_ID<o:p></o:p>



</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>CHEM_LIST_TYPE<o:p></o:p>



</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>L_CONTEXT<o:p></o:p>



</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 6"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>THRESHOLD_BASIS_CODE<o:p></o:p>



</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 7"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>STAFF_CODE<o:p></o:p>



</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 8"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254><o:p></o:p>



</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 9"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>LIST_ID<o:p></o:p>



</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 10"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>CAS<o:p></o:p>



</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 11; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 190.3pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=254>CAS_SEQ

I need it divided in 3 rows in excel sheet i.e. every blank row should be treated as next row.
I am in an urgent need.
any immediate kind help is highly required,
Thanks for any help to solve this

<o:p></o:p>



</TD></TR></TBODY></TABLE>
Click to expand...
 
Last edited:
Upvote 0
Thanks peter,
Below solution works absolutely fine for my problem..but i have nearly 35 pages of similar kind of data.

what I really want to know is how to generate the same rule for all 35 pages,

i have tried the solution with below 3 data set example and it works absolutely fine but how to make it for huge data content????

I cannt write formulae in all cells ...i need some workaround for this.....

Thanks once again for your help



Peter Thompson said:
I would reccomend you do something similar to the below. Assuming the data is in column a. You put the data set number in column B (each data set will require 4 rows) and then replicate the formulas below as many times as neccessary. These formulas work around offsetting principles with column c calculating how many rows to offset by.


<TABLE style="WIDTH: 743pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=990 border=0><COLGROUP><COL style="WIDTH: 96pt" span=3 width=128><COL style="WIDTH: 167pt" width=222><COL style="WIDTH: 96pt" span=3 width=128><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="WIDTH: 96pt; HEIGHT: 15pt" width=128 height=20>2</TD><TD style="WIDTH: 96pt" width=128>=(B5-1)*13</TD><TD style="WIDTH: 96pt" width=128>=OFFSET(A1,C5,0)</TD><TD style="WIDTH: 167pt" width=222>=OFFSET(A2,C5,0)</TD><TD style="WIDTH: 96pt" width=128>

</TD><TD style="WIDTH: 96pt" width=128>

</TD><TD style="WIDTH: 96pt" width=128>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>2</TD><TD>=(B6-1)*13</TD><TD>=OFFSET($A4,C6,0)</TD><TD>=OFFSET($A5,C6,0)</TD><TD>=OFFSET($A6,C6,0)</TD><TD>=OFFSET($A7,C6,0)</TD><TD>=OFFSET($A8,C6,0)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>2</TD><TD>=(B7-1)*13</TD><TD>=OFFSET($A10,C7,0)</TD><TD>=OFFSET($A11,C7,0)</TD><TD>=OFFSET($A12,C7,0)</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>2</TD><TD>=(B8-1)*13</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD></TR></TBODY></TABLE>
Click to expand...
 
Upvote 0
If it's urgent, make use of the copy-paste function. If it can wait, look at the VBA help files and hope that someone can offer you some direction later.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
E
  • Question
VBA for Web Scraping help
Replies
0
Views
606
epactheactor
E
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
854
gvsumich
G
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
357
esben
E

Forum statistics

Threads
1,215,507
Messages
6,125,212
Members
449,214
Latest member
mr_ordinaryboy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top