Carriage return after last comma

Hi all,

I need to print out labels for addresses which where taken from a database. As the labels are generated, the address comes out in one line and does not fit within the label. The format of the address is: Name Surname, House/Appartment Number, City, Postcode. I would like the postcode to go to another line. I could do it manually, but at the moment I have about 2000 addresses, and there's twice as much coming.

Is there a way to do this in VBA?

Thank you for your help!

sessionone

It would probably make more sense to merge your data into the label format having configured the labels to format the fields as you require them, however if the address is all on one line you don't need a macro. Use Replace (CTRL+H) to replace
,[space]
with
^p
where [space] is a space character, to put each part of the address on a new line.
To put only the part from the last comma on a new line is a bit more complicated, and without seeing the labels, unpredictable in terms of formatting, but

Code:

---

Sub FixLabels()
Dim oTable As Table
Dim ocell As Cell
Dim oRng As Range
Dim vAddr As Variant
Dim sAddr As String
Dim i As Long, j As Long
    For Each oTable In ActiveDocument.Tables

        For Each ocell In oTable.Range.Cells
            sAddr = ""
            Set oRng = ocell.Range
            oRng.End = oRng.End - 1
            If InStr(1, oRng.Text, ",") Then
                vAddr = Split(oRng.Text, ",")
                j = UBound(vAddr)
                For i = 0 To UBound(vAddr) - 1
                    sAddr = sAddr & CStr(vAddr(i) & ",")
                Next i
                oRng.Text = sAddr & vbCr & LTrim(vAddr(j))
                oRng.ParagraphFormat.Reset
            End If
        Next ocell
    Next oTable
lbl_Exit:
    Set oTable = Nothing
    Set ocell = Nothing
    Set oRng = Nothing
    Exit Sub
End Sub

---

should be close, and you can always undo the results if not.

Hi,

Thanks! Your first suggestion worked!

Thanks again.

sessionone