Can you retain different text formats when merging text?

G

Genmon

Is it possible to merge text retaing the formatting of the different cells.
(eg. bold text is in one and italics is in another) I'm using Excel 2003
Any help would be appreciated.

bill
 
B

Bernie Deitrick

Bill,

You could use a macro to do that, but in general, no, you can't. See the
macro examples below, which will combine cells A1:A100 with cells B1:B100
into cells C1:C100 when you run "test". Note that the macro will only
combine font, size, color, bold, italic, and underline.

HTH,
Bernie
MS Excel MVP

Sub test()
CombineFormats Range("A1:A100"), Range("B1:B100"), _
Range("C1:C100")
End Sub

Sub CombineFormats(inCell1 As Range, _
inCell2 As Range, OutCell As Range)
Dim i As Integer
Dim j As Integer
Dim k As Integer
For i = 1 To OutCell.Cells.Count
OutCell(i).Value = inCell1(i).Value & " " & inCell2(i).Value
For j = 1 To Len(inCell1(i).Value)
With OutCell(i).Characters _
(Start:=j, Length:=1).Font
.Name = inCell1(i).Characters _
(Start:=j, Length:=1).Font.Name
.FontStyle = inCell1(i).Characters _
(Start:=j, Length:=1).Font.FontStyle
.Size = inCell1(i).Characters _
(Start:=j, Length:=1).Font.Size
.Underline = inCell1(i).Characters _
(Start:=j, Length:=1).Font.Underline
.ColorIndex = inCell1(i).Characters _
(Start:=j, Length:=1).Font.ColorIndex
End With
Next j
For j = 1 To Len(inCell2(i).Value)
k = Len(inCell1(i).Value) + 1 + j
With OutCell(i).Characters _
(Start:=k, Length:=1).Font
.Name = inCell2(i).Characters _
(Start:=j, Length:=1).Font.Name
.FontStyle = inCell2(i).Characters _
(Start:=j, Length:=1).Font.FontStyle
.Size = inCell2(i).Characters _
(Start:=j, Length:=1).Font.Size
.Underline = inCell2(i).Characters _
(Start:=j, Length:=1).Font.Underline
.ColorIndex = inCell2(i).Characters _
(Start:=j, Length:=1).Font.ColorIndex
End With
Next j
Next i
End Sub
 
Top