PDF to Excel Editable: Fix Numbers Stored as Text

A PDF to Excel conversion is editable only when its numbers are real numbers, and converters often leave them as text, which Excel marks with a green triangle and ignores in sums. Convert them with the Convert to Number option, or with VALUE and NUMBERVALUE when hidden characters block it.

Why are converted numbers stored as text?

Converted numbers are stored as text because a PDF holds characters, not values. The converter hands Excel the string "1,234.50", and Excel keeps it as a string unless something tells it to read a number. The green triangle in the corner of the cell is Excel's way of saying so.

A PDF page records where each character sits, not what the characters mean. Software that converts the file has to guess which strings are amounts, which are dates and which are labels. When the guess is cautious, the cell arrives as text. When the cell already has the Text format, even a perfect number stays text.

How to tell a text number from a real one

Three signs give it away without any formula:

• The value sits against the left edge of the cell. Numbers align right by default.
• A small green triangle shows in the top-left corner, and hovering over the warning icon says "Number Stored as Text".
• The status bar shows a Count but no Sum when you select a column of them.

You can also test one cell directly. In an empty cell, type =ISNUMBER(B2). TRUE means a real number. FALSE means text, however it looks.

Why text numbers hurt

A SUM skips text cells silently, so a column total comes out low with no error. Sorting puts "1,000.00" before "250.00" because text sorts character by character. Filters by "greater than" find nothing, and pivot tables count the cells instead of adding them. Each of these looks like a formula bug, but the data is the cause.

The cell format can add to the problem. If the column was set to the Text format, either by the converter or by you, Excel stores whatever you type or paste as text. Switching the format to General afterwards does not convert the existing cells. Each one needs to be re-entered or converted, which the next section does in one step.

This is the most common reason a converted statement will not total. The habit that saves time is to check one column with =ISNUMBER before you build anything on the sheet. Convert PDF to Excel Without Losing Formatting covers the number formats you may want to restore once the values are real.

Convert to Number: the quick editable fix

Convert to Number is the fastest repair: select the cells with green triangles, open the warning icon, and choose Convert to Number. Excel reads each string as a number in one action. It works when the text is a clean number such as "1234.50" or "1,234.50" and fails when something else hides in the cell.

Here are the steps:

1. Select the whole column of amounts, or a block of cells that show the green triangle.
2. A small warning icon appears next to the selection. Click it.
3. Choose Convert to Number.
4. Check the alignment. The values should now sit on the right.

If no warning icon appears, error checking may be off. In Excel's options, under Formulas, the rule "Numbers formatted as text or preceded by an apostrophe" must be ticked.

Two other quick fixes

When the menu is missing, two older tricks work on clean text:

• Text to Columns. Select the column, choose Data, then Text to Columns, and press Finish without changing anything. Excel re-reads each cell as it would a typed entry. Dates and numbers both convert this way.
• Paste Special, multiply. Type 1 in an empty cell and copy it. Select the text numbers, choose Paste Special, then Multiply. Every cell is multiplied by 1, which forces a number. Remove the helper cell afterwards.

All three methods change the cell in place. They share one limit: they only help when Excel can read the string as a number. A cell with an invisible character, a trailing minus sign or the wrong decimal mark will stay text, or turn into an error. That is the job of formulas.

When the format is the problem

If you converted a column and the value returned to text, check the cell format. Select the column and set the format to General or Number, then run Convert to Number again. Fix the format first, and the conversion holds.

VALUE and NUMBERVALUE for stubborn text amounts

VALUE and NUMBERVALUE are formulas that turn a text string into a number when Convert to Number does nothing. VALUE reads the string the way your regional settings expect. NUMBERVALUE lets you state the decimal and group separators yourself, which is what you need when the file comes from another region.

The syntax is short:

• =VALUE(A2) returns the number in A2 if Excel can read it as one.
• =NUMBERVALUE(A2, ",", ".") reads A2 with a comma as the decimal mark and a period as the group separator.

You put the formula in a helper column, fill it down, then copy the helper column and paste it back as values over the original. Delete the helper column last.

A worked example: four stubborn amounts

The table below shows four amounts as a converter might return them. They are examples, not data from a real statement. Each one fails Convert to Number for a different reason, and each has its own fix.

Read the formulas from the inside out. In A3, SUBSTITUTE swaps the hidden character for nothing, and VALUE then reads what is left. In A4, the formula checks the last character, drops it, converts the rest and flips the sign. If the cell has no trailing minus, the formula falls through to a plain VALUE.

