Multiple font sizes in one cell

[merlin_the_magician]

Riddle me this.... riddle me that...

I have a formula in cell J10 that looks like this:
=TEKST.SAMENVOEGEN("Depot ";M37;" ";M46;" ";M47)

note: i don't know the exact english call for "Tekst.samenvoegen" but it means something like " join text"

Works fine, but i want something more. Is there any way i can change the font size of the M46;" "; M47 part? If possible, i would like this part of the text to be displayed in a smaller font.

 

[Barry Katcher]

Morning, Merl

As long as it's text, not a formula, just highlight the section of text up in the formula bar, and use the drop down font selector to set the font size for the selection. You can also format the selection any other way you would normally format a cell, i.e. bold, italic, etc. Well, o.k., you can't format it to etc.

 

[merlin_the_magician]

Ya.... i know that.
Problem is, it is not text, as you can see. I cannot just fix it into two cells, since M37 can vary in length. If i do fix it in multiple cells, the M37 value will overwrite other cells.

 

[Mudface]

I don't think you'll be able to do this via a worksheet function, as they only return values not formatting. The following code should work OK, however it will need to turn the cell value into text, overwriting the formulas. In summary, I don't think there's a really acceptable way of doing this-

Sub ChangeFontSize()
Dim intPosition As Integer

' Find position of first space after "Depot "
intPosition = InStr(7, ActiveCell.Value, " ")

' If not found then exit
If Not intPosition Then Exit Sub

' Convert formula to text
ActiveCell.Value = ActiveCell.Text

' Make all text after "Depot " into font size 8
ActiveCell.Characters(Start:=intPosition, _
Length:=Len(ActiveCell.Value) - intPosition + 1).Font.Size = 8

End Sub

 

[StephenPratt]

MudFace,

this is going a while back. Your code works if I delete the 'If not found bit', otherwise it seems to loop back to the start, even when I have the character it's looking for in the cell. Are you able to help explain how to avoid this.
Thanks.

 

[István Hirsch]

I am afraid you can not do this as long as there is a formula involved, but there can be a workaround if you copy/paste the cells to have different formats into another column as value and perform formatting there, see this video.