'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Convert Imported Excel Percentages from Text to Decimal Values
Question details
The user needs to convert percentage data imported from a website, which is currently treated as text, into numeric decimal values for multiplication and other mathematical operations.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Importing financial or statistical data from websites into a spreadsheet where the numbers need to be calculated.
- Observed behavior
- The imported percentage figures are recognized as plain text rather than numeric values, preventing standard mathematical calculations.
Before starting, check your system's regional settings, as imported data might use different decimal and thousands separators than your computer's default configuration.
Convert Text to Decimals using Power Query
Power Query is the most robust method for cleaning up imported web data and automatically assigning the correct numeric data types.
When dealing with data imported from web sources, Power Query allows you to strip out invisible text characters and accurately convert columns into usable numerical formats.
Select your imported data table, go to the Data tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.
Right-click the column header containing the text percentages and select 'Replace Values'. Remove any unwanted characters like parentheses or extra spaces.
Click the data type icon on the left side of the column header and select 'Percentage' or 'Decimal Number'.
If the value appears as a whole number (e.g., 25 instead of 0.25), go to the Transform tab, select Standard > Divide, and enter 100.
Click 'Close & Load' on the Home tab to insert the cleaned decimal values back into your spreadsheet.
Convert Text to Numbers using Spreadsheet Formulas
Use spreadsheet formulas if you want to quickly transform the data in an adjacent column without using the Power Query interface.
Clean and Convert Imported Data Easily with WPS Spreadsheet
WPS Spreadsheet provides powerful data cleaning tools, including robust formula support and text-to-columns features, making it incredibly simple to convert web-imported text percentages into calculable decimal values.
- 1. Open your Web Data: Launch WPS Spreadsheet and open the document containing your web-imported text values.
- 2. Apply Conversion Formula: In an empty adjacent column, use the NUMBERVALUE or VALUE formula to target the text cells.
- 3. Format the Decimals: Select the newly calculated cells, right-click, choose 'Format Cells', and select 'Number' with the desired decimal places.
- 4. Replace the Original Data: Copy the new numeric results and use 'Paste Special' > 'Values' to finalize your usable dataset.
Frequently Asked Questions
Why does my spreadsheet treat imported web numbers as text?
When copying or importing data from websites, numbers often contain hidden characters, non-breaking spaces, or HTML formatting tags. Spreadsheets interpret these unrecognizable characters as plain text rather than numerical values, preventing calculations.
How do I fix a percentage that converted to a whole number instead of a decimal?
If a percentage converted to 25 instead of 0.25, you need to divide the value by 100. You can type 100 in an empty cell, copy it, select your numbers, and use Paste Special > Divide. Alternatively, divide the cell reference by 100 inside your formula.
Does the NUMBERVALUE function work with different regional settings?
Yes, NUMBERVALUE is designed precisely for this. It allows you to specify the decimal separator and group separator as arguments within the formula, making it ideal for converting text to numbers regardless of your computer's local regional settings.
