Need advice on how to deal with big texts in cells

[ORoxo]

Hey, everyone,
I just joined a company a couple of weeks ago and I realized that the department I'm working on has a lot of room for improvement. I don't want to get into details, but we deal with risk management. As we can imagine, we have excel files with hundreds of risks and KRIs that were supposed to be monitored.

However, when each department describes a new risk, it usually takes them a lot of words. I would say two whole sentences, most of the times. This means that if one wishes to read everything, one would have to either adjust the lengh of the column or of the row which then takes too much screen space and then makes analysis harder because you only look at one thing at a time, two at most...

I think you get the sense of it. Therefore, I was wondering if you have dealt with something similar and, if so, if there's any piece of advice you could give me.

 

[Joe4]

Have you tried turning on the "Wrap text" option, which makes those particular rows taller without widening the columns so you can see it all?

Right-click on the cells you want to apply this to and select: Format cells --> Alignment --> Text control --> Wrap text

 

[ORoxo]

Have you tried turning on the "Wrap text" option, which makes those particular rows taller without widening the columns so you can see it all?

Right-click on the cells you want to apply this to and select: Format cells --> Alignment --> Text control --> Wrap text

First of all, thanks for your help, Joe!

Yes, that's what I have been doing, but still I get "big blocks" of cells. I guess it's the only way considering I can't force people to writing something under x characters, right?

 

[Joe4]

considering I can't force people to writing something under x characters, right?

Actually, you can. You can add special VBA code that runs automatically as a cell is manually updated. It is called Event Procedure code.

Here is example code below that will limit all entries in column C to 100 characters.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   Check to see if any cells updated in column C
    Set rng = Intersect(Target, Range("C:C"))
    If Not rng Is Nothing Then
        For Each cell In rng
'           Check to see if length is over 100
            If Len(cell) > 100 Then
'               Truncate entry at 100 characters
                Application.EnableEvents = False
                cell = Left(cell, 100)
                Application.EnableEvents = True
'               Inform user their entry has been truncated
                MsgBox "Entries are limited to 100 characters", vbOKOnly, "ENTRY TRUNCATED!"
            End If
        Next cell
    End If
                
End Sub

To apply this, right-click on the sheet tab name at the bottom of the screen that you want to apply this to, select "View Code", and paste this code in the resulting VB Editor window. Then test it out!

 

[Peter_SSs]

.. considering I can't force people to writing something under x characters, right?

Actually you can, and you can do it without vba.

1. Select the cell(s)/column(s) where you want to limit the text length.
2. Data ribbon tab -> Data Validation -> Data validation... -> Settings tab -> Allow: Text length -> Data: Less than or equal to -> Maximum: eg 100 -> Input message tab -> Enter an input message to advise the user what the limit is (once they get used to the limit you might want to remove this message so it doesn't pop up all the time & instead you could put a similar message in the Error Alert tab then it will only pop up if they actually try to exceed the limit) -> OK

Having given this alternative to Joe's method, I point out that both methods can have draw-backs so choose the one that suits you best:

Data Validation can be defeated if the user copies/pastes from a cell/range that does not have the same DV
The vba code can be defeated if the used does not enable macros

 

[sykes]

I was messing around with this, and thought of a different approach.
Rather than limiting the users' input, how about testing each selected cell for a certain character length (I set my example to 20), then if there are more characters than that, display the text for the user to read - in my case, I used a label. When the user's finished reading, they just click the label again, and it disappears.

I put a label on the sheet ("label1").
Changed it's background colour to something other than the sheet's colour - to make it easier to read the text.
Changed the Autosize property to "True"
Changed the Visible property to "False"
Then bunged this code into the sheet's module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    With Target

        If Len(.Value) > 20 Then
            Label1.Caption = .Value

'Position the label over the cell in question

            Label1.Top = .Top
            Label1.Left = .Left

'Make the label visible
            Label1.Visible = True
            
        End If
    End With
End Sub

Finally, this hides the label again, once the user clicks it:

Private Sub Label1_Click()
Me.Label1.Visible = False
End Sub

 

[ORoxo]

I guess I didn't explain myself properly, I'm sorry.

I mean, I could limit the user to a certain number of characters but I think that would do more harm than good. People would feel frustrated and would simply input something "because they have to".

 

[Peter_SSs]

I'm confused.
- You don't want them to write a lot because if they do you can only look at one or two at a time.
- You don't want to restrict how much they write, even though you did ask "I can't force people to writing something under x characters, right?"
- What do you want?

 

[ORoxo]

I'm confused.
- You don't want them to write a lot because if they do you can only look at one or two at a time.
- You don't want to restrict how much they write, even though you did ask "I can't force people to writing something under x characters, right?"
- What do you want?

You are right, Peter. I am confused myself, I'm sorry.

I will apply your suggestions and see how users react, I will then apply changes accordingly.

 

[sykes]

Didn't like my idea, then? It seems to solve all of your problems......