format characters within formula

[rline101]

I know you can format a cell to be superscript. However, I need to be able to format part of a cell; ie within a formula, so that only the bit I want is formatted superscript.

ie. A1 is happy
B1 is day
C1 is =A1&B1

Even if I format cell B1 to be superscript, when it's transferred into C1, the superscript is gone. Can I make just the word day in cell C1 to be superscript?

Thanks.

 

[Scott Huish]

Not in a formula, it will take VBA to do that, but then the formula will be gone.

Sub test()
Range("C1") = Range("A1") & " " & Range("B1")
Range("C1").Characters(Len(Range("A1")) + 2, Len(Range("B1"))).Font.Superscript = True
End Sub

 

[rline101]

OK. I did a little googling and found three sites which say it's easy to do this. The sites are
http://jldexcel.blogspot.com/2006/06/formatting-part-of-cell-content-in-ms.html

http://www.windowsdevcenter.com/pub/a/windows/2005/01/11/xcel_annoy.html

http://www.jegsworks.com/Lessons/numbers/format/step-partial-autoformat.htm

Anyway, I follow the instructions and when I change the formatting I can see my changes take place, but as soon as I click the tick, or hit enter, the formatting disappears.

Can anyone explain why my formatting appears and then disappears as soon as I click out of the cell. The first link is the easiest to implement.

Thanks. This is driving me crazy, particularly with these sites saying it's easy.

 

[Scott Huish]

None of those examples use formulas, as I said before you can not format part of a cell with a formula. You can have a formula to get a result, do a copy and Paste Special Values, and then manually format the result, but then the formula will be gone.

 

[rline101]

Right. I guess what didn't make sense was why I couldn't even replicate on my excel what was done on those examples. No formulas involved...

 

[Scott Huish]

So are you good to go?