Combining a string of text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tracktor - 22 May 2008 13:20 GMT

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?

Reply to this Message

Bernard Liengme - 22 May 2008 13:35 GMT

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

Signature

Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

>I want to combine a string of text from several cells. I can do this
>however
[quoted text clipped - 9 lines]
> but
> can't get the right fonts and styles. Is there a way to make this happen?

Reply to this Message

Gary''s Student - 22 May 2008 13:42 GMT

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.

Signature

Gary''s Student - gsnu200787

> 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
[quoted text clipped - 5 lines]
> 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?

Reply to this Message

Mike H - 22 May 2008 14:22 GMT

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

Reply to this Message