Subtitle Formatting Help

[HelloEx]

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

 

[iliace]

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]

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]

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.

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

 

[HelloEx]

Thanks a lot. Will try it out.