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:office:office" /><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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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:office:office" /><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

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
902
epactheactor
E
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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