How can I remove extra space when using autofit in Excel 2003?

[Doug Keith]

I have links set up in a spreadsheet, which pull text in from another sheet
in the workbook. The rows with the links are formatted to autofit the text.
When the text comes in, though, there is sometimes an extra line of space
above the text (e.g., if there are three lines of text, there is a blank line
above the text) instead of the text fitting perfectly within the cell. This
does not happen consistently, and it looks like all of the cells are
formatted the same way.

What settings could I use in combination with autofit to avoid this problem?
Or is it unavoidable, but there's a global fix (as opposed to manually
adjusting the height of each cell) I can use afterwards to correct it.

Thanks!

 

[Jacob Skaria]

Check horizontal alignment
Right click-->Format cells-->Alignment

OR

If there are blank lines use this formula and try
=TRIM(SUBSTITUTE(A1,CHAR(10),""))

If this post helps click Yes

 

[Doug Keith]

Hi! Thanks for the quick reply.

I can't quite figure out how to use the formula provided (do I insert it in
individual cells, or apply it globally somehow?). Still, I have a feeling I
didn't explain my issue clearly, so here's an example (since I can't seem to
paste a picture here). Imagine a spreadsheet with three rows of text:

It is unique for a television series
It seems believable enough for a TV series
It is fun to watch

In this example, each line of text should only occupy a row that's 18pts
tall. But what happens to me is that one of the three lines ends up coming
in at 36pts, and then I have to manually trim it back to 18pts. It's really
time-consuming. And I have both Wrap and Autofit set up, the cell
left-justified and text set to sit in the bottom of the cell, so it should
theoretically fit the row perfectly.

The other wrinkle is that the sheets I pull the data into have a bunch of
formatting already applied -- in a pristine, new excel sheet, the wrapping
and autofit work just fine.

Any thoughts? Again, thanks!

 

[Jacob Skaria]

Suppose you have your data in A7. Try the below formula in A8 as
=TRIM(SUBSTITUTE(A7,CHAR(10)," ")) Does that make any difference.???

If you are looking to equal the row height of all rows try this. Select the
entire row by a click on the row header OR Use Shift+SpaceBar to select the
entire row.. Entend your selection to cover all rows with data. Now move
mouse pointer to the left most (row header portion where rows are numbered)
You will notice a cross mark with which you can adjust the height. All
selected rows will assume the same height.

If this post helps click Yes