Fix Pound Values Imported as Text in Excel from PDF
Question details
User is experiencing an issue where numerical values containing a British Pound (£) symbol imported from a PDF into Excel are recognized as text rather than numbers, breaking calculations.
- Product
- Microsoft Excel / PDF Readers
- Device & OS
- not provided
- Scenario
- Importing or exporting tabular data from a PDF document into an Excel spreadsheet.
- Observed behavior
- Currency values with pound symbols are formatted as plain text instead of numeric values, making it impossible to perform mathematical operations or apply standard Excel number formatting.
Before troubleshooting, verify if the issue occurs with all PDFs or just a specific document, and check if you are exporting via a desktop application or a web browser to isolate the cause.
Remove Pound Symbols and Convert Text to Numbers
Manually remove the text-based pound symbols using the Find and Replace tool, convert the cells to numbers, and then reapply the correct currency format.
Often, Excel fails to recognize the imported string as a number due to the presence of the currency symbol. Stripping the symbol out allows Excel to recognize the raw numbers.
Highlight the range of cells containing the imported text values with the pound symbols.
Press `Ctrl + H` on your keyboard to open the Find and Replace dialog box.
In the 'Find what' field, type the pound symbol (£). Leave the 'Replace with' field completely empty, then click 'Replace All'.
If the numbers are still stored as text, highlight them, click the small warning icon that appears next to the selection, and choose 'Convert to Number'.
With the cells selected, go to the Home tab, click the Number Format dropdown, and select 'Currency' or 'Accounting' to properly reapply the pound symbol as a visual format.
Use Formulas to Extract Numeric Values
Use Excel formulas to strip out the non-numeric characters and convert the remaining string into a usable number.
Use Power Query for PDF Import
Bypass third-party PDF converters and use Excel's built-in 'Get Data' feature to cleanly import tables and define data types directly.
Convert PDFs to Spreadsheets Accurately with WPS Office
WPS Office features a powerful built-in PDF to Excel converter that intelligently recognizes tabular data and currency formatting, saving you from tedious manual data cleaning and broken formulas.
- 1. Open the PDF in WPS: Launch WPS Office, click on the 'PDF' tab, and open your document containing the tables.
- 2. Select PDF to Excel: In the top toolbar under the Tools menu, click on 'PDF to Excel'.
- 3. Configure and Convert: Choose the specific pages you want to extract and click 'Convert'. The data will automatically open in WPS Spreadsheet with the numbers correctly formatted for immediate calculation.
Frequently Asked Questions
Why does Excel format imported PDF numbers as text?
When converting from a PDF, the export software often reads the pound symbol (£) or extra spaces as literal text characters rather than a recognized currency identifier. This causes Excel to categorize the entire cell as text instead of a computable number.
Does the source of the PDF matter when importing data?
Yes. PDFs generated from scanned images using optical character recognition (OCR) or poorly formatted software may have hidden characters or formatting glitches that confuse Excel. Digitally generated native PDFs usually export much cleaner data.
How can I prevent currency symbols from turning into text during export?
Using a robust PDF to Excel converter or utilizing Excel's native Power Query 'Get Data from PDF' feature can prevent this. Power Query allows you to define data types during the import process, ensuring currency columns are parsed as numbers before they hit the spreadsheet.
