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

karlswhim

New Member
Joined
Jul 31, 2010
Messages
2
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.
 
Last edited:

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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

New Member
Joined
Jul 31, 2010
Messages
2
Totally great, Peter.

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

Thank you.
 

skyport

Active Member
Joined
Aug 3, 2014
Messages
374
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,426
Messages
5,601,595
Members
414,462
Latest member
StageRiis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top