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:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,855
Messages
5,833,998
Members
430,250
Latest member
Reggie Mcqueen

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