Subtitle Formatting Help

HelloEx

New Member
Joined
Mar 12, 2009
Messages
5
Hi

Not sure how to go about doing this. I want to time the text of subtitle so it displays the specific text at the right time.


I have column A which is the starting frame number. Column C is the Text and they are different lengths in each row.


Column A Column C
00003 This is the text column and in different lengths.
00055 This is the second subtitle text in the column and long
00225 This is the third subtitle text.

What I want to do is depending on the length of the text in column C, have excel insert another Row (lets say if text is longer that 15 characters) everything else in text gets shifted down to the next newly inserted row and if another 15 characters is passed then that gets inserted into another newly added row right below it and so on.
Now there is A:1 which has 00003 , A:2 and A:3 is going to be blank.
A:4 is 00055. I want A:2 and A:3 to be the number which evenly fits between 00003 and 00055. So the new inserted one would look like this:

Column A Column C
00003 This is the text
00021 column and in different
00038 lengths.
00055 This is the second subtitle....

I only want the text which gets inserted to the new row to be cut off during spaces and not in the middle of a word. In the example above I didn't count the 15 character length for text.

Hope this makes sense to someone out there and can help me. Any help appreciated. Thanks
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,546
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
Always round up? What happens to the numbers on the last line? In other words:

00225 This is the third subtitle text.

Becomes

00225 This is the thi
rd subtitle text.

what number goes on the last line?

Do you want to break up the words, like I just did, or stop at the end of the word that is in the middle of the 15th character?
 

HelloEx

New Member
Joined
Mar 12, 2009
Messages
5
Hi Thanks.

The last line will have a number which will basically have no text. I should have added that in the example. Last number will be like an end number. I would like all the lines to be split but at the end of the word and not in the middle as long as it is within the (15) or user defined character limit. Once I have this in place. I can easily add end frames on the new column B which will be lets say 5 frames less than the next number in column A. From the example below it would be 00295 as end frame and 00225 as start point for the subtitle "This is the third". For the second line it would be 00370. Note below the 00300 is what got calculated when "subtitle text." got shifted to the next line. 225+375/2. The number 2 is the number of lines added +1.

like

00225 This is the third
00300 subtitle text.
00375

Hope this helps

Thanks
 

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,546
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
Try this macro. Change the constants at the top to reflect your sheet name and beginning range in column A. That's where the first number should be.

Code:
Public Sub FormatSubtitle()
  Const wshName As String = "Sheet1"
  Const rngStart As String = "A1"

  Dim rng1 As Excel.Range
  Dim wsh As Excel.Worksheet
  
  Dim iNum1 As Long
  Dim iNum2 As Long
  Dim iLines As Long
  Dim iInt As Long
  Dim iSplit() As Long
  Dim iLastSpace As Long
  
  Dim i As Long
  Dim sText As String
  
  Set wsh = ThisWorkbook.Worksheets(wshName)
  Set rng1 = wsh.Range(rngStart)
  
  Do
    iNum1 = CLng(rng1.Value)
    iNum2 = CLng(rng1.Offset(1, 0).Value)
    sText = Trim(rng1.Offset(0, 2).Value)
    
    iLines = Len(sText) \ 15 - 1
    iInt = Application.WorksheetFunction.Ceiling((iNum2 - iNum1) / (iLines + 1), 1)
    ReDim iSplit(1 To iLines + 1)
    
    iLastSpace = 15
    
    rng1.Offset(1, 0).Resize(iLines, 1).EntireRow.Insert xlShiftDown
    
    For i = 1 To iLines
      iSplit(i) = InStr(iLastSpace, sText & " ", " ", vbTextCompare)
      iLastSpace = iSplit(i) + 15
    Next i
    
    iSplit(i) = Len(sText) + 1
    
    rng1.Offset(0, 2).Value = Left(sText, iSplit(1))
    
    For i = 1 To iLines
      rng1.Offset(i, 0).Value = Format(iNum1 + iInt * i, "00000")
      rng1.Offset(i, 2).Value = Mid(sText, iSplit(i), iSplit(i + 1) - iSplit(i))
    Next i
    
    Set rng1 = rng1.Offset(iLines + 1, 0)
  Loop Until Len(rng1.Offset(1, 0).Value) = 0
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,132,670
Messages
5,654,649
Members
418,146
Latest member
Shnn028

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