Combining a string of text

[tracktor]

I want to combine a string of text from several cells. I can do this however
the format of the text needs to be different. Example. I have "New Loan
Application" in cell A1 and it is in Calisto MT size 9 font. In cell B1 I
have "to be submited in" also in Calisto MT size 9 font. Then in cell C1 i
have the number "5" in Arial, Bold, underlined and in size 10 font. Then I
have "business days" in cell D1. I want to combine all 4 cells into one cell
with all of their respective font sizes, styles and characteristics. ie bold,
underlined, or italicized. I have been playing around with: =A1&B1&C1&D1 but
can't get the right fonts and styles. Is there a way to make this happen?

 

[Bernard Liengme]

You will not be able to do this with a simple formula
And the VBA solution might not be that easy, either
best wishes

 

[Gary''s Student]

Very easy.

Once you have entered the formula to combine the strings, copy the cell and
paste/special/value back onto the cell. Then hilight the areas in the
formula bar and apply the formats.

 

[Mike H]

or with a macro. Change the fonts ands styles to suit your needs

Sub stantial()
Range("E1").FormulaR1C1 = "=RC[-4]&"" ""& RC[-3]&"" ""&RC[-2]&"" ""&RC[-1]"
Range("E1").Select
ActiveCell.Copy
Selection.PasteSpecial Paste:=xlPasteValues

With ActiveCell.Characters(Start:=1, Length:=38).Font
.Name = "Calisto MT"
.Size = 12
End With

With ActiveCell.Characters(Start:=40, Length:=1).Font
.Name = "Arial"
.Bold = True
.Size = 6
.Underline = xlUnderlineStyleSingle
End With

With ActiveCell.Characters(Start:=41, Length:=14).Font
.Name = "Book Antigua"
.Bold = True
.Size = 16
End With
End Sub


Mike