Add hard returns within cell at maximum 255 characters, for 2000 lines

atlmsk

New Member
Joined
Sep 16, 2008
Messages
6
The following sample narrative needs to be uploaded to our offsite storage facilty database, which needs a hard return at maximum 255 characters, but I would like the hard return at the beginning of each client matter number. So the cell will go from this:

4-2512 Wilbert Carl breuer, II v. Isiah Hemlock [*Correspondence, client's File, Atty notes 22-7270 Cheese Farm as Subrogee of King via v. Rudolph Hart [Billing, pleadings, corresp, discovery, client's File, original discovery, Atty notes 2025-7285 Dollar Bill v. Imc Pepper, Inc. [Corresp., Billing, pleadings, client's File 2060-5025 Hal v. Hall [Billing, Correspondence, pleadings, Atty notes, discovery 52-7327 Smith v. Sith [Discovery, Med Records Virginia, Med Records Matthew, Billing, Spe, cial damages.

to this (the hard returns can be symbols embedded in the cell that the upload will recognize so it formats the upload like this:

4-2512 Wilbert Carl breuer, II v. Isiah Hemlock [*Correspondence, client's File, Atty notes
22-7270 Cheese Farm as Subrogee of King via v. Rudolph Hart [Billing, pleadings, corresp, discovery, client's File, original discovery, Atty notes
2025-7285 Dollar Bill v. Imc Pepper, Inc. [Corresp., Billing, pleadings, client's File
2060-5025 Hal v. Hall [Billing, Correspondence, pleadings, Atty notes, discovery
52-7327 Smith v. Sith [Discovery, Med Records Virginia, Med Records Matthew, Billing, Spe, cial damages.

It may also be true that there is only one client matter number listed in a cell, but for which there is 750 characters in the cell, so the break needs to be at maximum 255 characters.

I'm exhausted trying to find this on the web. I have two thousand lines of narrative (all in column D) that I need to edit. Any help would be greatly appreciated.
Michael
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

AD_Taylor

Well-known Member
Joined
May 19, 2011
Messages
687
Quick question, if the 255 characters fall in the middle of a word I assume you don't want the word to be spread across lines? You'd prefer if it was kept together?

Also do all client numbers have a '-' in the middle?
And finally are there likely to be '-' s anywhere else other than client numbers? If they are used exclusively in client numbers it becomes simpler to write.
 

atlmsk

New Member
Joined
Sep 16, 2008
Messages
6
Hi. Thanks for your quick resonse.

1. I would like to keep the word together
2. All client matter numbers do have a"-" in the middle
3. I believe there would be no or very few other "-" in the text, and if there is I can manually edit.

Thanks.
Michael
 

AD_Taylor

Well-known Member
Joined
May 19, 2011
Messages
687
Give this code a go. Press Alt + F11 to open the VBA Editor, then insert a new module and paste the code into there.

Code:
Option Explicit

Sub SepClientNumbers()
    Dim rngChk      As Range
    Dim rng         As Range
    
    Dim rngC        As Range 'Loop Counter
    
    Set rng = Range("A" & Rows.Count).End(xlUp)
    Set rngChk = Range("A1", rng)
    
    For Each rngC In rngChk.Cells
        rngC.Value = HardReturnStrAtIndex(rngC.Value, 255)
    Next rngC
End Sub


Function HardReturnStrAtIndex(str As String, index As Integer) As String
    Dim strSplit()  As String
    Dim strTemp     As String
    Dim strOut      As String
    
    Dim i           As Integer 'Loop Counter
    
    strSplit = Split(str, " ")
    strTemp = strSplit(LBound(strSplit))
    
    For i = (LBound(strSplit) + 1) To UBound(strSplit)
        If InStr(strSplit(i), "-") Then
            strOut = strOut & strTemp & Chr(10)
            strTemp = strSplit(i)
        Else
            If Len(strTemp & " " & strSplit(i)) >= index Then
                strOut = strOut & strTemp & Chr(10)
                strTemp = strSplit(i)
            Else
                strTemp = strTemp & " " & strSplit(i)
            End If
        End If
    Next i
    
    strOut = strOut & strTemp
    HardReturnStrAtIndex = strOut
End Function

The code makes 2 assumptions:

1) That dashes (-) are only used within client numbers.
2) That by hard return you simply meant a carriage return not that the text should be transferred across mutliple cells.

You will probably need to change the cell references within the variables rng and rngChk to be relevant for your data range as well.
It does overwrite the current cell values so please test on a copy of your file or just don't save when closing. It ran pretty fast on my sample of 20 cells so should be fairly quick for a larger amount as well.

Hope this helps!
 

atlmsk

New Member
Joined
Sep 16, 2008
Messages
6

ADVERTISEMENT

Mr. Taylor,

I copied my column of data to Column A in a new worksheet. But I'm not sure where to change the range in your formula. Say for examply my range is A2 to A2500. What needs to be changed in your formula?

Michael
 

AD_Taylor

Well-known Member
Joined
May 19, 2011
Messages
687
Its in the first part of the code, the macro called SepClientNumbers. I'll put it in red here for you:

Code:
Option Explicit


Sub SepClientNumbers()
    Dim rngChk      As Range
    Dim rng         As Range
    
    Dim rngC        As Range 'Loop Counter
    
    Set rng = Range([COLOR=#ff0000][B]"A" [/B][/COLOR]& Rows.Count).End(xlUp)
    Set rngChk = Range([COLOR=#ff0000][B]"A1"[/B][/COLOR], rng)
    
    For Each rngC In rngChk.Cells
        rngC.Value = HardReturnStrAtIndex(rngC.Value, 255)
    Next rngC
End Sub

The 2 red parts need to be changed. The code currently will run from A1 downwards till the last row in that column.
If you wanted to go from B10 downwards for example you'd need to change those 2 lines to:
Code:
    Set rng = Range([COLOR=#ff0000][B]"B" [/B][/COLOR]& Rows.Count).End(xlUp)
    Set rngChk = Range([COLOR=#ff0000][B]"B10"[/B][/COLOR], rng)

For the range you've just mentioned this is the set up that should work (From A2 downwards):
Code:
    Set rng = Range([COLOR=#ff0000][B]"A" [/B][/COLOR]& Rows.Count).End(xlUp)
    Set rngChk = Range([COLOR=#ff0000][B]"A2"[/B][/COLOR], rng)

Of course in every example I've just given the end point is set automatically via code. If you'd like to set the range explicitly you can also do this.

The following code would set it to explicitly only check the range A1:A10
Code:
    '[COLOR=#008000]Set rng = Range("B" & Rows.Count).End(xlUp)[/COLOR]
    Set rngChk = Range([COLOR=#ff0000][B]"A1:A10"[/B][/COLOR])

For your given example that could be "A2:A2500" if you didn't want it to go any further.

Hope that all makes sense :)
 

atlmsk

New Member
Joined
Sep 16, 2008
Messages
6

ADVERTISEMENT

Ad_Taylor,

BINGO!!!!!!

What an enormous amont of help this code was. Now my import should go as planned and I can leave on vacation this Saturday knowing this project is complete.
Kudos to your skill and willingness to help.
Michael
 

atlmsk

New Member
Joined
Sep 16, 2008
Messages
6
Ad-Taylor,

Well it worked the first time. But when I compared my test list to the master list, I'd gotten off track somewhere and, line 672 was not the same information on each list. So I deleted the test spreadsheet where I downloaded the narrative into column A and now I get an error message when I run the macro that says "subscript out of range." All I did was copy your exact script into Visual Basic and change A1 to A2. Why do you suppose it worked the first time and not the second? It looks like it runs the script on just cell A2 and nothing else. So I tried your other suggestion to spell out the exact range ie A2:A2500, and I still get the subscipt out of range message.
 

AD_Taylor

Well-known Member
Joined
May 19, 2011
Messages
687
Unfortunately without the data in front of me I can't say why it worked the first time and then stopped.

When you get the error press the Debug button. Which line of code is highlighted as causing the error?
 

atlmsk

New Member
Joined
Sep 16, 2008
Messages
6
Got it. When I copied your formula to my spreadsheet, there were to many spaces between the first part of the macro and the second. Once I took out a couple of spaces everything is fine.
Thank you once again for using your powers for good and not evil!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,445
Messages
5,642,194
Members
417,259
Latest member
gtacw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top