Results 1 to 3 of 3

Thread: Carriage return after last comma

  1. #1

    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!


  2. #2
    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
    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))
                End If
            Next ocell
        Next oTable
        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.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes

  3. #3

    Thanks! Your first suggestion worked!

    Thanks again.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts