Excel: Different formatting on different values in same cell

Question

I have the following formula in excel:

=TEXTO(D39;"#.##0")&" "&"("&TEXTO(D40;"0%")&")"

This formula returns an output with a size in the cell -- left number in this picture:

I need to change the formatting of the percentage portion of the formula's output in the cell (see the example to the right).

How can I do it?

I know how to do this when I have no formulas (just numbers or text), I tried to follow the same procedure to this case, but it does not work.

Answer 1

No, there is no way to do this with a formula and if you have formulas in the cell. Can you have different sized fonts in a cell? Yes. Can this be done any other way? Yes, mostly involves VBA but that is out of the scope of this question.

The thing is that Excel Formulas return a Value, which ever be it's nature, and not formating. Formatting is applied to the data, in the cell, so it suits the representation you want, like for date formatting. Dates are stored serialised in Excel and usually they are represented by the locale date format.

For an VBA aproach you will have to parse the cell data using a function like InSTR() to split the numeric value from the percentage value. Then you would apply the format you want to either part and return it to the cell.

Cheers.

EDIT:

As you can see down here there is another way that involves counting the characters and applying the desired format. I've recorded a small macro for you to use as a template.

Sub Macro1()
'
' Macro1 Macro
'

'
Range("H13").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Dude(11,3%)"
Range("H13").Select
ActiveCell.FormulaR1C1 = "Dude(11,3%)"

With ActiveCell.Characters(Start:=1, Length:=13).Font
    .Name = "Tahoma"
    .FontStyle = "Normal"
    .Size = 8
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
End With

With ActiveCell.Characters(Start:=14, Length:=7).Font
    .Name = "Tahoma"
    .FontStyle = "Normal"
    .Size = 20
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
End With
Range("I13").Select
End Sub