Convert Text to Number in Excel: VALUE, Paste Special & More

You paste a column of numbers from a website into Excel — and nothing adds up. SUM returns zero. The cells look fine, the digits are right there, but every formula treats them like text. The little green triangle in the top-left corner of each cell is the giveaway. Excel's quietly telling you, "These aren't numbers, friend."

This happens constantly. Data lands as text after a CSV import, a paste from a PDF, an export from accounting software, or a scrape of an HTML table. Sometimes the culprit is a leading apostrophe. Sometimes invisible characters like non-breaking spaces. Sometimes a thousands separator the locale doesn't recognize. The result is always the same — your numbers won't behave like numbers until you convert them.

Good news is, there are about eight reliable ways to fix this, and most take under thirty seconds. This guide walks through every method, from the one-click error indicator to the VALUE function, paste-special multiply, Text to Columns, Find & Replace tricks, and Power Query's Number.From for stubborn cases. Pick whichever fits your situation; bookmark the rest.

Before fixing anything, confirm the cells really are text. The fastest tell is alignment — by default Excel left-aligns text and right-aligns numbers. A column of "numbers" hugging the left edge of cells is almost always text. The second tell is that little green triangle in the top-left of each cell. Click one and you'll see a yellow caution icon with a dropdown that says "Number Stored as Text".

You can also test with a formula. In any empty cell, type =ISNUMBER(A1). If A1 is a real number, it returns TRUE. If TRUE doesn't come back, the cell holds text. Another quick check: =A1+0. If A1 is text, you'll get the #VALUE! error. If it's a number, the addition succeeds and gives back the original value.

One more thing worth checking — is the entire column text, or just some cells? Type =SUMPRODUCT(--ISTEXT(A2:A100)). This counts how many cells in the range are text. If it returns a number greater than zero, you've got mixed data and need a conversion pass.

Why does this matter? Because the right fix depends on what's wrong. A whole column of imported strings needs a different approach than three orphaned text cells in a sea of real numbers. Find the problem first, then pick the tool.

The single easiest fix is the green triangle itself. Excel knows the cells are text and offers a one-click conversion. Most people miss it because they don't realize the triangle is interactive.

Select the cells with the green triangles. Look just to the left of the selection — you'll see a yellow diamond with an exclamation mark. Click it. A dropdown opens with several options. Pick Convert to Number. Done. The triangles disappear, alignment shifts to the right, and your formulas start working.

This works on single cells, ranges, or entire columns. Select an entire column, click the yellow icon once, and Excel converts every text-formatted number in the selection. The whole operation takes about two seconds.

What if the yellow icon doesn't appear? Two reasons usually. First, error checking might be disabled — go to File > Options > Formulas and verify "Enable background error checking" is on, plus "Numbers formatted as text or preceded by an apostrophe" is checked under Error checking rules. Second, the cells might not technically be text — they could be formatted as text but contain real numbers, or contain non-numeric characters like spaces or symbols that the indicator doesn't flag. In those cases, jump to the next methods.

When the green triangle approach doesn't work, the VALUE function is your next stop. It's purpose-built for this exact problem — converting a text string that looks like a number into an actual number.

Syntax is dead simple: =VALUE(A1). Point it at the text cell, and you get back the numeric equivalent. "123" becomes 123. "45.67" becomes 45.67. "$1,234.50" becomes 1234.5. VALUE strips currency symbols and thousands separators in most locales without complaint.

To convert a whole column, put the formula in an empty column next to your data. Say column A is text and column B is empty. In B1 type =VALUE(A1), then drag the fill handle down to the last row. Column B now holds real numbers. Copy column B, then paste it back over column A as values (Paste Special > Values) and delete the helper column.

VALUE returns #VALUE! if the text isn't recognizable as a number — like if there's a stray letter or an unusual symbol. Wrap it in IFERROR if you need to handle that gracefully: =IFERROR(VALUE(A1),0) returns 0 for any unconvertible cell, or use "" to leave it blank.

VALUE has a more flexible sibling — NUMBERVALUE. Where VALUE relies on your system's locale for decimal and thousands separators, NUMBERVALUE lets you specify them explicitly. Useful when you're handling European-formatted numbers in a US-locale Excel, or vice versa.

Syntax: =NUMBERVALUE(text, [decimal_separator], [group_separator]). To parse "1.234,56" (a comma decimal, period thousands — common in Germany, France, Italy) in a US-locale spreadsheet, use =NUMBERVALUE(A1,",","."). Returns 1234.56 as a proper number. Excel doesn't care that the locale is wrong; you told it what the separators mean.

