How to remove uneven spacing in excel sheet

W

Wismer

Board Regular
Joined
Jun 7, 2006
Messages
51
Hi,

I am trying to download some data from the server in excel format which I was previously downloading in PDF format.

Appearance wise the data looks same in excel file as it was in PDF.

Since I want to sort and apply few calculations on it, I need to get rid of uneven spacing of rows and columns between the data and also the page break wordings which repeats every time.

Is there any short way in excel to clean the file so that the information appears without any gap in rows. (Currently the gaps are uneven like for example in some places there is one extra blank line and sometimes its two extra blank line and so on. Also the sort features does not work as the message says " this operation requires the merged cells to be identically sized" )

Any help greatly appreciated.

thanks.
Wismer
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
I would get rid of all merged cells first. Select all cells in the worksheet by clicking on the empty gray square in the upper left corner of the sheet and then clicking on the Merge & Center button on your toolbar.

Then you can get rid of empty rows by selecting a column and then going to Edit -> GoTo -> Special-> Blanks. This should select all blank cells in the column. Then go to Edit -> Delete-> Entire Row
 
Upvote 0
thanks for your reply.

I am unable to perform that function.
The message says selection is too large.

I have data around 6000 plus lines with uneven cell merge and spaces.


Still trying to find a way.....

Thanks.
Wismer
 
Upvote 0
Wismer

Did you remove the merged cells?

If you did perhaps you could now do a sort to send the blank rows to the bottom.

PS Some sample data might help.:)
 
Upvote 0
when i tried to remove merge cell it says
"The selection contains multiple data values. Mergin into one cell will keep the upper-left most data only."

And after clicking ok to this message the sheet goes blank totally. I dont see no data nowhere.

For confidentially reason I am unable to paste the portion of that data.

Thanks,
Wismer
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
CSV From Quickbooks Issue
Replies
1
Views
54
Alex Blakenburg
A
D
  • Question
Completely cleaning account numbers and storing with a specific format
Replies
2
Views
596
diversification
D
J
  • Question
Using VBA to move PDFs with specifications
Replies
2
Views
607
johnny51981
J
H
  • Solved
Need to remove spaces from cells
Replies
5
Views
731
Fluff
Fluff
J
  • Question
Record Macro Query- Ctrl + F & Ctrl + Shift + Arrow
Replies
6
Views
651
Joe4
Joe4

Forum statistics

Threads
1,214,661
Messages
6,120,790
Members
448,994
Latest member
rohitsomani

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