Format a number like a percentage (x100) without the % symbol

Question

This is the opposite to the question Number format in excel: Showing % value without multiplying with 100.

I have data which is in a format where, for example, a cell whose raw content is 0.75 "means" 75%. I'm setting up a process for transferring such data to another program (Adobe Illustrator) which trips up if numbers contain any symbol (even %).

I'd like to do it without any modifications to the content or structure of the sheet, so, no new columns, and no changes to the underlying data. Obviously without this limitation, adding a column with a formula like =A1*100 would be very easy. I want to avoid these because these sheets are complex, they're maintained by a team other than the person doing this process, and I don't want the person doing this process changing the actual content of the sheet beyond an easily reversible presentational change like a new number format.

Is there any number format or similar presentation layer modification that will allow me to take data stored like 0.75, 0.5, 0.25 and copy and paste it into another application like 75, 50, 25?

---

The magic "Percentage" number format, which multiplies by 100, appear to be listed in custom formats as 0.00%, but simply removing the % also removes the multiplication.

---

I found a blog (Chandoo) that suggests using a format like #,#.# [ctrl-J] % to hide the percentage sign below a new line, but that doesn't work for me since I need clean output for the application my users will copy and paste the output into.

Answer 1

You can use this Macro to get the job done. But first, you must add a reference to the Microsoft Forms 2.0 library. Press Alt+F11 to bring up the Visual Basic Editor, then click on Tools >> References... Scroll down until you see Microsoft Forms 2.0 Object Library and check the box next to it and click OK. If you don't see it (they are listed alphabetically), you'll need to add it via the dll. Click on Browse... and browse to and add C:\Windows\System32\FM20.DLL.

Usage: Select the range (you can select the entire column) and invoke the macro via whatever method is easiest for your user. After the macro runs the data will be on the Clipboard. You can make a button, assign a hotkey or manually run it via the ribbon. I'm assuming that the values are all calculated with formulas, and that there aren't any blank cells. It does not modify the workbook at all, so if your user messes up and selects the wrong column there is no impact to your data.

Option Explicit

Public Sub CopyPercentAsWholeNumber()

Dim Arr() As Variant
ReDim Arr(1 To Selection.SpecialCells(xlCellTypeFormulas).Count)

Dim i As Long
With Selection
    For i = 1 To UBound(Arr)
        Arr(i) = .Cells(i).Value * 100
    Next i
End With

Dim S As String
Dim objText As New MSForms.DataObject
S = Join(Arr, vbCrLf) & vbNewLine
objText.SetText S
objText.PutInClipboard

End Sub

Answer 2

Quick fix

Highlight your numbers. Go on custom formats type in 0.00 then ctrl+j in the same bit and then type percentage sign. Then go on alignment and click wrap text. Click ok

This hides the % sign but it means you can change the column name to the units your using.