The function also strips multiple decimal points, percent signs (multiplies the result by 0.01 — so "50%" becomes 0.5), and various currency symbols. NUMBERVALUE("$1,234.50",".",",") returns 1234.5 in any locale.

If you skip the optional arguments, NUMBERVALUE behaves like VALUE — uses your system locale. The reason to use NUMBERVALUE over VALUE is when you don't trust the locale match. Files from international clients, shared workbooks across regions, CSVs from European systems — NUMBERVALUE is the safer choice.

Paste Special Multiply is the trick power users reach for first. It converts an entire range to numbers with two paste operations and zero formulas. Cleaner than the VALUE approach because you don't end up with helper columns.

Here's the move. Type 1 in any empty cell. Press Ctrl+C to copy it. Now select the range of text-formatted numbers you want to convert. Right-click and pick Paste Special, or press Ctrl+Alt+V. In the dialog, under the Operation section, check Multiply. Click OK.

Excel multiplies every cell in the selection by 1. Since 1 × any number = that number, the values don't change — but the multiplication forces Excel to evaluate them as numbers. Text strings get cast to numeric, alignment shifts right, and the green triangles vanish. Delete the 1 from the helper cell and you're done.

This method handles entire columns instantly. It also works inside Tables, across non-contiguous selections (hold Ctrl to pick disjoint ranges), and won't break existing formatting on cells that were already numbers. The math is a no-op for real numbers but a forced cast for text — clean side-effect free conversion.

One gotcha: cells that contain non-numeric characters (letters, symbols, weird whitespace) will throw #VALUE! after the multiply. Filter those out first or clean them with TRIM and SUBSTITUTE before running paste-special.

Text to Columns is Excel's secret weapon for bulk conversion. It's labeled for splitting text but doubles as the fastest cleanup tool when paste-special multiply doesn't work — usually because of stubborn formatting that needs to be parsed.

Select the column of text numbers. Go to Data > Text to Columns. The wizard opens. On step 1, leave Delimited selected and click Next. On step 2, uncheck all delimiter boxes and click Next. On step 3, pick the column data format. For numbers, leave it on General. Click Finish.

That's it. Excel re-parses every cell as if you'd just typed it in. Text-formatted numbers become real numbers. Dates that were stuck as text become real dates (if you pick Date on step 3 with the right format). The conversion is instant for thousands of rows.

Why does it work? Text to Columns forces Excel to re-evaluate each cell's content using whatever format you specify in step 3. It's basically a bulk "retype this cell" operation. Cleanest method for large datasets where you don't want to add helper formulas or temporary cells.

The limit: Text to Columns works on a single column at a time. If you've got fifteen columns of text-formatted data, you'll need to run it fifteen times — or write a tiny macro. Still faster than most alternatives once you know the keystrokes.

Sometimes the numbers are text because of characters you can't see. A trailing space, a non-breaking space (CHAR(160)) common in HTML pastes, or invisible Unicode whitespace. Excel sees these as text content, refuses to treat the cell as numeric, and no amount of VALUE or paste-special multiply will fix it until you remove the offending characters.

Find & Replace is your weapon. Press Ctrl+H. In the Find what box, type a single space. Leave Replace with empty. Click Replace All. Regular spaces are gone. Now run any conversion method — paste-special multiply, Text to Columns, or VALUE — and the cells should convert.

For non-breaking spaces, regular Find & Replace won't catch them because they're a different character. The fix is a formula: =VALUE(SUBSTITUTE(A1,CHAR(160),"")). CHAR(160) is the non-breaking space; SUBSTITUTE strips it; VALUE then converts. You can chain multiple SUBSTITUTE calls if there are several hidden characters.

Other common offenders: CHAR(9) (tab), CHAR(10) (line feed), CHAR(13) (carriage return). The combo formula =VALUE(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),"")))) wraps all the cleanup into one — TRIM kills regular extra spaces, CLEAN removes non-printable characters below CHAR(32), SUBSTITUTE handles CHAR(160), and VALUE finishes the conversion.

For really stubborn cases — Excel 365 users can use REGEX to strip anything non-numeric: =VALUE(REGEXREPLACE(A1,"[^0-9.\-]","")). Strips every character that isn't a digit, period, or minus sign. Aggressive but effective when the text has weird symbols mixed in.

Power Query is overkill for ten cells but perfect when you're pulling data from a CSV, web table, or database repeatedly and need conversions baked into the import. Build the query once, refresh forever.

