Extract Numbers From Text

What it does

You have a column of text cells with numbers buried inside them: order codes in email subjects, weights in product titles, IDs in free-text comments. The macro scans every cell in your selected column and writes the numbers it finds to the column to the right. If a cell has multiple numbers, they're joined with commas. It runs offline with no setup beyond importing the .bas. Excel's built-in TEXTJOIN and SEARCH formulas can do this, but you'd write a small monster that breaks the moment a cell has two numbers instead of one.

Real-world example

A purchasing analyst at a mid-sized consumer electronics distributor in Hyderabad gets a CSV from the sales team every Monday: 600 rows, each one a customer-service email subject like "Replace order 49281 from vendor 71 urgent." She needs the order numbers in their own column to look them up in the ERP.

Her workaround so far: a column of MID and FIND formulas that work for "Replace order 49281" but break the moment a subject says "Replace orders 49281 and 49283 from vendor 71." She'd then go through and fix each odd row by hand. About 20 minutes per Monday spent on a task that has nothing to do with purchasing.

She runs the macro on column B (the subject text). Every order number lands in column C, comma-separated where two appear. She can copy column C back into the ERP lookup tool without touching a formula.

On a 600-row sheet, the macro scans and writes in about two seconds. The bigger win is that she stops fighting MID and FIND every Monday. The macro doesn't care how many numbers a cell has, where they sit, or whether they're decimals or integers. Free, offline, no signup. Works the same on her Windows laptop and the team Mac.

How to use

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

1. Make sure the column to the right of your text column is empty. The macro writes results there.
2. Select the single column of text cells you want to scan. Empty cells are skipped automatically.
3. Press Alt + F8 (Windows) or Option + F8 (Mac), pick ExtractNumbersFromText, click Run.
4. If the right-side column has data, confirm the overwrite when prompted.
5. The macro reports how many cells it scanned and how many produced numbers.

FAQ

Does it handle decimal numbers?

Yes. Periods inside a sequence of digits are kept as decimal points, so "Weight 1.5 kg" writes "1.5". A trailing period that looks like a sentence end ("Order 12345.") is dropped, so you get "12345" not "12345.". Negative numbers are not handled. The minus sign is treated as a non-digit, so "-50" becomes "50".

What about commas in numbers like "$1,234.56"?

The comma is treated as a separator between numbers, not a thousands separator. "$1,234.56" produces "1, 234.56" in the result cell. If your data uses commas as thousand separators, run Find and Replace Across All Sheets first to strip the commas, then run this macro.

Does it skip cells that already have formulas?

Yes. If a source cell contains a formula, the macro leaves it alone and writes nothing to the right of it. Only literal text is parsed. This protects spreadsheets where the source column is itself a calculated value you don't want disturbed.

Will it overwrite my data in the column to the right?

Only if you say yes when prompted. The macro checks the right-side column before running and asks for confirmation if it finds any non-empty cells. If you cancel, nothing is written. Always work on a copy of the file if it's the only copy.

Can I undo it?

Yes, Ctrl + Z (Windows) or Cmd + Z (Mac) immediately after running undoes the writes in one step. The macro runs entirely offline. Your data never leaves your machine.