Cell Character limit and overflow remaining characters to another cell?

[coolguyhb]

I know you can limit the number of characters using data validation, but can you set a character limit then overflow the remaining words to the next cell or be able to set a specific cell to overflow to?

i need to set the character limit in a cell to 59, any remaining words need to overflow to the 2 cells below then skip a cell and continue overflowing to another 3 cells.

Was thinking maybe use a text area to type in initially then have code or a formula pull the text from the textbox?

At a loss here. any help would be great!

Thanks

 

[Sektor]

This can be done with macros. Is it suitable for you?

 

[coolguyhb]

This can be done with macros. Is it suitable for you?

I'm willing to give it a try. What are you thinking? How would that be setup?

 

[Sektor]

Here's code which reacts to cells' changes (code must be is sheet's module). Here you can download workbook with example. I have created sample string with 906 characters. Copy this string to D2 cell - and macros will split this string as you need.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim r%, c%, x%, iPos%, iOffset%, length%
    Dim strOriginal$, strExtract$
    Const CHARS_COUNT% = 59
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    On Error GoTo MACROS_FAIL
    
    Application.EnableEvents = False
    
    strOriginal = Target.Value
    length = Len(strOriginal)
    r = Target.Row - 1: c = Target.Column
    iPos = 1
    
    If length > CHARS_COUNT Then
        While iPos <= length
            strExtract = Mid$(strOriginal, iPos, CHARS_COUNT)
            x = x + 1
            If x > 3 Then
                x = 0: iOffset = 2
            Else
                iOffset = 1
            End If
            r = r + iOffset
            Cells(r, c) = strExtract
            iPos = iPos + CHARS_COUNT
        Wend
    End If
    
    Application.EnableEvents = True
    
Exit Sub


MACROS_FAIL:
    Application.EnableEvents = True
    MsgBox "Error:" & Chr(10) & Err.Description, vbCritical
    
End Sub

 

[coolguyhb]

That seems to work well!! Is there a way to disallow splitting words?

 

[Sektor]

To disallow, you can:
1) Delete code
2) Use a cell on a sheet as a flag (say, digit 1 - allow, 0 - disallow) which will be checked by macros.

 

[coolguyhb]

I'm going to use the code you provided. It works the best out of everything i tried. Thank you so much! This was very helpful!!