Go to Data > Get Data and pick your source — From Text/CSV, From Web, From Database, whatever fits. The Power Query Editor opens with a preview of your data. Right-click the column that's loading as text, pick Change Type > Whole Number or Decimal Number. Power Query attempts the conversion and shows you any errors in a separate flagged row count.

For more control, use the M-language function Number.From. In the formula bar, replace the auto-generated Type.Transform line with something like = Table.TransformColumns(Source, {{"Amount", each Number.From(_)}}). This converts the Amount column to numeric using Power Query's parser, which is more forgiving than Excel's native VALUE — it handles odd locale formats, mixed separators, and even some currency notations.

Click Close & Load. The query runs and returns clean numeric data to your spreadsheet. The advantage is repeatability — every time the source file updates, hit Refresh All and the conversions run again automatically. Set it and forget it.

Excel 365 and 2021 users have access to LAMBDA and array formulas that simplify mass conversion. Instead of dragging VALUE down a thousand rows, use =VALUE(A1:A1000) in a single cell and Excel spills the converted values automatically — no helper columns, no dragging.

Same trick works with NUMBERVALUE for locale-specific data: =NUMBERVALUE(A1:A1000,",","."). The result spills down one cell per source row. If the source column changes length, the spill range adjusts. Live conversion baked into the formula.

For files that mix text and numeric data, wrap in IFERROR to handle the bad cases: =IFERROR(VALUE(A1:A1000),A1:A1000). Where conversion fails, the original text passes through unchanged. Wrap that in a SUBSTITUTE chain first if you need to strip CHAR(160) or other invisible characters.

One more pattern worth bookmarking — converting an entire table of mixed text and numbers using LAMBDA: =BYCOL(A1:E1000, LAMBDA(col, IFERROR(VALUE(col), col))). Loops over every column, applies VALUE, and returns the original value if conversion fails. Single formula, whole-table cleanup. Very 2026.

VBA macros are the right answer when you've got repeating workflows — like a weekly accounting export that always lands as text. Record the cleanup once, then run it with one click.

Press Alt+F11 to open the VBA editor. Insert a new module via Insert > Module. Paste in this simple converter:

Sub TextToNumber()
    Dim rng As Range
    Set rng = Selection
    rng.NumberFormat = "General"
    rng.Value = rng.Value
End Sub

Save the macro. Back in Excel, select your range and run the macro (Alt+F8, pick TextToNumber, click Run). The macro resets the cell format to General and reassigns the values to themselves — which forces Excel to re-evaluate each cell as numeric where possible.

For files where you need more aggressive cleanup — stripping CHAR(160), trimming spaces, handling locale — extend the macro: rng.Value = Application.Evaluate("VALUE(TRIM(CLEAN(SUBSTITUTE(" & rng.Address & ", CHAR(160), "))))"). Gets uglier quickly, but you only write it once and reuse forever.

Save the workbook as a macro-enabled file (.xlsm). Add the macro to your Quick Access Toolbar so it's one click from anywhere — perfect for repetitive cleanup of incoming data.

Dates that come in as text are their own special pain. They look like dates but won't sort chronologically, won't subtract from each other, and break any function that expects a real date serial number.

The cleanest fix is DATEVALUE — VALUE's date-specific cousin. =DATEVALUE(A1) converts "3/14/2026" to the serial number 46180, which you then format as a date. Combine with TIMEVALUE for datetime stamps: =DATEVALUE(LEFT(A1,10))+TIMEVALUE(RIGHT(A1,8)) for an "MM/DD/YYYY HH:MM:SS" string.

Or use Text to Columns with step 3 set to Date and the correct format (MDY, DMY, YMD) — works as well for dates as for numbers. Quick, no formulas needed.

One real-world example. You inherit a CSV of last quarter's invoices. 800 rows, columns for invoice number, customer, amount, date. SUM on the Amount column returns zero. ISNUMBER says everything is text. Alignment is left-justified.

Diagnosis takes ten seconds. Fix takes about a minute. Select the Amount column, click the yellow caution icon's "Convert to Number" option — and watch the column right-align. SUM now works. The green triangles are gone. Repeat for the Date column using DATEVALUE in a helper column or Text to Columns with Date format.

If the yellow icon didn't appear, run paste-special multiply by 1, or drop in a VALUE formula and copy-paste the results back over the original. Either way, the whole cleanup takes 60 seconds. Then your pivot tables work, your charts plot correctly, and your accounting team stops asking why the totals don't match.

That's the rhythm — diagnose with ISNUMBER, pick the fix that matches your situation, run it, verify, move on.