Limiting number of characters in a line

I have a problem that I don't think is unique, but I haven't been able to find a resolution via the usual tools (Google, forums, etc).

I work for a localizer of text heavy software. Our developers normally use a converter to pull text out of a file and insert it into the program. This text is normally input into an Excel file since it has to correspond with files/locations in the program.

Since the space on the screen of the program is limited, we often face character limits when inputting text into Excel. Now, one cell might hold up to 255 characters, and I could restrict that with data validation, but within one cell I need to restrict each line (with a hard return) to 30 characters to fit on the screen.
So for example I have a row of cells that look like:
Hello, my name is Amejin. I like pizza, beer, and bowling.

Which need to look like:
Hello, my name is Amejin. I[HR]
like pizza, beer, and bowling.

Does anyone know a simple way to do this, or do I need to look for a macro and a VB expert?

You'd need VBA, but it isn't difficult.

A macro to do this on the entire worksheet?

This is pretty brutal -- it whacks the string every 30 characters. Select the cells of interest and run it.

Sub x()
    Const iMax  As Long = 30
    Dim r       As Range
    Dim cell    As Range
    Dim sCAR    As String
    Dim sCDR    As String

    Set r = Intersect(Selection, ActiveSheet.UsedRange)
    
    For Each cell In r
        If Not (cell.HasFormula Or IsEmpty(cell.Value)) Then
            sCDR = cell.Text
            sCAR = vbNullString
            Do Until Len(sCDR) = 0
                sCAR = sCAR & vbLf & Left(sCDR, 30)
                sCDR = Mid(sCDR, iMax + 1)
            Loop
            cell.Value = Mid(sCAR, 2)
        End If
    Next cell
End Sub

It could be modified to break lines at word boundaries that occur prior to 30 characters ...

Originally Posted by shg

This is pretty brutal -- it whacks the string every 30 characters. Select the cells of interest and run it.
{CODE}
It could be modified to break lines at word boundaries that occur prior to 30 characters ...

You're a stud!

How would one modify that code to break lines at word boundaries? I'm already pretty lost in the code, but I'm guessing you'd have to work backwards from char #30 to the first space encountered and tell it to break there? I think I understand a bit of how it's supposed to work, but I just don't know the code to do it.

Try this:

Sub x()
    Const iMax  As Long = 30
    Dim r       As Range
    Dim cell    As Range
    Dim sCAR    As String
    Dim sCDR    As String
    Dim iPos    As Long

    Set r = Intersect(Selection, ActiveSheet.UsedRange)
    
    For Each cell In r
        If Not (cell.HasFormula Or IsEmpty(cell.Value)) Then
            sCDR = cell.Value
            sCAR = vbNullString
            Do Until Len(sCDR) = 0
                iPos = InStrRev(Left(sCDR, iMax), " ")
                If iPos = 0 Then iPos = InStr(sCDR, " ")
                If iPos = 0 Then iPos = Len(sCDR)
                sCAR = sCAR & vbLf & Left(sCDR, iPos)
                sCDR = Mid(sCDR, iPos + 1)
            Loop
            cell.Value = Mid(sCAR, 2)
        End If
    Next cell
End Sub

Would you please mark the thread as Solved?

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.