SSRS expression: How to put a line break after a special character for an address that is in a table column

Question

I have a table in SSRS that has multiple addresses, they are all difficult to read and I was wondering if there was a way a line break could be added.

All the addresses start with a 2) or 3) or 1) so a closed bracket would be the marker where the line breaks should occur, removing the ).

Current format; 5)Passenger Lifts - Insurance - The High St 5-16- due - 28 Jan 22, 5)Passenger Lifts - Insurance - 6 Lovedale Road Flats- due - 09 Sep 21, 5)Passenger Lifts - Insurance - Queens Court 1 - 31 BLOCK- due - 14 Jan 22

Required format;

Passenger Lifts - Insurance - The High St 5-16- due - 28 Jan 22,
Passenger Lifts - Insurance - 6 Lovedale Road Flats- due - 09 Sep 21,
Passenger Lifts - Insurance - Queens Court 1 - 31 BLOCK- due - 14 Jan 22

Any tips and tricks appreciated

Answer 1

I didn't test it in VBA, but it functions in Regex101 and is something like this through Regex Replace:

EDIT on 02/04/2022: I managed to get a VBA editor, and put things more DRY and SOLID...

Public Sub TestReplaceThroughRegex()
  Dim strOutput As String
  strOutput = _
    ReplaceThroughRegex( _
        "5)Passenger Lifts - Insurance - The High St 5-16- due - 28 Jan 22, 5)Passenger Lifts - Insurance - 6 Lovedale Road Flats- due - 09 Sep 21, 5)Passenger Lifts - Insurance - Queens Court 1 - 31 BLOCK- due - 14 Jan 22" _
        , "\d{1}\)" _
        , "\r" _
    )
    Debug.Print (strOutput)
End Sub

Public Function ReplaceThroughRegex(ByVal strInput As String, ByVal pattern As String, ByVal replacement As String) As String
Static regEx As Object 'VBScript_RegExp_55.RegExp
    'Static declaration means we don't have to create
    'and compile the RegExp object every single time
    'the function is called.

  If strInput = "" Then
    'This is the signal to dispose of oRE
    Set regEx = Nothing
    Exit Function 'with default value
  End If
   
  'Create the RegExp object if necessary
  If regEx Is Nothing Then
    Set regEx = CreateObject("VBScript.Regexp")
  End If
    With regEx
        .Global = True
        .Multiline = True
        .IgnoreCase = False
        .pattern = pattern
    End With
    ReplaceThroughRegex = regEx.Replace(strInput, replacement)
End Function

PS: I'm assuming that you will have only 1 digit before ). If more than one is possible, change for "\d{1,2}\)" or "\d{1,3}\)", whatever suits your needs.