Convert PDF Text Percentages to Numbers in Excel for Mac
Question details
The user needs to convert imported text percentages (such as "0.53%") from a PDF into actual numeric values without improperly splitting the data.
- Product
- Excel for Mac
- Device & OS
- Mac
- Scenario
- Importing data from a PDF into a spreadsheet and needing to fix formatting issues where percentage values are stored as text.
- Observed behavior
- Using "Text to Columns" with a decimal point as a delimiter incorrectly splits values like "0.53%" into two separate columns instead of converting them to a numeric percentage.
Make sure to widen your columns to view the full text and check if there are any hidden trailing spaces in the imported PDF data before applying conversion formulas.
Use the VALUE Function to Convert Text to Numbers
The VALUE function is the safest and most reliable way to convert text strings containing numbers and percent signs into actual numeric values.
When importing data from PDFs, spreadsheet software often reads numbers with symbols (like %) as plain text. The VALUE function forces the software to recognize standard numeric formats and convert them into measurable decimal values (e.g., converting 0.53% to 0.0053).
Click on an empty cell adjacent to the cell containing your text percentage (for example, B1 if your text is in A1).
Type the formula =VALUE(A1) into the formula bar and press Return. The text "0.53%" will be converted to the numeric value 0.0053.
Select the new cell with the numeric value, navigate to the Home tab on the ribbon, and click the Percentage style button (%) to display it as 0.53%.
Multiply the Text by 1 Using Paste Special
This is a quick shortcut to force the spreadsheet to evaluate and convert text strings into numbers without writing any formulas.
Clean Hidden Spaces Before Conversion
PDF imports often include non-breaking spaces or hidden formatting characters that prevent the VALUE function from working properly.
Use WPS Office to Perfectly Convert PDFs to Spreadsheets
Instead of manually fixing text-to-number errors after the fact, use the built-in PDF to Excel converter in WPS Office. It accurately recognizes tables, numeric formats, and percentages during the extraction process, saving you from complex data cleanup.
- 1. Open the PDF tool: Launch WPS Office, navigate to the Tools tab, and click on 'PDF to Excel'.
- 2. Import your file: Select the PDF file containing your data tables and import it into the conversion dialog.
- 3. Convert the document: Choose your desired output location and click 'Convert' to start the extraction.
- 4. Open in WPS Spreadsheet: Open the newly created .xlsx file in WPS Spreadsheet. Your percentages and numbers will already be perfectly formatted as numeric values.
Frequently Asked Questions
Why does 'Text to Columns' split my percentages?
When you use a decimal point as a delimiter in the 'Text to Columns' wizard, you are instructing the software to break the data exactly where the dot is. This separates the whole number portion from the decimal fraction into two adjacent columns, rather than converting the entire string.
How do I format the decimal 0.0053 back to 0.53%?
Select the cell containing the decimal value, right-click, and choose 'Format Cells'. Navigate to the 'Number' tab, select 'Percentage' from the category list, set the decimal places to 2, and click OK.
Does the VALUE function work with currency symbols too?
Yes, the VALUE function is designed to recognize and convert standard text formats, including percentage signs (%) and standard currency symbols ($), into pure numeric values that can be used in calculations.
What if the VALUE function returns a #VALUE! error?
A #VALUE! error usually indicates that there are hidden spaces, non-breaking spaces, or unrecognized characters embedded in the text from the PDF import. You can fix this by nesting the TRIM and CLEAN functions inside the VALUE function, like =VALUE(TRIM(CLEAN(A1))).
