How to Convert Text to Numbers in Excel (Five Real Methods)

The SUMIF returns zero problem

You imported a CSV. Or you exported a report from QuickBooks. Or someone sent you a sheet from a web page. Now your =SUMIF(A:A, "Acme", B:B) returns 0 even though there are clearly numbers in column B that match. The reason is almost always the same: those "numbers" are stored as text.

This is one of the most common Excel data bugs and one of the most fixable. Here are five ways to fix it. The right one depends on whether you're cleaning once or every week and whether the text is just numbers or has currency symbols and commas.

TL;DR — Key takeaways

• The fast fix for apostrophe-prefixed text numbers ('1234) is the green-arrow trick: select, click the warning indicator, "Convert to Number".
• For numbers with currency symbols, commas, or parentheses, the green arrow doesn't work. Use Paste Special > Multiply by 1.
• The =VALUE() formula converts one cell at a time but requires a helper column.
• Power Query handles recurring cleanups elegantly but is overkill for a one-off.
• A VBA macro handles all the messy real-world cases (currency, commas, parentheses-as-negatives) in one pass.

Why "numbers stored as text" happens

CSV imports and web copy-paste are the top causes. Specifically:

• CSV import where the column was tagged as text. Excel's import wizard sometimes assumes text for columns that contain mixed values.
• Numbers with leading apostrophes ('1234). The apostrophe is a force-text marker that Excel preserves.
• Currency-formatted numbers that came in as text strings ($1,234.56) instead of as numbers with currency formatting.
• Negative numbers in accounting parentheses ((789)). Excel doesn't auto-convert these to -789.
• QuickBooks, SAP, and other ERP exports that prepare numbers for human reading rather than machine processing.

The symptom is always the same: SUMIF returns 0, VLOOKUP returns #N/A, and a math operation on the cell either errors or treats the text as a string.

Method 1: The green-arrow trick (works for apostrophe-prefixed text)

If your text-numbers came in with a leading apostrophe, Excel shows a small green triangle in the top-left corner of each cell.

Steps:

1. Click any of the affected cells. A yellow warning icon appears.
2. Click the warning icon dropdown.
3. Select Convert to Number.

To fix multiple cells, select the range first, then click the warning icon on any cell in the selection. Excel converts all the apostrophe-prefixed text-numbers in the selection.

The catch: this only works for apostrophe-prefixed text. If your text-numbers are formatted as $1,234.56 (with currency symbols), no green arrow appears and this method does nothing.

Method 2: Paste Special > Multiply by 1

A clever trick that works for clean numeric text (no symbols, just digits and a decimal point).

Steps:

1. Type 1 in any blank cell.
2. Copy that cell (Ctrl+C).
3. Select your range of text-numbers.
4. Right-click > Paste Special.
5. Under "Operation", choose Multiply.
6. Click OK.

Multiplying any text-number by 1 forces Excel to coerce it into a real number. The cells convert in place, no helper column needed.

The catch: this fails on cells with currency symbols, commas, or parentheses. Excel can't multiply $1,234.56 by 1 because it doesn't know how to parse the string.

Method 3: The =VALUE() formula

For cell-by-cell conversion with a formula:

=VALUE(A2)

VALUE parses a text string and returns it as a number. It handles a few formatting niceties (it can parse 1,234.56 as 1234.56 in US locale) but not all (currency symbols, parentheses-as-negative).

Workflow:

1. Insert a helper column.
2. Drop =VALUE(A2) in the first row.
3. Fill down.
4. Copy the helper, Paste Special > Values back over the original.
5. Delete the helper.

For $1,234.56 with currency symbol, you'd need a more complex formula:

=VALUE(SUBSTITUTE(SUBSTITUTE(A2, "$", ""), ",", ""))

Now you're nesting SUBSTITUTE calls and remembering which characters to strip. Workable for a one-off, fragile for repeat use.

Method 4: Power Query column type change

Power Query (Data > Get & Transform > From Table/Range) lets you change a column's type from Text to Number. Power Query is smart enough to handle currency symbols and thousands separators in most locales.

Steps:

1. Load your data into Power Query.
2. Right-click the text-number column header > Change Type > Decimal Number (or Whole Number).
3. Power Query attempts the conversion. Errors are flagged so you can see which rows didn't parse.
4. Close & Load back to Excel.

This is the right answer for a recurring cleanup that processes new data weekly. The conversion logic is captured in a query that re-runs on refresh.

The catch: for a one-off check, building a Power Query is overkill, and the setup time is longer than just running a quick conversion.

Method 5: The free VBA macro (handles the messy cases)

Download Convert Text to Numbers. It's a free .xlsm with one macro that handles every common case in a single pass.

What it handles:

• Apostrophe-prefixed text numbers ('1234).
• Currency-symbol-prefixed text ($1,234.56, €999.50, ₹500).
• Thousands-separator text (1,234,567).
• Accounting-parentheses negatives ((789) → -789).
• Leading and trailing whitespace.

Steps:

1. Select the range of text-numbers.
2. Press Alt + F8, pick ConvertTextToNumbers, click Run.
3. Done. The popup tells you how many cells got converted and how many were skipped.

Cells with non-numeric content (like N/A or see notes) are left alone, not silently turned into 0 or an error.

Comparison: which method to use

Method	Handles apostrophe	Handles currency symbols	Handles parens negatives	Best for
Green-arrow trick	Yes	No	No	Quick one-off, apostrophe-only data
Paste Special multiply	Yes	No	No	Quick one-off, clean numeric text
=VALUE() formula	Yes	Partial	No	One-off with helper column, formula-friendly users
Power Query	Yes	Yes (most locales)	No	Recurring weekly cleanup
VBA macro	Yes	Yes	Yes	One-shot real-world data with mixed formatting

A common scenario: cleaning a QuickBooks export

You exported a payables aging report. The amount column has values like $1,234.56, $789.00, and (45.99) for credits. SUMIF returns 0.

1. Select the amount column.
2. Run Convert Text to Numbers.
3. The macro strips $ and commas, treats parentheses as negative signs, and writes back real numbers with General formatting.
4. SUMIF starts returning the right answers.

Total elapsed: about 3 seconds. The green-arrow trick wouldn't have worked on the dollar-prefixed values; Paste Special wouldn't have either. The macro handles the messy reality.

Frequently asked questions

Will it convert percentages?

No, the macro deliberately skips percentage strings like 25%. The reason: silently dividing by 100 to convert 25% to 0.25 would corrupt sheets where someone meant the literal text 25%. If you have a column of percentage strings you want as decimals, run a Find/Replace to remove the % first, then run the macro, then format the column as a percentage.

What about negative numbers with a minus sign instead of parens?

Handled. -1234 and (1234) both convert to -1234. The macro detects either form.

Does it work on cells already formatted as numbers?

Cells that are already real numbers (with currency formatting applied as a cell format, not as text) are skipped. The macro only touches cells whose value is a text string or has a leading apostrophe. The popup's "skipped" count includes these.

What if my European data uses comma as decimal separator?

The default macro uses the period (.) as the decimal separator. For data using comma as decimal (1.234,56 European format), edit the .bas source: in NormalizeNumberCandidate, swap which character gets stripped (comma vs period). It's a 2-line change.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running. As always, work on a copy of your data if it's the only copy.

What to do next

If your text-numbers came from a CSV import and SUMIF still returns 0, also check for whitespace contamination. If you're cleaning phone numbers stuck as text, use Clean Phone Numbers instead, which preserves the digits without forcing them numeric.