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)
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,545
Members
414,316
Latest member
ExcelLee

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