Fit cell height to number of true lines (no text warpping)

[MarcoU]

Hi everybody, I am new, this is my first post.
Anyone can help me with the following question?

Given an excel cell with line breaks (Alt+Enter or line breaks from text copied and pasted), is there a way to adjust the height of the cell so that it fits the number of text lines without wrapping/ showing the full text?
My need is just to realize at a glance how many rows are there in a cell, but not to see the full content, which may be quite long for each line.

Background:
I am exporting to excel the content of an Access field Excel via VBA code:
DoCmd.OutputTo acOutputQuery, [query name], acFormatXLS
The content of this access field needs to have line breaks, either entered manually or via VBA code.
I am trying to understand if there is any character that I can use to achieve what mentioned above.

Thanks for your help

 

[MarcoU]

... no way?

 

[Andrew Fergus]

Hello and welcome to MrExcel!

the following formula will count the number of instances of the hidden carriage return in text and add 1 to show how many lines of text are contained in one cell:

=1+LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))

Copy down beside your data.

I believe the hidden character is Alt+Enter which has the ASCII code 10. This will show the value 1 where there is 1 line of text, 2 or more where there are hidden Alt+Enters.

You could then either use this value in a macro, or just double height every row in your spreadsheet?

I trust this helps.
Andrew

 

[Rick Rothstein]

First set the column width... make the column with the text you want to fit real wide and then double click the right hand line next to its letter up in the column letter header... this will autofit the column width. Next, make the row with the text you want to fit real tall and then double click the bottom line under the row's number in the row number header... this will autofit the rows height.

 

[MarcoU]

Thank you Andrew.
So you suggest to use some code that counts the number of lines contained in the cell and sets accordingly the height of that row.
I am completely new to excel VBA but will explore this.

I suspect that the main problem I will meet is coverting the number of lines into the correct cell heigth (which I suppose is always to be expressed as points)... any suggestion?

 

[Andrew Fergus]

Hello Rick
That method also occurred to me but the OP stipulated:
"My need is just to realize at a glance how many rows are there in a cell, but not to see the full content, which may be quite long for each line."
I have seen data like this where the number of rows can exceed the height of the screen such that manipulation of the screen and data is problematic if you auto set the height.
Andrew

 

[Andrew Fergus]

Hello Marco
My hope was that by having the number of lines of text in another column you could see something had say 10 or 20 lines but as you mentioned earlier you don't necessarily want to see all of that. You could use a macro to set the row height as a function of the number of lines (e.g. 1 + half the number of lines) if you really want this but I don't recommend it given it can get very messy. My preference is to open up every row to say 2 lines high so that you can see where a line is more than 1, but you don't need to see lines 3 through x unless you actually go into the cell. Did you know you can also change the height of the formula bar? I always set mine to two so that I'm aware of data that occupies a second line. Given there is a column stipulating how many lines high the data is, you can always keep an eye on that if you are manipulating the data.
Andrew

 

[MarcoU]

Hi, thank you both.
The method suggested by Rick is very easy but as Andrew says things might mess up with very long lines.
OK Andrew, now I got your suggestion!
Yes, I know that I can increase the height of the formula bar, but as this spreadsheet is meant to share from a database with people even less experienced than me, so my ideas was to set the content in a way that is was easily understandable.