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.
Download Practice Workbook
Download this practice workbook below.
7 Ways to Remove Percentage Symbol in Excel
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 range of cells E5:E12 is free of the percentage symbol.
Read More: How to Remove Sign from Numbers in Excel [6 Suitable Methods]
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:
=VALUE[LEFT[TEXT[E11,"000.0000%"],7]]
- After entering the formula, you will notice that the values with the percentage symbol in the cell E5 are no longer with the percentage symbol in cell F5
- Then drag the fill handle icon to the cell F12, and notice all the entries in the range of cells E5:E12 are now without the percentage symbol.
🔎 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:
In this formula, the arguments inside the functions should enter with care. 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: How to Remove Sign in Excel [With 3 Examples]
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.
- And then enter the following formula in the Custom Column Formula field:
=Number.Round[[Percentage]*100,2,RoundingMode.Up]
- 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 Symbol in Excel [8 Ways]
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.
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 data location.
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.
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 to formulas, then continuing to using Power Query, finally ended up with using VBA Macros. Among all of the methods used here, using formatting options is the easier to understand and simple 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.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the ExcelDemy community will be highly appreciable.
Related Articles
- How to Remove Pound Sign in Excel [8 Easy Methods]
- Remove Dollar Sign in Excel Formula [2 Easy Ways]
- How to Remove Plus Sign in Excel [2 Easy Techniques]
- Remove Currency Symbol in Excel [6 Ways]