how to remove % using VBA in excel

[jj0911]

Hello,
i have an excel file with a lot of columns in different tabs that have % signs. i want to write a VBA macro to remove all % sign. for example 98.23% change to 0.98 and 67.9% change to 0.68 etc. there are a lot of these percentages in different columns and different tabs in the excel file. so i am thinking to write a VBA function or a macro code to change those percentages but don't know how to write the code. would be appreciate if somebody can help me on this.

 

[johnnyL]

Simplest I can think of, assuming the entire column is percentages, is:

Sheets("Sheet1").Columns(1).NumberFormat = general          ' Convert Column A percents to decimal

 

[jj0911]

Simplest I can think of, assuming the entire column is percentages, is:

Sheets("Sheet1").Columns(1).NumberFormat = general          ' Convert Column A percents to decimal

Thank you very much. but i have a lot of percentages in different column and different tab. how do i let the code update all of those columns?

 

[Sanjeev1976]

Try using this on a copy of the file :

Sub Remove_Percentage()

Dim rng As Range: Set rng = Application.Range("Sheet1!A1:B6")
Dim cel As Range
For Each cel In rng.Cells
        
        If cel.NumberFormat = "0%" Then
        
            cel.NumberFormat = "0.00"
        
        End If
        
Next cel

End Sub

Change the Range and Sheetname as per your requirement

 

[jj0911]

Try using this on a copy of the file :

Sub Remove_Percentage()

Dim rng As Range: Set rng = Application.Range("Sheet1!A1:B6")
Dim cel As Range
For Each cel In rng.Cells
       
        If cel.NumberFormat = "0%" Then
       
            cel.NumberFormat = "0.00"
       
        End If
       
Next cel

End Sub

Change the Range and Sheetname as per your requirement

Thank you very much. I tried this code. there is no error message but the % did not be removed. not sure what's happened.

 

[Peter_SSs]

What result do you get if you put this formula in a blank cell and point it at one of your % cells?
=ISNUMBER(C5)

 

[jj0911]

What result do you get if you put this formula in a blank cell and point it at one of your % cells?
=ISNUMBER(C5)

i got TRUE.

 

[Sanjeev1976]

i got TRUE.

Please provide a small sample of the data using the xl2bb tool available on this platform

 

[jj0911]

i just open a new excel workbook, and created some fake #% in A1 through B6, and then i created a macro just copy and paste the code. and then run the macro. there is nothing happen after i run the code.

 

[Peter_SSs]

Try this with your test workbook

Sub ChangePercentageToDecimal()
  Dim cel As Range
  
  For Each cel In Range("A1:B6")
    If cel.Text Like "*%" Then cel.NumberFormat = "0.00"
  Next cel
End Sub