Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to limit long text to column width?
I have a VBA function that returns a very long string. If the
adjacent cell is blank, the display of the string extends past the cell boundary. If the adjacent is nonblank, the display of the string stops at the cell boundary. How can I get the latter behavior even when the adjacent cell is blank? I don't want to truncate the string value, just the display of it. That is, LEFT() is not a solution for me. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to limit long text to column width?
Hi,
Formatting the cells to "wrap text" should do! Regards! Jean-Guy " wrote: I have a VBA function that returns a very long string. If the adjacent cell is blank, the display of the string extends past the cell boundary. If the adjacent is nonblank, the display of the string stops at the cell boundary. How can I get the latter behavior even when the adjacent cell is blank? I don't want to truncate the string value, just the display of it. That is, LEFT() is not a solution for me. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to limit long text to column width?
On Dec 1, 11:41 am, pinmaster
wrote: " wrote: I have a VBA function that returns a very long string. If the adjacent cell is blank, the display of the string extends past the cell boundary. If the adjacent is nonblank, the display of the string stops at the cell boundary. How can I get the latter behavior even when the adjacent cell is blank? Formatting the cells to "wrap text" should do! Thanks for the response. But that is not what I want. That wraps the text, normally increasing the row height to display the entire string, still. I want the display of the string truncated at the cell boundary -- as I wrote before, just like what happens when the adjacent cell is nonblank. Well, I 'spose I could change the row height back to its size before setting "wrap text". But that requires forethought (or undo/redo) to determine the pre-reformat row height. And it is a multistep process, the number of steps depending on how many mistakes I make <wink. It gets especially messy if I have to do this in a large number of rows with varying heights. Sigh, I 'spose I could create a yet-another macro. Admittedly, not difficult to do. If "wrap text" is the best answer, so be it. But I am hoping for something a little more straight-forward. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to limit long text to column width?
Hi,
I have excel 2000 and it works just fine, but if I format the row to "auto fit" then yes it will adjust the row height to show the entire text, try formatting your rows to a specific height see if that helps! Regards! Jean-Guy " wrote: On Dec 1, 11:41 am, pinmaster wrote: " wrote: I have a VBA function that returns a very long string. If the adjacent cell is blank, the display of the string extends past the cell boundary. If the adjacent is nonblank, the display of the string stops at the cell boundary. How can I get the latter behavior even when the adjacent cell is blank? Formatting the cells to "wrap text" should do! Thanks for the response. But that is not what I want. That wraps the text, normally increasing the row height to display the entire string, still. I want the display of the string truncated at the cell boundary -- as I wrote before, just like what happens when the adjacent cell is nonblank. Well, I 'spose I could change the row height back to its size before setting "wrap text". But that requires forethought (or undo/redo) to determine the pre-reformat row height. And it is a multistep process, the number of steps depending on how many mistakes I make <wink. It gets especially messy if I have to do this in a large number of rows with varying heights. Sigh, I 'spose I could create a yet-another macro. Admittedly, not difficult to do. If "wrap text" is the best answer, so be it. But I am hoping for something a little more straight-forward. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to limit long text to column width?
You can check each adjacent cell and if empty add an apostrophe
or a space in it.... If len(rCell.Offset(0, 1).Value) = 0 then rCell.Offset(0, 1).Value = "'" Or you could use SpecialCells and find all blanks in the adjoining column and add the same. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) wrote in message I have a VBA function that returns a very long string. If the adjacent cell is blank, the display of the string extends past the cell boundary. If the adjacent is nonblank, the display of the string stops at the cell boundary. How can I get the latter behavior even when the adjacent cell is blank? I don't want to truncate the string value, just the display of it. That is, LEFT() is not a solution for me. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to limit long text to column width?
On Dec 1, 3:25 pm, Jean-Guy wrote:
I have excel 2000 and it works just fine, but if I format the row to "auto fit" then yes it will adjust the row height to show the entire text First, sorry, I neglected to say that I have Office Excel 2003. Based on your observation, that might make a difference. I do not see an option or operation to "auto fit" rows per se. But when I select "wrap text" for the cell format, the row height is changed automatically. I do not see any way to disable that, except.... try formatting your rows to a specific height see if that helps! Yes. But in the posting to which you responded, I explained my reservations about doing that, namely: "It gets especially messy if I have to do this in a large number of rows with varying heights". Again, if that's the only way, so be it. I am hoping someone will know a more straight-forward way. Nonetheless, thanks for your thoughts. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to limit long text to column width?
If you select your column and format it to "wrap text",
Auto-Fit is *automatically* turned on. It (Auto-Fit) will turn itself off if the row height is manually changed. In fact, many of the question in these groups have been on how to turn it on again, after it was unintentionally turned off. (<Format <Row <Auto-Fit) For you, simply select the range you intend to use by clicking on the *row* headers and dragging down, and then change the default row height. from 12.75 to say 13 or 12.5, and you'll see that the Auto-fit will be off for those selected rows. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === wrote in message ... On Dec 1, 3:25 pm, Jean-Guy wrote: I have excel 2000 and it works just fine, but if I format the row to "auto fit" then yes it will adjust the row height to show the entire text First, sorry, I neglected to say that I have Office Excel 2003. Based on your observation, that might make a difference. I do not see an option or operation to "auto fit" rows per se. But when I select "wrap text" for the cell format, the row height is changed automatically. I do not see any way to disable that, except.... try formatting your rows to a specific height see if that helps! Yes. But in the posting to which you responded, I explained my reservations about doing that, namely: "It gets especially messy if I have to do this in a large number of rows with varying heights". Again, if that's the only way, so be it. I am hoping someone will know a more straight-forward way. Nonetheless, thanks for your thoughts. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to limit long text to column width?
On Dec 1, 4:48 pm, I wrote:
I neglected to say that I have Office Excel 2003. Based on your observation, that might make a difference. I do not see an option or operation to "auto fit" rows per se. Of course, I was wrong. Format / Rows / Auto Fit does exist. I overlooked it because the Format menu was not fully expanded. Oh well, based on the responses, it is clear that there is no simple method <sigh. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text to column, fixed width | Charts and Charting in Excel | |||
Text to column,fix width | Charts and Charting in Excel | |||
Text to column, fix width | Excel Discussion (Misc queries) | |||
Text to column, fixed width | Excel Discussion (Misc queries) | |||
fixed column width with text string | Excel Discussion (Misc queries) |