How to Remove Percentage Symbol in Excel (7 Ways)

The dataset below will be used to demonstrate how to remove percentage symbols in Excel.

7 Ways to Remove Percentage Symbol in Excel


Method 1 – Use of Custom Formatting to Remove Percentage Symbol

Steps

  • Select the cells where you want to remove the percentage sign. We selected the range of cells E5:E12.

Using Custom Formatting to Remove Percentage Symbol

  • Right-click and click Format Cells.

Using Custom Formatting to Remove Percentage Symbol

  • In the format cells window, go to Number > Custom and in the Type field, you will see 0.00% format.

Using Custom Formatting to Remove Percentage Symbol

  • In the Type field, place the cursor before the % sign and press Ctrl+J.

Using Custom Formatting to Remove Percentage Symbol

  • The formatting in the Type field will change to 0.00. Click OK.

Using Custom Formatting to Remove Percentage Symbol

  • The cells are still displaying the percentage symbols.

  • To resolve this, select the range of cells E5:E12 again, and click Wrap Text from the Home tab.

Using Custom Formatting to Remove Percentage Symbol

Read More: How to Remove Sign from Numbers in Excel


Method 2 – Using Formula to Remove Percentage Symbol

Steps

  • Select cell F5 and enter the following formula:
=VALUE(LEFT(TEXT(E11,"000.0000%"),7))
  • The value in cell E5 is copied to cell F5 without the percentage symbol.

Using Formula to Remove Percentage Symbol in Excel

  • Drag the fill handle icon down to apply the formula to the entire range of cells.

Using Formula to Remove Percentage Symbol in Excel

Breakdown of the Formula

1. TEXT(E8,”000.00%”): It takes the content in cell E8 as input and returns the text string in format 000.00.

2. LEFT(TEXT(E8,”000.00%”),5): This function will extract the 5 characters on the left side of the string return in TEXT function

3. VALUE(LEFT(TEXT(E8,”000.00%”),5)): This function will return the number from the string format from the left function.

Note:

You should enter the correct arguments inside the functions. The format 000.00 represents the rounding value, which is 2 here. You need to enter a suitable rounding value based on your own requirements.

Read More: Remove Percentage Symbol in Excel Without Changing Values


Method 3 – Applying Power Query in Excel

Steps

  • Go to Data > From Table/Range in Get and Transform Data group.

Utilizing Power Query to Remove Percentage Symbol in Excel

  • A small window will open asking the range of the table, press the up-arrow key and select the range of cells B4:E12. Tick My table has headers to let Excel know that your table’s first row is the header. Click OK.

  • A new window will open. Go to Add Column tab and select Custom Column.

Utilizing Power Query to Remove Percentage Symbol in Excel

  • New options menu will appear. Enter a desired name in the New column name box.
  • Enter the following formula in the Custom Column Formula field:
=Number.Round([Percentage]*100,2,RoundingMode.Up)
  • Click OK.

  • A new column with all values from the Percentage column without the percentage symbol in the Percentage(Symbol Rounded) column will appear on the sheet.

  • Load these columns into the worksheet by clicking Close and Load from the Home Select the option Close and Load To.

Utilizing Power Query to Remove Percentage Symbol in Excel

  • The existing window will close and return to the main worksheet, with a new window.
  • In that window specify the location where you want to put the newly created table, select the location in the worksheet and click OK.

Utilizing Power Query to Remove Percentage Symbol in Excel

  • The table created in the power query will now be in the worksheet with formatted percentage values.

  • Copy the Percentage (Symbol Remove) column and paste it in the original table.

Utilizing Power Query to Remove Percentage Symbol in Excel

Read More: How to Remove Currency Symbol in Excel


Method 4 – Multiply Specific Number to Remove Percentage Symbol

Steps

  • Enter the following formula in cell F5:
=E5*100
  • The values in the percentage column are displayed in the Percentage (Symbol Removed) column without the percentage sign.

Multiply Specific Number to Remove Percentage Symbol in Excel


Method 5 – VBA Macro to Remove Percentage Symbol

Steps

  • Go to the Developer tab and click Visual Basic.

VBA Macro to Remove Percentage Symbol

  • Click InsertModule.

  • In the Module window, enter the following code.
Sub remove_percentage_sign()

Dim rng, cell As Range
Set rng = Range("E5:E12")

For Each cell In rng

 cell.NumberFormat = "General"
 cell = cell.Value * 100

Next

End Sub

  • Close the window.
  • Go to the View Select Macros > View Macros (Double Click).

VBA Macro to Remove Percentage Symbol

  • Select the macro that you created just now. The name here is remove_percentage_sign. Click OK.

  • The percentage symbol will be removed from all the numbers  in the column.

VBA Macro to Remove Percentage Symbol

Note:

In this VBA code, the range of cells E5:E12 indicates the range of input data. You need to enter a suitable range of data based on your own requirements/worksheet.


Method 6 – Utilizing Number Formatting Method

Steps

  • Copy the entries from the Percentage column to Percentage (Symbol Removed).

  • In the Percentage (Symbol Removed) column, from the Home tab, click on text formatting in the Number group.

sing Number Formatting to Remove Percentage Symbol 

  • A new menu will open. Click Number.

  • All the numbers in the range of cells F5:F12 are now without percentages.

sing Number Formatting to Remove Percentage Symbol 

  • But, their values are incorrect. To fix it, multiply the values by 100 to get the accurate values without a percentage symbol.


Method 7 – Using General Formatting to Omit Percentage Symbol

Steps

  • Copy the entries from the Percentage column to Percentage(Symbol Removed).

sing Generel Formatting to Remove Percentage Symbol 

  • In the Percentage(Symbol Removed) column, from the Home tab, click on text formatting in the Number group.

Using General Formatting to Remove Percentage Symbol

  • A new menu will open. Select General.

  • All the numbers in the ranges of cells F5:F12 are now without a percentage symbol.

Using General Formatting to Remove Percentage Symbol

  • But their values are incorrect. Multiply the values by 100 to get the correct values.

Using General Formatting to Remove Percentage Symbol


Download Practice Workbook


Related Articles


<< Go Back to Remove Symbol in Excel | Excel Symbols | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo