Truncating cell data to fit in cell

[shankar]

Hi,

When a text is longer than the width of a cell, there are two options- one is to allow it to spill into the nearby cell(s), or wrap around so that the height of the cell increases. Is there a way to just truncate the string so that neither occur? I believe this option was there in earlier versions of Excel. I am using Office 2007. Thanks.

Shankar

[p45cal]

There might be three options, the last one being to increase the width of the cell; perhaps 4: shrink to fit.
The contents of a cell won't spill into the next one if there's something in that cell, for example a single space.
It is difficult to work out how much text extends beyond the end of a cell unless you have used a non-proportional (fixed width) font.
Otherwise you could use a little macro to truncate to say the first 10 characters in ech cell of a selection:[vba]Sub blah()
For Each cll In Selection.Cells
cll.Value = Left(cll.Value, 10)
Next cll
End Sub
[/vba]

[mdmackillop]

To truncate as you go, try
[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 And Target.Cells.Count = 1 Then
If Len(Target) > 10 Then Target.Value = Left(Target.Value, 10)
End If
Application.EnableEvents = True
End Sub

[/VBA]