Function: Join Cells with Format

  • Thread starter Thread starter Alexey E. Kolmyk
  • Start date Start date
A

Alexey E. Kolmyk

Hi,

Could you help me?
How to join cells with different fonts?
For example, I have:
A1 = "2" (Normal)
B1 = "10" (Superscript)
C1 = "=1024" (Normal)
and I want to see 2^10=1024 (where "^10" - is "10" in
superscript). So, can I write:
D1 = SomeFuncJointWithFormats(A1, B1, C1)?

Thank you for help,
Alexey E. Kolmyk
 
A worksheet function can only return a value - it can't do
formatting. You could instead use an event macro: put this in the
worksheet code module (right-click on the worksheet tab and choose
View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Range("A1:C1"), Target) Is Nothing Then
With Range("A1:D1")
Application.EnableEvents = False
.Item(4).Value = .Item(1) & .Item(2) & .Item(3)
.Item(4).Characters(Len(.Item(1)) + 1, _
Len(.Item(2))).Font.Superscript = True
Application.EnableEvents = True
End With
End If
End Sub
 
Hi,

for me it only works when the value in D4 is text.
I changed it as follows:

........
.Item(4).Value = .Item(1) & .Item(2) & "="&.Item(3)
........
which formats it automatically to text

Best regards
Wolf
 
Since the OP wrote

C1 = "=1024"

indicating that C1 *was* text, your solution would produce an extra
= sign.

There are, of course, a variety of ways to use conditionals to make
this more flexible. Both your solution and mine fail if LEN(B1)=0.
 
Back
Top