• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Limit the characters per line

Juzar22

Member
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
 

Attachments

  • Line Break.xls
    33.5 KB · Views: 7
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)?
 
Last edited:
Yes if the last word is long and cannot fit in 30 characters then should go to next line . Thank you
 
Last edited by a moderator:
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.
 
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.
 
Last edited by a moderator:
Juzar22, according to your attachment a beginner starter demonstration, result in cell D2 :​
Code:
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 !​
 
A variation for the fun :​
Code:
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
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, according to your attachment a beginner starter demonstration, result in cell D2 :​
Code:
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.
 
Based on Jindon's code (in similar issue)
Try this UDF
Code:
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
 
Back
Top