How to Remove Percentage Symbol in Excel (7 Ways)

For many practical reasons, one might need to remove the percentage symbol from the number string. The answer to the question “How to remove percentage symbol in Excel” is answered here with a broad explanation with demonstrations.

Remove Percentage Symbol in Excel: 7 Ways

In this article, I am going to use this dataset for demonstrating purposes. In the range of cells E5:E11, numbers with the percentage (%) sign in the Percentage column are present. On the right side of this column is another column containing numbers that are actually from the Percentage column after the percentage sign is removed. How we get to these numbers without the percentage (%) symbol, we will discuss here with explanations with demonstrations.

1. Use of Custom Formatting to Remove Percentage Symbol

The formatting tool/option is a very handy way to omit percentage symbols from the numbers.

Steps

• First, select the cells that need to be free from the percentage sign. In this case, select the range of cells E5:E12.

• Then click the right mouse button, from the context menu, and click Format Cells.

• In the format cell window, go to Number > Custom, and then in the Type field, you will see 0.00% formatting already existing.

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

• After pressing Ctrl+J, you will notice that the formatting in the Type field is now changed to 0.00 only, removing the percentage sign. Then click OK.

• After clicking OK, you will see that the numbers are still with percentage symbols.

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

After clicking wrap text, you will finally observe that the range of cells E5:E12 is free of the percentage symbol.

Read More: How to Remove Sign from Numbers in Excel

2. Using Formula to Remove Percentage Symbol

In this method, we’re going to use the TEXT function to format numbers with percentages to have no percentages. Here, the TEXT function will pull the number and return a character string according to our format argument. And then the VALUE function will convert that string to a number. The actual formula will occupy the TEXT function alongside the LEFT and VALUE functions.

Steps

• Select the cell F5 and enter the following formula:

• After entering the formula, you will notice that the values with the percentage symbol in cell E5 are no longer with the percentage symbol in cell F5

• Then drag the fill handle icon to cell F12, and notice all the entries in the range of cells E5:E12 are now without the percentage symbol.

Read More: Remove Percentage Symbol in Excel Without Changing Values

3. Applying Power Query in Excel

Power Query is considered to be a very powerful tool to execute commands repeatedly in Excel. Although this won’t be a short process. Once it’s done, it can be used to repeat the whole process a lot.

Steps

• First, go to Data > From Table/Range in Get and Transform Data group.

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

• After that, a new window will open, in that window, from the Add Column tab select Custom Column.

• Right after clicking the Custom Column option, a new options menu will appear, in that menu, enter the new column name in the New Column.
• Then enter the following formula in the Custom Column Formula field:

• After entering the formula, Click OK.

• Then you will notice there is a new column with all values from the Percentage column now without the percentage symbol in the Percentage(Symbol Rounded) column.

• After this, you need to load these columns into the worksheet. To do this, click Close and Load from the Home tab. After clicking the Close and Load, select the option Close and Load To.

• Next, the existing window will close and return to the main worksheet, with a new window.
• In that window specify the location of the table created, select the location in the worksheet, and click OK.

• 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 beside the existing table.

Read More: How to Remove Currency Symbol in Excel

4. Multiply Specific Number to Remove Percentage Symbol

Steps

• To do this process, enter the following formula in cell F5:

This formula will multiply the values of the cells in the Percentage column with 100 and make them free of the percentage symbol.

• After entering the value, you will see that the values in the percentage column are now without the percentage sign in the column Percentage (Symbol Removed).

5. VBA Macro to Remove Percentage Symbol

Using a simple VBA Macro can drastically reduce the time to remove the percentage sign in an Excel worksheet.

Steps

• First, go to the Developer tab, then click Visual Basic.

• Then click Insert > Module.

• 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

• Then close the window.
• After that, go to the View tab. Select Macros > View Macros(Double Click).

• After clicking View Macros, select the macros that you created just now. The name here is remove_percentage_sign. Then click Run.

• After clicking Run, you will see that all the numbers in column Percentage are shown without the percentage symbol.

6. Utilizing Number Formatting Method

Formatting texts with the number format can easily remove percentage symbols.

Steps

• First, copy the entries from the Percentage column to Percentage (Symbol Removed).

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

• After clicking the format option, a new menu will open, from that menu, click Number.

• After selecting Number, you will notice that all your numbers in the range of cells F5:F12 are now without percentages.

• But at the same time, their value is not showing properly. To fix that, multiply the values by 100 to get the accurate values without a percentage symbol.

7. Using General Formatting to Omit Percentage Symbol

Formatting with general settings can easily omit percentage symbols without any hassle.

Steps

• At first copy the entries from the Percentage column to Percentage(Symbol Removed).

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

• After clicking the format option, a new menu will open, from that menu, select General.

• After selecting General, you will notice that all your numbers in the ranges of cells F5:F12 are now without a percentage symbol.

• But at the same time, their value is not showing properly. To fix that, multiply the values by 100 to remove the percentage symbol in Excel.

Download Practice Workbook

Download this practice workbook below.

Conclusion

To sum it up, the question “how to remove percentage symbol in Excel” is answered here in 7 different ways. Starting from using the formatting option for formulas, then continuing to using Power Query, and finally ending with using VBA Macros. Among all of the methods used here, using formatting options is the easiest to understand and simplest one to use. The VBA process is also less time-consuming and simplistic but requires prior VBA-related knowledge. Other methods don’t have such a requirement.

For this problem, a macro-enabled workbook is attached where you can practice and get used to these methods.

Related Articles

• How to Remove Dollar Sign in Excel
• How to Remove Dollar Sign in Excel Formula
• How to Remove Pound Sign in Excel
• How to Remove Plus Sign in Excel
• How to Remove Negative Sign in Excel
  

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