Convert Text to Numbers

What it does

You SUMIF a column of dollar amounts and Excel proudly returns zero. Or your VLOOKUP fails to match 1234 against 1234 because one is a number and the other is text. The macro walks your selection, strips currency symbols, commas, leading apostrophes, and accounting parentheses, then writes each cell back as a real number with General formatting. Excel's built-in "Convert to Number" green-arrow trick only handles one case (apostrophe-prefixed). This handles the messy real-world ones, including the parens-as-negative convention.

Real-world example

Picture a senior accountant at a 6-location restaurant group, the day after the quarter closes. Her team exports a payables aging report from the accounting system. The export comes through with amount columns formatted as $1,234.56 and (789.00) for credits, and somewhere along the pipeline it picked up text formatting. So when she pivots the data to summarize by vendor, the totals come back as zero.

She knows the trick. Click the green warning triangle, "Convert to Number." Except the green triangle only shows on apostrophe-prefixed cells, not on the ones with dollar signs. So she runs Find/Replace four times: remove $, remove ,, change ( to -, change ) to nothing. Now they're plain text strings of digits. Then she selects the column, types 1 in an empty cell, copies it, Paste Special, Multiply. Now they're numbers. Total elapsed time: about 12 minutes per export, longer if she fat-fingers a Find/Replace step and has to undo.

With this macro, she selects the amount column and runs it from Alt+F8. The macro turns 1,847 cells from text into real numbers in about 2 seconds, preserves the negatives from the parentheses, and tells her exactly how many it converted versus skipped (the few cells with notes like "see vendor" get skipped, not silently destroyed).

On a typical 2,000-row aging report with currency formatting, expect about 11 minutes saved per cleanup. Across a fiscal year of weekly reports that's roughly 9 hours she gets back, plus the smaller win of not accidentally breaking the negative numbers.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select the range of cells you want to convert.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick ConvertTextToNumbers, click Run.
3. The macro converts every cell that looks numeric and tells you how many it changed and how many it skipped.

That's it. No prompts, no options, no chance to misconfigure.

FAQ

What text gets converted and what gets left alone?

Anything that, after stripping $ £ € ¥ ₹ and commas and spaces, parses as a number gets converted. $1,234.56 becomes 1234.56. (789) becomes -789. Cells containing letters or other symbols (like N/A or see vendor note) are left alone. The popup at the end tells you the converted count and the skipped count separately.

Will it mess up real numbers that are already formatted as currency?

No. The macro only touches cells that are stored as text or have a leading apostrophe. Cells already stored as numbers (with $ formatting applied via cell format, not as text) are skipped. Your formatting choices for real numbers are untouched.

What about percentages?

Percentages are skipped. The % symbol triggers the "this is not a clean number" path, so 25% stays as text. That's intentional. If we silently divided by 100, we'd quietly corrupt sheets where someone meant the literal text 25%.

Will it work on a column with mixed valid and invalid entries?

Yes, that's the common case. The macro processes each cell independently. The numeric ones get converted; the text comments and N/A entries stay as text. The summary tells you both counts so you can spot-check the skipped ones.

Can I undo it?

Yes, hit Ctrl/Cmd + Z right after running. As always with macros, work on a copy of your data first if you cannot afford a bad edit. Your data never leaves your machine. The macro runs entirely offline, and there are no API calls.