1

I am using ALT-ENTER for hard word wrapping in a cell.

However, when I use it, the autofit height doesn't work properly. It fits the height of the cell to the first line.

Is there any solutions to this?

Improve this question

3 Answers 3

Reset to default
1

I couldn't find a fix for this in one go however did come up with something that worked well enough. It's a bodge for sure.

First count the number of lines in the cell you are looking at by using the following statement.

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

Then use a massive if statement =IF(B82="","",IF(B82=1,"1",IF(B82=2,"1 1",IF(B82=3,"1 1 1",IF(B82=4,"1 1 1 1",IF(B82=5,"1 1 1 1 1",IF(B82=6,"1 1 1 1 1 1",IF(B82=7,"1 1 1 1 1 1 1" and so on

That cell needs to be the width of the "1" and it will then wrap to the height of the other cell. It's not pretty but it worked for me.

Improve this answer
3
  • Interesting approach. Instead of the "massive if", why not do =REPT("1 ", B82)?
    – Scott - Слава Україні
    Nov 2, 2016 at 18:47
  • That's brilliant Scott! Cheers. Had I known this yesterday it would have saved me writing the if statement up 23 times as that was the most the s/s had in the column. Your way means the formula is effectively future proofed no matter the num ber of lines.
    – Matt
    Nov 3, 2016 at 11:30
  • Additionally - you could change the 1 to another word that's more appropriate in width using the =REPT( . 1 was simply for ease of not writing out a longer word repeatedly. Format to white and then no one will know it's there.
    – Matt
    Nov 3, 2016 at 13:49
0

All right, so there are a few ways about this -

  1. You can double click in between the row labels and it will adjust the row to auto-fit the contents of the largest cells
  2. You can do it the old fashioned way and go to Home → Cells → Format → Autofit Row Height
  3. You can use a keyboard shortcut for #2 - hold ALT and press H O A

Unfortunately, you'll need to do this manually because excel doesn't consider a "line break" when autofitting the row height.

Improve this answer
7
  • 1
    I have tried all of them again and again. However, it didn't work. Can you make a video to show or can you show with screenshots?
    – SNaRe
    Jan 25, 2014 at 13:52
  • Do you have any merged cells? autofit won't work when there are merged cells.
    – Raystafarian
    Jan 25, 2014 at 14:05
  • No there are no merged cells. The only thing that I do thing I type a word and press alt enter, type again press alt center. Afterwards, I click auto-fit and it only fits till the first line. Not others.
    – SNaRe
    Jan 25, 2014 at 14:07
  • Try to autofit columns first, then rows. If not, try to adjust the zoom on the spreadsheet to something else and then back.
    – Raystafarian
    Jan 25, 2014 at 14:09
  • I just found that this is happening one and only if I merge a cell. Otherwise it works. I need to find a solution for merged cell.
    – SNaRe
    Jan 25, 2014 at 14:20
0

Often ALT+HOA does not work immediately, or only for some rows in a column. Often this sequence works:

  1. Alt+HOA
  2. click in the cell of which the row height should reckon with Alt+Enter
  3. Click in its edit field on top of the Excel-screen
  4. Click on another cell in the row with the cell of which the row height should reckon with Alt+Enter, and Voila (hopefully)

Admittedly, this is a stupid and time-consuming procedure, but gazing at cells in an excel sheet with largely invisible texts may cost even more time.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .