Extracting Numbers from Text in Excel Using TEXTJOIN and MID

Extracting scattered numbers from alphanumeric text strings in Excel is a persistent headache for data analysts. When consolidating financial reports from standard funding sources, you often inherit poorly formatted legacy databases that resist basic filtering. Fortunately, mastering an advanced formulaic approach grants you immediate processing efficiency and eliminates manual data entry.

Stipulation: This method requires Excel 2019 or Microsoft 365, as it relies on modern array-handling capabilities to evaluate individual characters. For example, analysts successfully use this technique to isolate clean numeric codes from mixed strings like "PROJ-9402-FY24".

Below, we break down how to combine TEXTJOIN and MID to automate this extraction seamlessly.

Data cleaning is one of the most common-and often frustrating-tasks in Excel. You will frequently encounter datasets where numbers are inconveniently fused with text. Whether you are dealing with cluttered product codes like "SKU-8849-Active", messy transaction descriptions like "Payment of $1050USD", or mixed serial numbers, isolating the numeric digits is crucial for performing calculations.

Historically, extracting numbers from a text string in Excel required writing complex VBA (Visual Basic for Applications) macros or constructing monstrously long formulas using nested SUBSTITUTE functions. However, with the introduction of modern Excel functions, particularly TEXTJOIN, you can accomplish this task using a highly elegant, readable, and dynamic formula. By combining TEXTJOIN with MID, LEN, and either SEQUENCE or ROW, you can easily parse any string, isolate the numbers, and piece them back together.

In this comprehensive guide, we will break down exactly how this formula works, explore variations for older and newer versions of Excel, and look at how to handle advanced scenarios like preserving decimal points and negative signs.

The Core Concept: How the Extraction Works

Before diving into the exact formulas, it helps to understand the underlying logic of character-by-character text extraction. To extract numbers from a mixed text string, Excel must perform the following steps behind the scenes:

1. Measure the string: Determine how many characters are in the target cell.
2. Deconstruct the string: Split the text into an array of individual, single characters.
3. Evaluate each character: Test each character to see if it is a number.
4. Filter out non-numbers: Keep the numeric characters and discard (or turn into blanks) the alphabetical characters and symbols.
5. Reconstruct the string: Glue the isolated numeric characters back together into a single cohesive value.

This is where our formula dynamic duo comes in. MID handles the deconstruction, and TEXTJOIN handles the reconstruction.

The Modern Solution: Excel 365 & Excel 2021

If you are using a modern version of Excel (Office 365 or Excel 2021 and newer), you have access to dynamic arrays and the SEQUENCE function. This makes the extraction formula remarkably concise.

Assuming your mixed text is in cell A2, enter the following formula:

=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1)*1, ""))

Step-by-Step Breakdown of the Formula

To fully understand why this formula works, let's peel back the layers starting from the inside out, using the sample text "A5B12" in cell A2.

• LEN(A2): This calculates the length of the string. For "A5B12", this returns 5.
• SEQUENCE(5): The SEQUENCE function generates an array of sequential numbers starting from 1 up to the length of the string. In this case, it generates the array: {1; 2; 3; 4; 5}.
• MID(A2, {1; 2; 3; 4; 5}, 1): The MID function extracts characters from text starting at a specific position. Because we fed it an array of starting positions from 1 to 5, it extracts 1 character at each of those positions. This breaks our text apart into an array of individual strings: {"A"; "5"; "B"; "1"; "2"}.
• *1 (Mathematical Coercion): We multiply each character in our array by 1. Excel attempts to convert text representations of numbers into actual mathematical numbers (e.g., "5" * 1 = 5). However, multiplying a non-numeric letter by 1 results in an error (e.g., "A" * 1 = #VALUE!). Our array now looks like this: {#VALUE!; 5; #VALUE!; 1; 2}.
• IFERROR(..., ""): This function sweeps through the array and replaces any error values (our non-numeric characters) with an empty text string (""). This leaves us with a clean, filtered array: {""; 5; ""; 1; 2}.
• TEXTJOIN("", TRUE, ...): Finally, TEXTJOIN binds these elements back together. The first argument is the delimiter (we use "" for no delimiter). The second argument is TRUE, which tells Excel to ignore empty values. TEXTJOIN skips the blanks and merges the numbers, resulting in the final text string: "512".

The Legacy Solution: Excel 2019

If you are using Excel 2019, you have access to TEXTJOIN, but you do not have the SEQUENCE function. To get around this limitation, you can generate your array of numbers using a classic combination of ROW and INDIRECT.

Use this formula instead:

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

Crucial Step: Because Excel 2019 does not feature native dynamic array behaviors, you must enter this as a traditional array formula. To do this, paste the formula into the cell and press Ctrl + Shift + Enter instead of just Enter. If done correctly, Excel will wrap your formula in curly braces: {=TEXTJOIN(...)}.

How ROW(INDIRECT(...)) works

This construction acts as a substitute for SEQUENCE. "1:"&LEN(A2) turns into the text string "1:5". The INDIRECT function converts this text string into a physical reference to rows 1 through 5. Lastly, the ROW function returns the row numbers of that reference as an array: {1; 2; 3; 4; 5}. The rest of the formula operates exactly like the modern version.

Converting the Result to an Actual Number

It is important to note that the output of TEXTJOIN is always classified as Text by Excel, even if it consists entirely of digits. If you plan on using the extracted numbers in mathematical equations, sums, or lookups, you should convert the text output into a real numeric value.

You can easily achieve this by prepending a double unary operator (two minus signs) to the front of your formula, or by multiplying the entire result by 1:

=--TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1)*1, ""))

The double negative coerces the final text string back into a standard Excel numeric value, allowing you to format it as currency, decimals, or standard integers.

Advanced Scenario: Retaining Decimals and Negative Signs

The standard extraction formulas shown above will strip away everything except raw digits. If your data contains decimal numbers (like "Total: $45.99") or negative values (like "Temp: -12C"), the standard formula will return "4599" or "12", respectively.

To extract numbers while preserving periods (decimals) and minus signs (negatives), we need to modify our logical check. Instead of forcing an error with *1, we can check each character against an approved list of characters using ISNUMBER and SEARCH.

Here is the advanced formula for Excel 365:

=TEXTJOIN("", TRUE, IF(ISNUMBER(SEARCH(MID(A2, SEQUENCE(LEN(A2)), 1), "0123456789.-")), MID(A2, SEQUENCE(LEN(A2)), 1), ""))

How this logic works:

• SEARCH(MID(...), "0123456789.-"): This looks at each individual character from cell A2 and searches for its position within the allowed characters string: "0123456789.-".
• If the character is a digit, a period, or a minus sign, SEARCH returns its position index (a number). If it is a letter, it returns a #VALUE! error.
• ISNUMBER(...): This converts the positions returned by the search into TRUE or FALSE.
• IF(..., MID(...), ""): If the character is one of our allowed characters (TRUE), we keep it. Otherwise, we replace it with an empty string ("").
• Finally, TEXTJOIN merges the permitted characters back together.

Alternative Solutions Comparison

While the TEXTJOIN + MID approach is highly flexible and dynamic, it is helpful to know how it compares to other common Excel tools for extracting numbers.

Conclusion

Extracting numbers from mixed text strings no longer requires long, intimidating VBA code blocks. By leveraging TEXTJOIN and MID, you can build clean, array-based formulas that dynamically handle messy data on the fly. Whether you are using the modern SEQUENCE function in Excel 365 or the tried-and-true ROW(INDIRECT(...)) fallback in older Excel versions, this formula is a vital technique to add to your data manipulation toolkit.