Large amount of cell text

  • Thread starter Thread starter Chris Feaster
  • Start date Start date
C

Chris Feaster

Hi,

We have a case where we have received records from a
Hotmail subpoena. Microsoft has put the records in an
Excel spreadsheet with all emails for a six month period
being contained in a single column and tens of thousands
of rows.

The problem I am having is that some of the cells that
contain the text from the email messages are very long,
and makes reading of the cells difficult. An attempt to
save to or publish to a different format for easier
reading causes truncation of the text in those particular
cells. Word Wrap, Autofit, nor simply double-clicking on
the cell margin doesn't provide a solution.

Has anyone dealt with this problem or know of a
solution? I am able to adjust each of these cells (not
easily) but we have around 100,000 cells through which to
sift.

What to do?

Thanks in advanced.
 
Chris,

1) Put a file name in the cell to the right of a cell with very long text
2) Select the cell with the long text
3) Run the macro below.

The macro will export the text to a .prn file in the folder C:\Excel (which must already exist) with the file name that you entered
in step 1. The file created can then be opened in Word. The macro can easily be modified to loop through all the cells, if you can
come up with an acceptable naming convention.

The macro should work on a cell with any amount of text.

HTH,
Bernie
Excel MVP


Sub ExportLongCellToPRN()

Dim FName As String
Dim WholeLine As String
Dim FNum As Integer

FName = "C:\Excel\" & ActiveCell.Offset(0, 1).Value & ".prn"

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

Open FName For Output Access Write As #FNum
Print #FNum, ActiveCell.Value

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub
 
Back
Top