for a true guru... formatting with concatenation
Posted by Stephanie on July 31, 2001 3:59 PM
Is there any way to have different formatting on various parts of a concatenation? For example, I want to reference a date in the middle of some text:
="Today's lucky number is " & A1 & " and blah blah"
I want part of that text (a reference to a footnote, actually) to be superscripted, but not the whole cell. It's easy to just highlight part of the text if the cell has only text, but in this case the text is part of a formula... This is driving me nuts!
Posted by gregc on July 31, 2001 8:01 PM
I know how to do dates, but not superscripted
example: ="Today's lucky number is " & text(A1,"mm/dd/yy") & " and blah blah"
that will give you a date in the middle of the concatenation.
Posted by Stephanie on August 01, 2001 8:35 AM
this is really bugging me! i don't want to have to paste as values...
yes, that is what i have right now!
Posted by Damon Ostrander on August 01, 2001 1:27 PM
Hi Stephanie,
I don't think there is a way to have a formula format its own results in Excel. Assuming that is the case, here is a little VBA that does the same thing as the formula and it superscripts the date:
Sub SuperscriptDate()
With [C8]
.Value = "Today's lucky number is" & Format([A1], "mm/dd/yy") & " blah blah"
.Characters(Start:=21, Length:=8).Font.Superscript = True
End With
End Sub
For the sake of this example I used cell C8 as the destination of the string.
You could have it automatically call this macro whenever cell A1 updates by calling this macro from the Worksheet_Change event, and testing the Target argument to see if it is cell A1.
Damon
Posted by Stephanie on August 01, 2001 5:53 PM
that's terrific, thanks!
works like a charm
