Multiline cells are displayed as single line cells

Question

Looks like the problem occurs when spreadsheet is automatically generated or converted to Excel format from something different.

I've saved spreadsheet from google drive as excel spreadsheet (.xlsx) and found out that all multiline cells are displayed as single line cells:

If I select such cell's value and press enter it turns into multiline cell and finally displayed as intended (after setting proper row size):

To fix it you can just:

1. Select range with affected cells.
2. Press F2 and then enter again and again until all cells fixed.

But this way only acceptable for smaller spreadsheets.

Isn't there any better solution?

Answer 1

Once you have data in cells, run this short macro:

Sub FixHardReturns()
    Dim CH As String, r As Range, v As Variant
    CH = Chr(10)
    For Each r In ActiveSheet.UsedRange
        v = r.Value
        If v <> "" Then
            If InStr(1, v, CH) > 0 Then r.WrapText = True
        End If
    Next r
End Sub

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

Macros must be enabled for this to work!