Ever opened a spreadsheet, dropped =SUM(A1:A100) at the bottom, and watched it return zero? Yeah. Frustrating. The culprit is almost always text-formatted numbers — digits that look like numbers but Excel treats as strings. You can't add them. You can't sort them properly. Formulas choke. This guide walks through every reliable way to fix it, from one-click tricks to VBA loops, with notes on when each method shines and when it falls flat.

Text-formatted numbers sneak in through imports — CSV files from your bank, exports from old databases, scraped tables from web pages, copy-paste from PDFs. Sometimes a leading apostrophe causes it. Sometimes a stray space. Sometimes a currency symbol or a thousands separator that doesn't match your locale. The fix depends on the source and the volume. Small dataset? Click the green triangle. Hundreds of thousands of rows? Power Query. Repeatable refresh? Power Query again. One-off bulk? Paste Special multiply. Pick the right tool and you save hours.

First — how do you even know a cell holds text instead of a number? Excel gives you several tells. Numbers right-align by default; text left-aligns. A small green triangle in the upper-left corner of a cell often means "this looks like a number stored as text." The ISNUMBER() function returns FALSE for text-formatted digits, and =TYPE(A1) returns 2 for text but 1 for an actual number.

Select the suspicious range — if the status bar at the bottom shows only Count instead of Sum and Average, you've got text. Each check takes seconds and confirms the diagnosis before you commit to a fix. Worth remembering when you inherit messy spreadsheets.

A second-level diagnostic — =LEN(A1) versus your expected length. If a five-digit number reads as length 6 or 7, you've got hidden characters. Run =CODE(LEFT(A1,1)) to inspect the first character. A return of 39 means an apostrophe. A return of 160 means non-breaking space. A return of 9 means tab. Each tells you which cleaning function to reach for. Diagnosis before treatment — five seconds with these checks saves an hour of trial-and-error fixes.

Multiplying by 1 deserves its own callout because it's the workhorse inside formulas. Three flavors do the same job: =A1*1, =A1+0, and =--A1. The double-negative is the most idiomatic in array-style formulas because it's two characters shorter and parses faster inside SUMPRODUCT and dynamic-array expressions. Try =SUMPRODUCT(--A1:A100) over text-formatted numbers — it sums them correctly without a helper column. Pros use this constantly. It's compact, fast, and works in every Excel version going back to 2003.

Why does multiplying by 1 work? Excel's evaluation engine forces both sides of an arithmetic operator to be numeric. When you feed it a text string that resembles a number, the engine attempts a parse, succeeds, and the result is a true number type. If the parse fails — say the cell contains "abc" — you get a #VALUE! error. Wrap with IFERROR(--A1,0) if you want a clean zero for non-numeric entries instead of error noise spreading through dependent formulas. Pattern shows up everywhere in finance models.

Now — the green triangle method. When Excel flags a cell as "number stored as text," you'll see a small green triangle in the corner and a warning icon to the left of the cell when you select it. Click that icon. The dropdown offers "Convert to Number" as the first option. Pick it. Done. Works for one cell or a selected range. The catch — it only appears when Excel itself decided the cell looks like a number stored as text.

Cells with currency symbols, weird spaces, or non-locale separators won't show the triangle, and you'll need a different approach. For the cases where it does work, this is the fastest no-formula fix on the planet. Two clicks. The Excel TEXT function works the opposite direction — turning numbers into formatted text, which can be useful when building reports.

If the green triangle has gone missing on a sheet where you expected it, check File → Options → Formulas → Error Checking Rules. Make sure "Numbers formatted as text or preceded by an apostrophe" is ticked. Some IT departments push corporate templates with that rule disabled, killing the diagnostic. Re-enable it and the triangles come back on the next workbook open. The same dialog controls a dozen other automatic checks — formulas inconsistent with neighbors, formulas referring to empty cells, unlocked cells in protected ranges. Worth a one-time review when you set up a fresh Excel install.

Currency symbols and thousand separators trip up most conversion attempts. A cell showing $1,234.56 as text won't yield to plain VALUE(). Two clean fixes: NUMBERVALUE() with explicit separator arguments, or chained SUBSTITUTE() calls to strip the symbols first. Something like =SUBSTITUTE(SUBSTITUTE(A1,"$",""),",","")*1 nukes both the dollar sign and the comma, then multiplies by 1 to convert. For European data with comma-as-decimal, =NUMBERVALUE(A1,",",".") tells Excel the comma is the decimal mark and the period is the thousands separator. Match the args to your data's locale, not your system's.

