Combining a string of text

  • Thread starter Thread starter tracktor
  • Start date Start date
T

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?
 
You will not be able to do this with a simple formula
And the VBA solution might not be that easy, either
best wishes
 
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.
 
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
 
Back
Top