Creating a block of string that centers itself with spaces

[bradyboyy88]

I am trying to figure out some way to take a string and make sure its a certain number of characters (I would say width but that would be much more involved given characters and point sizes are so confusing!!!) and center it via spaces. For instance, if I have a string "cat" but I want it to have 7 characters then I have " Cat ". Does the format function have way of doing this?

 

[bradyboyy88]

Anyone have an idea how to make this concept work?

 

[bradyboyy88]

For anyone looking to do this here is the final code:

Function CenterTextImproved(arr As Variant) As String
    
    'FIRST COMPONENT OF ARRAY HAS THE STRING AND SECOND COMPONENT HAS THE MAX STRING LENGTH FOR COLUMN
    Dim e, f As Long
    Dim L, R
    Dim CenterText As String
    Dim RowsComplete As Boolean
    
    Do While Not RowsComplete
        For e = LBound(arr, 1) To UBound(arr, 1)
            If Len(arr(e, 1)) < arr(e, 2) Then
                L = Int((arr(e, 2) - Len(arr(e, 1))) / 2)
                R = arr(e, 2) - (L + Len(arr(e, 1))) '<- correction
                CenterText = CenterText & Space(L) & arr(e, 1) & Space(R)
                arr(e, 1) = ""
            ElseIf Len(arr(e, 1)) > arr(e, 2) Then
                CenterText = CenterText & Left(arr(e, 1), arr(e, 2))
                arr(e, 1) = Right(arr(e, 1), Len(arr(e, 1)) - arr(e, 2))
            Else
                CenterText = CenterText & arr(e, 1)
                arr(e, 1) = ""
            End If
            If e = UBound(arr, 1) Then
                For f = LBound(arr, 1) To UBound(arr, 1) - 1
                    If arr(f, 1) <> "" Then
                        Exit For
                    Else
                        RowsComplete = True
                    End If
                Next f
                CenterText = CenterText & vbNewLine
            End If
        Next e
    Loop
    CenterTextImproved = CenterText
    
End Function