You can combine the fixes in one formula when a column mixes problems. A common pattern wraps the substitution for code 160 and the trim together, then hands the clean string to VALUE or NUMBERVALUE. Test it on a handful of rows before filling down the full column.

Checking the result

After you fill the formula down, confirm that nothing broke:

• Run =COUNT(range) on the helper column and compare it to the number of rows. A smaller count means some cells still failed.
• Look for #VALUE! errors. They mark strings the formula could not read, and they usually contain a character you have not found yet.
• Compare one total to the figure printed on the PDF.

For how a comma decimal causes trouble in Google Sheets, where the locale setting decides how numbers are read, see locale settings that scramble spreadsheet dates and decimals.

Hidden spaces and non-breaking characters in editable cells

Some cells refuse every fix because they hold characters you cannot see. The usual culprit is the non-breaking space, character code 160, which looks like a normal space but is a different character. TRIM removes ordinary spaces and leaves it alone, which is why a cleaned cell can still be text.

PDF text carries these characters often, because layout software uses them to keep a number and its currency symbol from splitting across a line. Converters copy them into the sheet without comment.

How to find an invisible character

Use LEN to compare what you see to what is there. If a cell shows "480.00", that is six characters. If =LEN(A2) returns 7, one character is hiding.

To see which character, use =CODE(LEFT(A2,1)) for the first character, or =UNICODE(MID(A2,3,1)) for any position. A result of 32 is an ordinary space. A result of 160 is the non-breaking space. Other codes point to stranger characters, such as a zero-width space (8203) or a line break (10).

How to remove them

For the common cases, substitute them out:

• Non-breaking space: =SUBSTITUTE(A2,CHAR(160),"")
• Line break inside a cell: =SUBSTITUTE(A2,CHAR(10),"")
• Other unprintable characters: =CLEAN(A2) removes the first 32 control codes, though not code 160.

You can also use Find and Replace once for the whole column. Copy one hidden space out of a cell, paste it into the Find box, leave Replace empty and choose Replace All. This changes the cells in place and avoids a helper column, but it works only for a character you have copied exactly.

Currency symbols and letters

Hidden characters are not the only blockers. A symbol such as a dollar sign usually reads fine, but a trailing "CR" or "DR", a stray letter, or a footnote mark makes the whole cell text. Where the extra letters carry meaning, such as credit and debit markers, keep that information in its own column before you strip it. Splitting such a column is a Power Query job, and transforming a PDF import with Power Query cleanup steps shows how.

Is a protected sheet blocking edits?

A protected sheet blocks edits when the author locked the cells and turned protection on. Excel then refuses typing, pasting and formula changes, and shows a message saying the cell is protected. You can remove it with Review, then Unprotect Sheet, if the sheet has no password or if you know it.

Not every blocked edit is a protected sheet, so check the cause before you try anything else. These are the usual reasons a converted file will not take your changes:

1. Protected View. A yellow bar at the top of the window says the file came from the internet or an email. Choose Enable Editing and the file opens normally.
2. Sheet protection. Review shows "Unprotect Sheet" instead of "Protect Sheet". Cells are locked by default, and protection takes effect only once the sheet is protected.
3. Workbook structure protection. You can edit cells but cannot add, rename or move sheets. Review also holds the option to unprotect the workbook structure.
4. Read-only file. The title bar says Read-Only, often because the file is open elsewhere or marked final.
5. A converted file with no protection at all. If you can click into the cell but the value does not change when you retype it, the format is Text, as covered above.

If there is a password

A password-protected sheet is the owner's choice, and the correct step is to ask them for the password or for an unprotected copy. Converted files from a PDF rarely carry protection, so if one does, the file likely came to you as an Excel workbook that someone else built.

A PDF itself can also be locked with a permissions password that bars copying. That is a limit on the PDF, not on the Excel sheet, and it appears before conversion rather than after.

After the fix: keeping the file editable

Once the numbers are real, a few habits keep the sheet editable. Convert one column and verify it with =COUNT before moving to the next. Keep the helper formulas until the totals match the PDF, then paste values over them. Save a copy of the raw conversion in a separate tab so you can always return to the source.

If your source is a bank statement, an extraction that returns amounts as numbers already avoids most of this work. FastBankConvert is getting ready to open and is built to put dates, descriptions, amounts and balances into their own columns, and you can join the list through Get started. A plain CSV export is another way to dodge the cell-format trap, because a text file has no formats to inherit; turning a bank statement into a clean CSV explains that route.