Microsoft Office Forums - View Single Post

gmayor · 09-20-2014, 10:02 PM

You can certainly reformat the text using a macro (which is probably the easiest approach) but learning vba programming is not going to take five minutes.

If you select the text and run the following macro it will be formatted as your example:

Code:

Sub Example()
Dim oRng As Range
Dim strText As String
Dim vText As Variant
    'set the selected text as a range
    Set oRng = Selection.Range
    'assign a text string to the range
    strText = oRng.Text
    'remove the paragraph breaks from the text string
    strText = Replace(strText, Chr(13), Chr(32))
    'split at the default character which is space
    vText = Split(strText)
    'Reassemble the string in the required format
    oRng.Text = vText(0) & Chr(32) & _
                vText(1) & Chr(32) & _
                vText(2) & Chr(32) & _
                "(Billed $" & vText(3) & _
                " Reduced $" & vText(4) & _
                ") " & vText(5) & Chr(32) & vText(6) & Chr(32) & _
                vText(7) & Chr(32) & vText(8)
End Sub