Hidden non-printable characters from web scrapes deserve their own mention. Copy a number from a webpage and paste it into Excel — looks fine. Try to add it to another number — boom, error. The cell often contains a Unicode non-breaking space (CHAR 160) or some other invisible junk. The CLEAN() function strips most control characters; TRIM() handles whitespace.

Combine them with SUBSTITUTE: =VALUE(CLEAN(TRIM(SUBSTITUTE(A1,CHAR(160),"")))). Ugly, but bulletproof for messy imports. For repeated cleanup, drop the whole expression into a custom LAMBDA in modern Excel and call it like a built-in function. The count cells with text guide covers the complementary problem — identifying text cells in a range.

For one-off massive jobs, VBA wins. A short macro loops a range and forces each cell back to numeric. Open the VBA editor with Alt+F11, insert a module, and paste this: For Each c In Selection: If IsNumeric(c.Value) Then c.Value = CDbl(c.Value): End If: Next c. Select your range first, then run the macro.

CDbl() converts the string into a double-precision number and assigns it back, replacing the text. Val() works similarly but stops at the first non-numeric character — handy for stripping suffixes but risky if your data has weird formatting. For bulk in-place conversion across hundreds of thousands of cells, this approach finishes in seconds.

Power Query deserves serious attention for any data that refreshes on a schedule. Load your CSV through Data → Get Data → From File → From Text/CSV. In the Power Query editor, find your text-formatted column, right-click the header, choose Change Type → Decimal Number or Whole Number. Power Query remembers the transformation. Every time you refresh the source, the conversion applies automatically.

No formulas to maintain. No Paste Special dance. Robust against new rows. The setup costs five minutes; the payoff is years of friction-free imports. For accountants pulling weekly bank exports or analysts hitting quarterly database dumps, Power Query is the answer. The convert Excel to PDF walkthrough covers a different export need, but the Power Query mindset transfers — set up once, run forever.

Performance matters when ranges get large. VALUE() in a helper column across 100,000 rows recalculates every time the workbook updates — that drags. Paste Special Multiply runs once and leaves no formulas behind. VBA is fastest for one-time bulk jobs because it bypasses Excel's calc engine entirely.

Power Query loads and transforms in a separate engine, so it doesn't slow live calc. Text to Columns is instant but limited to one column at a time. For a small range, any method works. For ranges over 50,000 rows, prefer Paste Special, VBA, or Power Query. You'll feel the difference. The wrap text in Excel guide covers another formatting need that's quick but useful.

Memory usage differs too. A workbook full of text-formatted numbers uses more RAM than the same data stored as numbers — text is bytes per character, numbers are eight bytes flat. Converting a column of 200,000 financial values from text to number can shrink the file by 30 percent or more. Save, close, reopen — the calc tree rebuilds faster, autosave intervals shorten, the workbook feels snappier. If you've ever wondered why a colleague's spreadsheet runs sluggish, look first at columns of numeric-looking data that's actually text.

Sometimes you absolutely should not convert. Zip codes with leading zeros — convert and you lose the zeros, turning 02134 into 2134. Phone numbers, account numbers, SKUs, product IDs — same problem. Keep these as text on purpose. Excel doesn't need them as numbers because you'll never sum or average them.

If your spreadsheet has a column of zips, format the column as Text before pasting data and you'll preserve the zeros. For mixed columns where some entries should be numeric and others should stay text, use a helper column with logic like =IF(LEN(A1)=5,A1,VALUE(A1)) to preserve five-digit zips while converting other entries.

Times and dates have their own conversion functions. Text like 05/11/2026 won't yield to VALUE() — use DATEVALUE() instead. Text like 14:35:00 needs TIMEVALUE(). Combined date-time strings convert through nested calls: =DATEVALUE(LEFT(A1,10))+TIMEVALUE(RIGHT(A1,8)) for a string like 05/11/2026 14:35:00.

After conversion, apply a date or time format from Format Cells so the result displays as you expect — otherwise you'll see Excel's serial number representation, which looks like 45788 instead of a recognizable date. The convert PDF to Excel guide covers the upstream problem of extracting tabular data from PDFs, which often produces text-formatted numbers you'll then need to convert.

One more scenario worth flagging — pivot tables built over text-formatted numeric columns. Pivot tries to auto-detect the type, often fails, and presents what should be a Sum column as a Count instead. Symptom: your pivot shows row counts where you expected dollar totals. Fix: convert the source column first, then refresh the pivot.

