Concatenate 3 columns and retain bold format of contents of 1 column

[karlswhim]

Does anyone know a VBA code that can concatenate contents of 3 columns with text cells while retaining the bold format of the contents of 1 column.

I have a Range A1:A5 containing text of titles that are in Bold format and I wand to combine it with text of different last names in Range C1:C5. I use Range B1:B5 as separator containing " ".

I do it manually using the concatenate function to output in D1 using =concatenate(A1,B1,C1)

But I lose the formatting so I have to do edit each output cell and highlight and format the text to Bold the titles again:

Output
A B C D
Mr. Harper Mr. Harper
Mrs. Moran Mrs. Moran
Ms. Jensen Ms. Jensen
Mr. Gilbert Mr. Gilbert
Mrs. Smith Mrs. Smith



I do this for about 250 rows

I do not know making macros. I hope someone can help.

 

[VoG]

Try

Sub Concat()
Dim i As Long, LR As Long, j As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("D" & i)
        .Value = Join(Application.Transpose(Application.Transpose(.Offset(, -3).Resize(, 3))), vbNullString)
        j = InStr(.Value, ".")
        .Characters(Start:=1, Length:=j).Font.Bold = True
    End With
Next i
End Sub

 

[karlswhim]

Totally great, Peter.

It works awesome. You've saved me a lot of time.

Thank you.

 

[skyport]

Hi Peter, I saw this solution you gave and it worked great for me as well however, the only problem was something in the program appears to limit the number of characters that can end up in column D to around 255 instead of the 32k+ excel capacity. Is there a way to get the program to do that?