Excel Extract Number From Text String

Excel Extract Number from Text String: A complete walkthrough

Extracting numbers from mixed text strings is a frequent requirement when cleaning data, preparing reports, or performing calculations in Excel. Because of that, whether you are dealing with product codes, addresses, or user‑generated notes, knowing how to pull out numeric values efficiently can save hours of manual work. This article walks you through several reliable techniques—from simple built‑in formulas to advanced VBA and Power Query solutions—so you can choose the method that best fits your data size, complexity, and comfort level Took long enough..

Why Extract Numbers from Text in Excel?

Real‑world datasets rarely arrive in a perfectly structured format. Common scenarios where you need to excel extract number from text string include:

• Invoice numbers embedded in descriptions (e.g., “Order #12345 – shipped”).
• Mixed alphanumeric codes such as SKUs (“ABC‑987‑XYZ”).
• Addresses containing house numbers (“123 Main St, Apt 4B”).
• User comments where ratings or quantities are typed freely (“Score: 8/10”).
• Financial notes where amounts appear with currency symbols (“$2,350.00”).

Extracting those numbers enables further calculations, sorting, filtering, or integration with other systems.

Overview of Extraction Methods

Below, each method is explained step‑by‑step with concrete examples.

Method 1: Using Basic Text Functions

When the number’s position relative to delimiters is predictable, classic text functions suffice.

Example: Extract a 5‑digit order ID that always appears after “Order #”

Explanation

1. FIND("#", A2) locates the hash symbol.
2. Adding +1 moves the start point to the first digit after the hash.
3. MID(..., 5) pulls exactly five characters, assuming a fixed length.

If the number length varies, replace the hard‑coded 5 with LEN(A2)-FIND("#", A2) or use a combination of SEARCH and LOOKUP.

When to Use: Small lists, consistent formatting, no need for dynamic arrays Small thing, real impact..

Method 2: SUBSTITUTE + TEXTJOIN (Array Formula)

This technique strips all non‑numeric characters, leaving only digits (and optionally a decimal point or minus sign).

Formula (Excel 365/2021)

=TEXTJOIN("", TRUE, IFERROR(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), ""))

Steps

1. ROW(INDIRECT("1:"&LEN(A2))) creates an array of positions 1 through the string length.
2. MID(A2, ..., 1) extracts each character.
3. -- attempts to convert the character to a number; non‑numeric characters produce an error.
4. IFERROR(..., "") replaces errors with blank strings.
5. TEXTJOIN("", TRUE, ...) concatenates the remaining numeric characters.

Result: From “Price: $2,350.00” you get 235000. To preserve the decimal point, adjust the formula:

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) OR MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)=".", MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), ""))

When to Use: You need to pull all numbers from a string, regardless of position, and you are comfortable with array formulas (or have dynamic array support) And that's really what it comes down to. Which is the point..

Method 3: VBA with Regular Expressions

For repetitive tasks or complex patterns (e.Here's the thing — g. , extracting numbers with optional signs, decimals, or thousands separators), a custom VBA function offers flexibility.

Step‑by‑Step

1. Press ALT+F11 → Insert → Module.
2. Paste the following code:

Function ExtractNumber(ByVal txt As String) As String
    Dim regEx As Object
    Set regEx = CreateObject("VBScript.RegExp")
    With regEx
        .Pattern = "-?\d+(\.\d+)?"
        .Global = True
        .IgnoreCase = True
    End With
    If regEx.Test(txt) Then
        ExtractNumber = regEx.Execute(txt)(0)   'returns first match
    Else
        ExtractNumber = vbNullString
    End If
End Function

3. Close the VBA editor.
4. In a worksheet, use =ExtractNumber(A2).

Explanation

• The pattern -?\d+(\.\d+)? matches an optional minus sign, one or more digits, and an optional fractional part.
• .Global = True would allow finding all matches; we return the first ((0)) for simplicity, but you can loop through Matches to collect every number.

When to Use: You need a reusable function, want to handle varied formats, or plan to apply the logic across many workbooks.

Method 4: Power Query (Get & Transform)

Power Query excels at cleaning large tables

Method 4: Power Query (Get & Transform)

Power Query excels at cleaning large tables because it lets you apply transformations once and then refresh the query whenever the source data changes. Extracting numbers from a mixed‑text column can be done entirely within the query editor, without writing any Excel formulas Turns out it matters..

Step‑by‑Step

1. Load the data into Power Query

   • Select any cell in your table → Data tab → From Table/Range.
   • Confirm the range and click OK to open the Power Query editor.

2. Add a custom column that isolates numeric characters

   • Go to Add Column → Custom Column.

   • Name the column, e.g., ExtractedNumber.

   • Use the following M expression (it keeps digits, an optional decimal point, and an optional leading minus sign):

     let
         src = Text.Trim([YourColumnName]),
         // Build a list of characters that are either digits, a dot, or a minus sign
         chars = List.Select(Text.ToList(src), each _ = "." or _ = "-" or Character.FromNumber(_) >= 48 and Character.FromNumber(_) <= 57),
         // Re‑assemble the list into a text string
         result = Text.
     
     

   • Replace [YourColumnName] with the actual name of the source column (Power Query will show it automatically when you start typing).

   • Click OK.

3. Convert the extracted text to a proper number type

   • With the new column selected, go to Transform → Data Type → Decimal Number (or Whole Number if you know there are no fractions).
   • Power Query will automatically turn blanks or non‑numeric strings into null; you can later replace those with zeros or leave them as missing values, depending on your analysis needs.

4. Optional: Keep only the first numeric block
   If your strings sometimes contain multiple numbers (e.g., “Item 12 – Batch 34”) and you only want the first occurrence, adjust the custom column expression to use Text.Select with a regular‑expression‑like pattern via Text.Replace:

   let
       src = Text.Day to day, trim([YourColumnName]),
       // Replace everything that is NOT a digit, dot, or minus with a delimiter, then split and take the first part
       cleaned = Text. And replace(src, each not (_ = ". " or _ = "-" or (Character.FromNumber(_) >= 48 and Character.FromNumber(_) <= 57)), "|"),
       parts = Text.Split(cleaned, "|"),
       first = List.
   
   - This approach treats any non‑numeric character as a separator, extracts the resulting segments, and returns the first one.
   
   

5. Load the query back to Excel

   • Click Close & Load (or Close & Load To… if you want to place the output on a specific sheet).
   • Your worksheet now contains a fresh column with the cleaned numbers, and any future updates to the source table can be refreshed with a single click (Data → Refresh All).

When to Use Power Query

• You are working with large datasets (hundreds of thousands of rows) where formula‑based solutions would slow down recalculation.
• You need a repeatable, auditable transformation that can be applied to multiple files or shared with colleagues via a .pbix or .xlsx query connection.
• You prefer a no‑formula, UI‑driven approach but still want the flexibility to tweak the logic (e.g., handling thousands separators, currency symbols, or scientific notation) without touching VBA.

Conclusion

Extracting numbers from mixed‑text strings is a common data‑cleaning task, and Excel offers several complementary routes depending on your comfort level and the scale of the problem:

• Formula‑based methods (SUBSTITUTE + TEXTJOIN, MID/ROW arrays) are quick for ad‑hoc checks and work well in any recent version of Excel without leaving the grid.
• VBA with RegExp shines when you need a reusable function that can handle complex patterns, signs, decimals, and multiple matches across many workbooks.
• Power Query provides the most reliable, scalable solution for large tables or recurring refreshes, letting you define the extraction logic once and apply it automatically whenever the source data changes.

Choose the technique that matches your workflow: formulas for simple, one‑off tasks; VBA for custom,