CODEWITHSUNDEEP
excelvbanumber-formattingpercentage
Oak
Oak

Reputation: 362

Excel VBA : format to percentage without percentage sign

To keep the question as simple as possible:

In excel cell A1 has a numeric value: 0.11

I want to format the cell to percentage value but without the % sign. I want: 11 ; not 11%

I am not asking how to do this in regular excel; it must be VBA..

I guess Range("A1").NumberFormat = .... is the way to go; but when using this method, the % sign always show up.

Is there a way to format this without the percentage sign? Note that the value can not be changed, it has to remain 0.11 .

Upvotes: 2

Views: 3029

Answers (4)

JvdV
JvdV

Reputation: 75840

It's a bit of a workaround but try this:

Sub Test()

Dim i As Long
Dim STRNG As String

For i = 2 To Range("A1").Height / 11.25
    STRNG = STRNG & Chr(10)
Next i
Debug.Print STRNG
Range("A1").NumberFormat = STRNG & "0" & Chr(10) & "%"

End Sub

It will look at the cells height and will calculate how many linebreaks you need to hide the "%" sign.

You might want to add on it, like call it upon sheet change event and instead of looking at only cell A1 then loop from A1 to A & Lastrow.....

Or assign it to a button offcourse

Upvotes: 1

Pierre44
Pierre44

Reputation: 1741

Another workaround would be to display the content of your cell x 100. 

Sub Macro1()

    Dim cell As Range
    For Each cell In Range("A:A")   ' Change with your Range
        If cell.Value > 0 Then
            cell.NumberFormat = Str(cell.Value * 100)
        End If
    Next cell

End Sub

This would work but would have to be rerun with every change. For example with the following code in your Worksheet: 

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, Range("A:A")) Is Nothing Then 
       Call Macro1            
   End If

End Sub

Sub Macro1()

        Dim cell As Range
        For Each cell In Range("A:A")   ' Change with your Range
            If cell.Value > 0 Then
                cell.NumberFormat = Str(cell.Value * 100)
            End If
        Next cell

    End Sub

Upvotes: 1

Samuel Hulla
Samuel Hulla

Reputation: 7089

This will work as requested:

Private Sub remove_percent(where As Range)

    Dim cell As Range
    For Each cell In where
        If (cell.NumberFormat = "0.00") Then
            cell = Round(cell * 100, 0)
            cell.NumberFormat = "0"

            ' if we exceed 100%, let's round it to 100
            If (cell > 100) Then
                cell = 100
            End If     
        End If
    Next cell

End Sub

You can then invoke it in the following way (as an example):

Call remove_percent(Range("A1:A5"))

Upvotes: 0

Imran Malek
Imran Malek

Reputation: 1719

Try this,

Range("A5").NumberFormat = "0.00" & Chr(10) & "%"
Range("A5").WrapText = True

Upvotes: 1

Related Questions