Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I remove extra space when using autofit in Excel 2003?
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I remove extra space when using autofit in Excel 2003?
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 --------------- Jacob Skaria "Doug Keith" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I remove extra space when using autofit in Excel 2003?
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" wrote: 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 --------------- Jacob Skaria "Doug Keith" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I remove extra space when using autofit in Excel 2003?
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 --------------- Jacob Skaria "Doug Keith" wrote: 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" wrote: 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 --------------- Jacob Skaria "Doug Keith" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove extra space when text wrapping? | Excel Worksheet Functions | |||
How do I remove extra space when text wrapping? | Excel Discussion (Misc queries) | |||
Remove an extra space after a number. | Excel Worksheet Functions | |||
How do I get rid of extra white space when row autofit is on? | Excel Discussion (Misc queries) | |||
Autofit rows - white space. | Excel Discussion (Misc queries) |