Currency format and superscript numbers (VBA)

ab3887

New Member
Joined
Jul 19, 2010
Messages
24
I have a table that looks like this:

A $125,000
B $350,000
C $109,000
D $122,000

The values come from formulas on another sheet and change frequently. The table gets attached to a publisher document that goes out to clients and row D has an important disclosure. Because of this I need to append a superscript 1 at the end of $122,000. What is the easiest way to do this with VBA?

Currently I could only get it to work by typing it in manually, formatting as text, and having a space between the last 0 and the superscript 1, but I wasn't able to replicate this in VBA.

(I'm using excel 2007)
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
Hi

A much easier solution, that doesn't require any further processing is to format the cell with the custom format

$#,##0 ¹

where the ¹ is not a regular character "1" in which you apply the superscript format, but, instead, the unicode character "superscript one". You can insert it with the insert->symbol, the code is 0xB9, or with vba.
 
Last edited:

ab3887

New Member
Joined
Jul 19, 2010
Messages
24
Thanks, custom formatting definitely works better.

I ended up going with Alt+0185 to generate the superscript 1 character and it worked perfectly.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,559
Messages
5,832,498
Members
430,136
Latest member
Asir Jefferson

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