Carriage return after last comma

[sessionone]

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

[gmayor]

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

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.

[sessionone]

Hi,

Thanks! Your first suggestion worked!

Thanks again.

sessionone