Monday, December 2, 2013

Padding Strings

As mentioned in the post regarding ZIP Codes, sometimes I need to add characters at the beginning or end of a character string.  It's quite easy in Excel to use the REPT function:
=REPT("xo",5) & " Ma cherie" sends your true love hugs and kisses

The String function is fine for repeating the same character, but sometimes the need is to repeat the same string multiple times.  Will Robinson knew this as the "DANGER! DANGER! " function
Ucase(String(2, "DANGER! ") & "Will Robinson!") makes the robot stutter, "D D WILL ROBINSON!"

For those cases when you want to pad the front, back, or both sides with a character string you can use the following:
Public Function Pad(StartWith As String, _
                `              Pad_String As String, _
                              Optional Pad_Repeat As Double = 1, _
                              Optional SpacePad As Boolean = TRUE, _
                              Optional FrontBackBoth As String = "BOTH") As String
' StartWith is the basic string to have characters added.  In the above, "Will Robinson"
' Pad_String is what will be added, i.e. "Danger!" in the above
' Pad_Repeat determines how many times Pad_String will occur.
' Space_Pad determines whether a space is used between each Pad_String and eliminates the need to make certain there is a space at the end of each field or included in a concatenated string
'   * TRUE (the default) would result in "DANGER! DANGER! " 
'   * FALSE would result in "Danger!Danger!"
' FrontBackBoth controls whether Pad_String will be added to the left of StartWith(Front), the right (Back), or surrounding (Both).  
    Dim i As Double
    FrontBackBoth = Trim(UCase(FrontBackBoth))
    Pad_Repeat = Round(Pad_Repeat, 0)
    If (FrontBackBoth <> "BOTH" And FrontBackBoth <> "FRONT" And FrontBackBoth <> "BACK") _
        Or Pad_Repeat < 0 Then
        Pad = "#ERROR"
        Exit Function
    End If
    Pad = Trim(StartWith)
    If FrontBackBoth = "BOTH" Or FrontBackBoth = "FRONT" Then
        For j = 1 To Pad_Repeat
            If SpacePad Then Pad = " " & Pad
            Pad = Pad_String & Pad
        Next j
    End If
    If FrontBackBoth = "BOTH" Or FrontBackBoth = "BACK" Then
        For j = 1 To Pad_Repeat
            If SpacePad Then Pad = Pad & " "
            Pad = Pad & Pad_String
        Next j
    End If
End Function

To perform the same thing, with the exception of the optional no-space between the element, using an Excel formula it looks like the following
&IF(OR(UPPER(TRIM(D24))="BOTH",UPPER(TRIM(D24)) ="BACK"),REPT( " "&TRIM(B24),ROUND(C24,0)),"")

This assumes, referring to the variables used in the VBA Function
  • Column A: StartWith ("Will Robinson")
  • Column B: Pad_With ("Danger!)
  • Column C: Pad_Repeat
  • Column D: FrontBackBoth