Automatically put line break after certain number of Character in a line in Excel

This will insert a break after the first 60 characters.

=LEFT(A1,60)&CHAR(10)&RIGHT(A1,LEN(A1)-60)

However, you have to set Wrap Text to make it display properly. Which brings me to my next point, which is that if you set all cells to Wrap Text in the first place then you don't have to mess around with a formula. Why would you need actual line break characters in the data?

To Attach a Workbook with good sample data:
1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit

I think I can help if you tell me a little more about what you are doing with the text.

The text is rather long, nearly 2000 characters, and my formula only handles the first 60-character chunk, so a formula solution isn't going to work well unless it's a very long formula that can insert up to 30-40 line breaks. This would be very straightforward to do with a macro and I could help with that but I hesitate to put any effort into a solution until I really understand the problem.

After I change the formatting for column B, here is what I see. [shoro=line breaks.jpg] I am assuming you must see the same thing but maybe not--after seeing your sample I am at a complete loss to understand why you need to insert line breaks. Your desired solution isn't any different to work with than the original text. I don't see the benefit of having spent hours to manually produce the type of result you show in column C.

What are you doing with the text after you reformat it that you can't do with it in its original state?

Here's a user defined function:

Option Explicit

Function BreakLine(ByVal CrntCell As Range, ByVal WrapAt As Long) As String
    Dim CrntSpacePos    As Long, _
        CommaPos        As Long, _
        PeriodPos       As Long, _
        NextSpacePos    As Long, _
        StringLength    As Long, _
        StartPos        As Long, _
        ControlPointer  As Long, _
        OldString       As String, _
        Wrap            As String
        Wrap = Chr(10)
        
    OldString = Range(CrntCell.Address).Value
    ControlPointer = 1
    StringLength = Len(OldString)
    
    NextSpacePos = 1
    
    For StartPos = 1 To StringLength
        CrntSpacePos = InStr(StartPos, OldString, " ")
        CommaPos = InStr(StartPos, OldString, ",")
        PeriodPos = InStr(StartPos, OldString, ".")
        NextSpacePos = InStr(CrntSpacePos + 1, OldString, " ")
        If NextSpacePos > WrapAt * ControlPointer Then
            Debug.Print WrapAt * ControlPointer, CrntSpacePos, NextSpacePos
            Mid(OldString, CrntSpacePos, 1) = Wrap
            ControlPointer = ControlPointer + 1
        End If
            
            
        StartPos = NextSpacePos
        If CrntSpacePos = 0 Or NextSpacePos = 0 Then Exit For
    Next StartPos
    
   BreakLine = OldString
    
End Function

in a blank cell: "=BreakLine(ref,#), ref = the address of the cell with string to break (e.g. A2), # = a number indicating break positions (60, 100, ...)
See cell D2 in the attachment

Welcome to the forum cwhitley! As a new member, please take the time to review our rules. There aren't many, and they are all important.

Originally Posted by cwhitley

There is a different [need] for my project.

If your need is different, you need to post a new question.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.