Pivot won't reinterpret existing values, so a convert-then-refresh sequence is required. If the source data updates regularly, route it through Power Query and set the column type there — the pivot reads from the loaded data and the conversion sticks across refreshes. Saves daily headache for analysts who run weekly reports on imported figures.

Power Query specifics are worth a closer look since it's the most under-used tool for this problem. Open Power Query through Data → Get Data → From File for CSVs or From Other Sources for databases. The query editor shows your raw data. Click a column header and inspect the type icon to the left of the column name — ABC means text, 123 means number. Click the icon and pick the type you want.

Power Query inserts a Changed Type step in the Applied Steps panel on the right. Close & Load drops the cleaned data into Excel. Every refresh re-runs the same steps. For locale issues, use Change Type → Using Locale to specify how the source represents decimals and thousands. Refresh the source data and Power Query handles everything automatically.

After conversion finishes, you'll usually want to re-apply formatting. Paste Special Multiply strips custom formats, so a column of dollar amounts becomes plain numbers without the dollar sign. Re-apply via Ctrl+1 (Format Cells) — pick Currency, Number, or Custom as needed. Set decimal places, thousands separator, and any currency symbol. For consistent formatting across multiple workbooks, copy the formatted cell and use Paste Special Formats to apply the look without affecting values. ROUND() handles precision issues from imports — bank statements often carry 15 decimal places that you don't want. =ROUND(A1,2) trims to cents.

A subtle Power Query trick — the Replace Errors step. If your column has a few non-convertible cells mixed in with thousands of clean ones, set the column type to Number, accept the error indicators on bad rows, then right-click the column and choose Replace Errors. Enter null or zero.

Power Query swaps every error with your replacement, and the rest of the conversion proceeds clean. Beats hunting through 50,000 rows looking for that one weird entry that broke the type cast. The same approach scales to any messy import scenario where most data is clean and a few outliers cause grief.

Errors during conversion give you clues about what went wrong. #VALUE! means the text contains characters that can't parse as a number — currency symbols, letters, weird spaces. Wrap the cell with CLEAN() and TRIM() first, or use SUBSTITUTE to remove the offending characters.

A result of 0 often means the original cell was empty or contained only whitespace. #DIV/0! happens when a converted value flows into a division step later — the issue isn't the conversion, it's the downstream formula. #NAME? means a function name is wrong, often a typo or a function unavailable in your Excel version (like NUMBERVALUE before Excel 2013).

Version differences matter more than people expect. Excel 365 and Excel 2021 ship with dynamic arrays — drop =VALUE(A1:A100) in a single cell and it spills converted results down a hundred rows. No drag-down needed. Excel 2019 and earlier need the formula in every cell or wrapped in a legacy array entry (Ctrl+Shift+Enter). NUMBERVALUE() arrived in 2013, so workbooks targeting 2010 or earlier need SUBSTITUTE chains instead.

Excel for Mac matches Windows feature-for-feature in modern versions but lags on edge cases like Power Query refresh from certain database sources. Google Sheets has its own equivalents — VALUE() works identically, and TO_NUMBER() handles the same job. Most patterns transfer across spreadsheet apps with minor syntax tweaks.

Best practice — never convert in place without a backup. Copy your raw data to a second sheet or a backup workbook first, then convert on the live copy. If something goes sideways (and the first time you try Paste Special Multiply on the wrong range, something will), you can revert. Even better — keep the source data in its original text form, do conversions in a helper column with VALUE or NUMBERVALUE, and reference the helper column in downstream formulas.

You preserve the original, you can audit the conversion, and you can change the conversion logic without re-importing. For repeatable workflows, Power Query gives you all of this automatically because the original source file stays untouched and every transformation is logged in Applied Steps.

The bottom line — text-formatted numbers are a routine nuisance with at least seven distinct solutions, and each method earns its place in your toolkit for different volumes and contexts. Pick the one that matches your volume, frequency, and locale. Paste Special for one-off in-place fixes. VALUE or double-negative inside formulas. Text to Columns for single columns. Power Query for anything that refreshes. VBA when you want to script it.

The green-triangle click for the easiest cases. NUMBERVALUE for international data with locale-specific separators. Master two or three of these and you'll never see a SUM return zero again — at least, not because of text-formatted numbers. Test on small samples first. Back up before bulk operations. Verify with a sum at the bottom of the column. Confirm cell types with ISNUMBER. Build the habits and the messy CSV imports stop costing you afternoons. Document your conversion approach in a worksheet note so the next person to open the file knows what happened to the data. Future-you will thank present-you.