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

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

CodePudding user response：

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

Private Sub ThisRegex()
    Dim regEx As New RegExp
    Dim input As String
    Dim ouput As String
    
   input = "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"
        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = "\d{1}\)"
        End With
        ouput = regEx.Replace(input, "\r"))
End Sub

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.

CodePudding user response：

If there's only three possible values and you wanted to use SSRS expressions you could use the Replace function to replace "5)" with vbcrlf which would insert a new line.

=REPLACE(Fields!LongString.Value, "5)", vbcrlf)