Can I Concatenate two cells and keep superscript?

jeebs83

Board Regular
Joined
May 12, 2009
Messages
97
Cell A1 = 10
Cell A2 = 3 (superscripted)

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

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

jeebs83

Board Regular
Joined
May 12, 2009
Messages
97
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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:

Code:
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939

ADVERTISEMENT

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:

Code:
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.
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
May 11, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
May 11, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top