MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Begginer's question

Posted by Marcelo on December 26, 2000 7:54 PM

Cells A1 and B1 contains, respectevily, bold and italics text.

How can I concatenate them, in cell C1, preserving bold and italics format?

Thans for your help!

Posted by Dave on December 26, 2000 9:11 PM

Hi Marcelo

You would need a macro like below to do this. To use this one select the 2 cells to join and it will concatenate them with Bold and italic formatting in the cell to the right of the selection.

To place the code in push Alt+F11 then go to Insert>Module and paste in the code. Push Al+Q and then Alt+F8 select "MyCopy" and click "Options" and assign a shortcut key.

Sub MyCopy()
Dim T1 As Integer, T2 As Integer

With Selection.Cells(1, Selection.Columns.Count + 1)
.Value = Selection.Cells(1, 1) & " " & Selection.Cells(1, 2)
T1 = Application.WorksheetFunction.Find(" ", .Value) - 1
T2 = Len(.Value) - (T1 + 2)
.Characters(Start:=1, Length:=T1).Font.FontStyle = "Bold"
.Characters(Start:=T1 + 1, Length:=T2).Font.FontStyle = "Italic"
End With

End Sub

You can also do this manually by typing =A1&" " & B1 in cell C1. Then highlight the first word in the formula bar and apply the formatting required, then do the same for the second word.

Good luck


OzGrid Business Applications

Posted by Celia on December 27, 2000 12:00 AM

I'm a bit hesitant to ask this in case you get upset, but here goes.
Regarding your suggestion to do it manually. Won't the formula =A1&" "&B1 appear in the formula bar rather than the two words?

Posted by Dave on December 27, 2000 12:39 AM

Yes celia it will. I forgot to mention that you will need to copy and PasteSpecial as Values then highlight the words in the formula bar.


OzGrid Business Applications

Posted by Marcelo on December 27, 2000 1:11 PM

Dave, Celia! Thanks a lot for your ready answers!!

I have placed the code as you suggested and attributed ctrl+p as a shotrcut key.

The problem is that when I select both cells and press ctrl+p, the result in the next cell does not displays the bold and italics formatting. It just concatenates the words in the previous cells.

By the way, how can I use this macro to allow all cells in column C to be the concatenation of the cells in column A, with bold formatting, with the cells in column C, with italics formatting?

Thanks again!


Posted by Tim Francis-Wright on December 27, 2000 2:27 PM

The following macro will do the following:
If you highlight a 2-column range, it will
copy into the third column the first column,
then a space, then the second column. It will
preserve bold and italics from either range.
(You can also preserve .Superscript, .Subscript,
.Underline, and .Strikethrough properties by
amending the code in the proper places).

I assumed that you would never have a Chr(1)
character (Alt-001) in the range; the previous
examples searched for spaces, which would have
given wrong answers if you had spaces in the
data in column A.

Sub MyCopy()
Dim T1 As Integer, T2 As Integer, c As Range, r As Range
Dim FS1, FS2

For Each c In Selection.Columns(1).Cells
Set r = Intersect(c.EntireRow, Selection)

With r.Cells(1, Selection.Columns.Count + 1)
.Value = r.Cells(1, 1) & Chr(1) & r.Cells(1, 2)
FS1 = r.Cells(1, 1).Font.FontStyle
FS2 = r.Cells(1, 2).Font.FontStyle
T1 = Application.WorksheetFunction.Find(Chr(1), .Value) - 1
T2 = Len(.Value) - (T1 + 0)
.Value = Application.WorksheetFunction.Replace(.Value, T1 + 1, 1, " ")
.Characters(Start:=1, Length:=T1).Font.FontStyle = FS1
.Characters(Start:=T1 + 1, Length:=T2).Font.FontStyle = FS2
End With
Next c

End Sub

Posted by Marcelo on December 27, 2000 8:15 PM

Now, everything is working fine!

All of you helped me a lot and I'm surprised with the efficiency of this page.

Thank you very much, folks!