Cell Character limit and overflow remaining characters to another cell?

coolguyhb

New Member
Joined
Apr 4, 2017
Messages
7
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Sektor

Well-known Member
Joined
May 6, 2011
Messages
2,834
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.

Code:
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

New Member
Joined
Apr 4, 2017
Messages
7
That seems to work well!! Is there a way to disallow splitting words?
 

Sektor

Well-known Member
Joined
May 6, 2011
Messages
2,834
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

New Member
Joined
Apr 4, 2017
Messages
7
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,098,993
Messages
5,465,878
Members
406,452
Latest member
GroupGoal

This Week's Hot Topics

Top