Can I Concatenate two cells and keep superscript?

[jeebs83]

Cell A1 = 10
Cell A2 = 3 (superscripted)

=CONCATENATE(A1,A2) renders 103, which is not what I'm looking for.

 

[schielrn]

You would need VBA to do this. It would be something similar to what I posted yesterday about combining bold cells:

http://www.mrexcel.com/forum/showthread.php?t=488659

If you need help with the code post back, but this would be the only way I would know of doing this unfortunately.

 

[jeebs83]

I am clueless when it comes to VBA, so yes, I would need to know what code is required and how to insert the code.

Thanks.

 

[schielrn]

I am leaving for the day now, but I will try to check from home tonight if I can, but someone else hopefully can come along and assist you.

This is a very quick try at it:

Sub test()
Range("A3").ClearContents
Range("A3").Value = Range("A1").Value & Range("A2").Value
For i = 1 To Len(Range("A1").Value)
    If Range("A1").Characters(i, 1).Font.Bold = True Then Range("A3").Characters(i, 1).Font.superscript = True
Next i
For j = 1 To Len(Range("A2").Value)
If Range("A2").Characters(j, 1).Font.superscript = True Then Range("A3").Characters(j + Len(Range("A1").Value), 1).Font.superscript = True
Next j
End Sub

Hope that helps, just put it in a standard module.

Make sure you make a backup of the workbook.

 

[schielrn]

Actually here is the code that will be used, but the cell needs to be formatted as text because if it is just numbers it keeps reverting back to just a general format with no superscript:

Sub test()
Range("A3").ClearContents
Range("A3").Value = Range("A1").Value & Range("A2").Value
For i = 1 To Len(Range("A1").Value)
    If Range("A1").Characters(i, 1).Font.Superscript = True Then Range("A3").Characters(i, 1).Font.Superscript = True
Next i
For j = 1 To Len(Range("A2").Value)
If Range("A2").Characters(j, 1).Font.Superscript = True Then Range("A3").Characters(j + Len(Range("A1").Value), 1).Font.Superscript = True
Next j
End Sub

Hope that helps and post back with any questions or comments.

 

[RoryA]

If you specifically want the cubed symbol, you can add that to A2 as a symbol (Insert-Symbol...) and then concatenate. Or use a custom number format.

 

[tejaswiggowda]

worked for me, thanks
but can i now how to extend the VBA option to other cells like, B, C, D and all the cells i need as you have given for a1, a2, a3

 

[tejaswiggowda]

Actually here is the code that will be used, but the cell needs to be formatted as text because if it is just numbers it keeps reverting back to just a general format with no superscript:

Sub test()
Range("A3").ClearContents
Range("A3").Value = Range("A1").Value & Range("A2").Value
For i = 1 To Len(Range("A1").Value)
    If Range("A1").Characters(i, 1).Font.Superscript = True Then Range("A3").Characters(i, 1).Font.Superscript = True
Next i
For j = 1 To Len(Range("A2").Value)
If Range("A2").Characters(j, 1).Font.Superscript = True Then Range("A3").Characters(j + Len(Range("A1").Value), 1).Font.Superscript = True
Next j
End Sub

Hope that helps and post back with any questions or comments.

thank you , worked for me
but how to extend the code to remaining cells , like b, c d