CODEWITHSUNDEEP
vbaexcel
Estevam Garcia
Estevam Garcia

Reputation: 445

Convert decimal to percentage

I'm receiving data like below from an external source

enter image description here

What I need is to convert all highlighted data to percentage and I can't lost the part after the comma. Here is what I have tried so far:

For Each actCell In shRecipiente.ListObjects("tblRecipiente").ListColumns("All-In Rate").DataBodyRange
    If Not (Right(actCell.NumberFormat, 1) = "%") Then
        actCell.Value = Format(actCell, "#.####%")
    End If
Next actCell

But I'm getting for example 396,83% instead of 3,9683%

Upvotes: 3

Views: 10835

Answers (2)

Dirk Reichel
Dirk Reichel

Reputation: 7979

change

actCell.value = Format(actCell, "#.####%")

to

actCell.value = Format(actCell / 100, "#.####%")

However, if it is no real % value you neet to change it to

actCell.value = Format(actCell, "#.####""%""")

or if you cant change the values try

actCell.Offset.NumberFormat = "#.####""%"""

Upvotes: 2

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19767

Divide the number by 100 and format as a percentage.

=A1/100

Edit - to check which is formatted as a percentage and which isn't use: =IF(CELL("format",A1)="P0",A1,A1/100) - P0 indicates a percentage format. The formula will display all the numbers without percentages, so 150% will display as 1.5, as will 150.
Then it's just a case of formatting the entire column back to percentages.

Upvotes: 0

Related Questions