Limit the characters per line

[Juzar22]

Hi All

I want line break after every 30 characters in excel, attached is example file where i have done manually line break by ALT+ENTER
Can someone please provide me the VBA or some kind of formula to do it automatically, Appreciate your response

 

[vletm]

Juzar22
Your wrote
... Limit the characters per line ... and
... break after every 30 characters ...
Should it also take care about words?
Or
Could You adjust needed columns width that it shows 'good' (You're already using 'Wrap text'-option)?

 

[Juzar22]

Yes if the last word is long and cannot fit in 30 characters then should go to next line . Thank you

 

[vletm]

Juzar22
You write something,
but You show something else ... hmm?
I asked two questions ( ... or ... ).
You answered with 'Yes'
and
'Yes' to both questions means,
that Your thread could be solved.

 

[Juzar22]

Sorry regarding column adjustment no i do not want to wrap text as per column adjustment i need line break after every 30 characters regardless column width.

 

[vletm]

Juzar22
One possible is to
... use Cell C2's Juzar-function as in sample.

 

[Marc L]

Juzar22, according to your attachment a beginner starter demonstration, result in cell D2 :​

Sub Demo1()
    Dim S$(), P&, N&, L&
        S = Split([A2].Value2)
        P = Len(S(0))
    For N = 1 To UBound(S)
        L = Len(S(N))
        P = P + 1 + L
        If P > 30 Then P = L: S(0) = S(0) & vbLf & S(N) Else S(0) = S(0) & " " & S(N)
    Next
        [D2].Value2 = S(0)
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Marc L]

A variation for the fun :​

Sub Demo1vf()
    Dim S$(), P&, C&, N&, L&
        S = Split([A2].Value2)
        P = Len(S(C))
    For N = 1 To UBound(S)
        L = Len(S(N))
        P = P + 1 + L
        If P > 30 Then C = C + 1: P = L: S(C) = S(N) Else S(C) = S(C) & " " & S(N)
    Next
        ReDim Preserve S(C)
        [D2].Value2 = Join(S, vbLf)
End Sub

You may Like it !
​

 

[Juzar22]

Juzar22
One possible is to
... use Cell C2's Juzar-function as in sample.

Actually i have to copy this text and put it in our company program when i copy and paste this text then it is showing complete text without line break , for instance i copy this text and put it in notepad no difference.

 

[Juzar22]

Juzar22, according to your attachment a beginner starter demonstration, result in cell D2 :​

Sub Demo1()
    Dim S$(), P&, N&, L&
        S = Split([A2].Value2)
        P = Len(S(0))
    For N = 1 To UBound(S)
        L = Len(S(N))
        P = P + 1 + L
        If P > 30 Then P = L: S(0) = S(0) & vbLf & S(N) Else S(0) = S(0) & " " & S(N)
    Next
        [D2].Value2 = S(0)
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

Thank you so much with this code my query is solved.

 

[vletm]

Juzar22
Actually You should know all needed details BEFORE you could open a new thread.
You're still changing Your thread.

 

[YasserKhalil]

Based on Jindon's code (in similar issue)
Try this UDF

Function LimitCharacter(txt As String)
    With CreateObject("VBScript.RegExp")
        .Global = True
            txt = Application.Trim(Replace(txt, vbLf, " "))
            .Pattern = "(.{1,30})( |$)"
            txt = .Replace(txt, "$1" & vbLf)
            .Pattern = "\n+(?!.)"
            LimitCharacter = .Replace(txt, "")
    End With
End Function

 

[Juzar22]

Juzar22
Actually You should know all needed details BEFORE you could open a new thread.
You're still changing Your thread.

Sorry i will take note of your advice !!