excel concatenate text with newline character and changing format of few lines

Question

I have excel sheet as follow

cell a1 has text- 1234
cell d1 has text- abc.com
cell f1 has text- AZ, USA 85663

I created a formula in cell i1 as =CONCATENATE("Gaaa Reference No. I-20-",A1,CHAR(10),D1,CHAR(10),F1) Then used steps in this link to concatenate three columns with newline character in between lines.

I want the first line to be bold, second line in italics. The output should be times new roman font. I tried changing formatting of columns a and d, but it didnt help

1. How could I change the formatting? The current output is as below

2. I have an excel sheet with multiple rows populated. I would like to have same format for the entire column I

3. It seems that this requires VBA code. Please provide that

Answer 1

Insert a new code module in VBA and use the following code...

Option Explicit

Sub FormatConcatColumn()

    Dim i&, rows&, LF, v1, v2, v3, vOut, r As Range

    rows = 60  '<-- change to 200 or however many rows you need

    ReDim vOut(1 To rows, 1 To 1)
    ReDim LF(1 To rows, 1 To 2)

    With [a1].Resize(rows)

        v1 = .Value2
        v2 = .Offset(, 3).Value2
        v3 = .Offset(, 5).Value2

        For i = 1 To rows
            vOut(i, 1) = v1(i, 1) & vbLf & v2(i, 1) & vbLf & v3(i, 1)
            LF(i, 1) = Len(v1(i, 1))
            LF(i, 2) = LF(i, 1) + Len(v2(i, 1))
        Next

        With .Offset(, 8)
            .Clear
            .Value2 = vOut
            .Font.Name = "Times New Roman"

            i = 0
            For Each r In .Cells
                i = i + 1
                r.Characters(1, LF(i, 1)).Font.FontStyle = "Bold"
                r.Characters(LF(i, 1) + 1, LF(i, 2) - 2).Font.FontStyle = "Italic"
                DoEvents
            Next
        End With

    End With

End Sub