Two Fonts in One Cell

[TFCJamieFay]

Hi all,

I'm trying to use two fonts in one cell. I'm using a formula that concatenates the values from two cells for my data labels in a waterfall chart. One cell has the label and should be formatted as Calibri, size 11 and the second cell has either a "P" or "Q" formatted as Webdings3. Is it possible to concatenate these two cells and use their original fonts?

Many thanks,

Jay

 

[JackDanIce]

Don't think you can do this

 

[Peter_SSs]

Don't think you can do this

Correct. If the cell contains a formula, you cannot use two different fonts.

 

[TFCJamieFay]

Not really the answer I was looking for! Thanks anyway

 

[JackDanIce]

What answer were you looking for?

Yes it is possible to concatenate the values of the two cells, but you can't retain the formatting of the cells when you concatenate them. Concatenating only works on cell values

 

[mirabeau]

I don't know about formulas, but you can do it with VBA

 

[diddi]

if you are desperate, you could use a label with the webdings chars and sit it next to your text. not a solution, but a possible workaround.

@mirabeau how does that work (VBA)

 

[mirabeau]

if you are desperate, you could use a label with the webdings chars and sit it next to your text. not a solution, but a possible workaround.

@mirabeau how does that work (VBA)

diddi,

Actually, you'd have little trouble figuring it out yourself by using the formula bar and the macro recorder.

However here's a couple of readymade codes.
The first enters differently formatted data in a couple of cells.
The second code combines them in a single cell.

Sub testdatacode()
Range("A1:A5").Clear
With Range("A1")
    .Value = "Abcde"
    .Font.Name = "calabri"
    .Font.Size = 11
    .Font.FontStyle = "italic"
End With
With Range("A2")
    .Value = "PQ"
    .Font.Name = "webdings"
    .Font.Size = 20
End With
End Sub

Sub concatenatecode()
Dim Val1 As String, Val2 As String
Dim Fname1 As String, Fname2 As String
Dim Fsize1 As Long, Fsize2 As Long
Dim Lng1 As Long, Lng2 As Long
With Range("A1")
    Val1 = .Value
    Fname1 = .Font.Name
    Fsize1 = .Font.Size
    Lng1 = Len(.Value)
    Fstyle1 = .Font.FontStyle
End With
With Range("A2")
    Val2 = .Value
    Fname2 = .Font.Name
    Fsize2 = .Font.Size
    Lng2 = Len(.Value)
End With
With Range("A5")
    .Value = Val1 & Val2
    .Characters(1, Lng1).Font.Name = Fname1
    .Characters(1, Lng1).Font.Size = Fsize1
    .Characters(1, Lng1).Font.FontStyle = Fstyle1
    .Characters(Lng1 + 1, Lng2).Font.Name = Fname2
    .Characters(Lng1 + 1, Lng2).Font.Size = Fsize2
End With
End Sub

 

[diddi]

nice trick.

 

[Armando Montes]

Please check this example:
http://vbadud.blogspot.com/2007/09/excel-vba-change-font-color-for-part-of.html