Extracting Decimal Numbers in Excel Using TEXTJOIN and ISNUMBER

Extracting scattered decimal values from mixed text strings in Excel is a notoriously tedious struggle for data analysts. While standard text-parsing functions like LEFT and MID offer baseline solutions, they often fail when data structures vary. Leveraging a dynamic array approach grants you the precision needed to isolate decimals instantly without complex VBA scripting. However, this advanced method stipulates that your Excel environment must support modern dynamic array behaviors. Applying this formula allows you to seamlessly extract target values, such as "45.5" or "102.75", from messy alphanumeric strings. Below, we break down how combining TEXTJOIN, MID, and ISNUMBER automates this process.

Extracting numerical data from raw text is one of the most common challenges faced by data analysts, accountants, and Excel power users. While extracting whole numbers (integers) can be relatively straightforward, extracting decimal numbers introduces a layer of complexity. Standard extraction techniques often strip away the decimal point, transforming a value like 10.50 into 1050, or fail altogether when text and numbers are tightly packed.

Fortunately, by combining the flexibility of TEXTJOIN, the logical checking power of ISNUMBER, and character-parsing functions like MID, ROW, and FIND, you can build a robust, dynamic formula to extract decimal numbers from any alphanumeric string. This guide will walk you through the logic, formulas, and advanced configurations to master this technique in both legacy Excel and Microsoft 365.

The Core Challenge of Decimal Extraction

To understand why a specialized formula is necessary, we must look at how Excel views characters. To Excel, the period (.) used as a decimal separator is text, not a number. If you use a standard extraction formula that filters exclusively for numeric digits (0–9), it will discard the decimal point.

For example, if you have the string "Total: $45.67 USD":

• A pure digit-extractor will yield 4567.
• An ideal decimal-extractor should yield 45.67.

To preserve the decimal structure, we must design a formula that recognizes both numeric digits and the regional decimal separator (usually a dot or a comma) while ignoring all other alphabetic and special characters.

The Ultimate Formula (Universal Excel Method)

The most robust way to extract decimal numbers in traditional Excel is by parsing the text string character by character, checking if each character is a digit or a decimal point, and then stitching the valid characters back together.

Here is the master formula to extract decimal numbers from cell A2:

=TRIM(TEXTJOIN("", TRUE, IF(ISNUMBER(FIND(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), "0123456789.")), MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), " ")))

Note: If you are using Excel 2019 or earlier, you must press Ctrl + Shift + Enter after typing this formula to enter it as an array formula. In Excel 2021 and Microsoft 365, you can simply press Enter.

How the Formula Works: A Step-by-Step Breakdown

This formula may look intimidating at first glance, but it operates as an elegant assembly line. Let's deconstruct it from the inside out using the sample text "Price: 12.5" (length of 11 characters) in cell A2.

Step 1: Generating Character Positions

ROW(INDIRECT("1:"&LEN(A2)))

The LEN function calculates the length of the string (11). INDIRECT creates a reference range of "1:11", and ROW returns an array of sequential numbers from 1 to 11: {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}. This acts as our index loop.

Step 2: Splitting the String into Individual Characters

MID(A2, {1; 2; ... ; 11}, 1)

The MID function takes each position index and extracts exactly one character. This converts our text string into an array of individual characters:
{"P"; "r"; "i"; "c"; "e"; ":"; " "; "1"; "2"; "."; "5"}

Step 3: Filtering for Digits and Decimals

FIND(MID(...), "0123456789.")

The FIND function attempts to locate each extracted character within the allowed string of numeric characters and the decimal point ("0123456789.").

• For "P", FIND cannot find it in "0123456789." and returns a #VALUE! error.
• For "1", FIND locates it at position 2 and returns 2.
• For ".", FIND locates it at position 11 and returns 11.

Step 4: Identifying Valid Numbers

ISNUMBER(FIND(...))

This wraps the previous step in a logical check. If FIND returned a position number, ISNUMBER evaluates to TRUE. If it returned an error, it evaluates to FALSE. This produces an array of logical values:
{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE}

Step 5: Isolating Valid Characters and Substituting Spaces

IF(ISNUMBER(...), MID(...), " ")

The IF statement keeps the original character if it matched our criteria (evaluated to TRUE). If it evaluated to FALSE, it replaces the character with a single space (" "). This results in:
{" "; " "; " "; " "; " "; " "; " "; "1"; "2"; "."; "5"}

Step 6: Stitching It Back Together

TEXTJOIN("", TRUE, ...)

The TEXTJOIN function concatenates all the characters in our array. By setting the first argument (delimiter) to empty quotation marks ("") and the second argument to TRUE (to ignore empty elements), it merges the characters. However, because we replaced non-digits with spaces, they are not ignored, resulting in:
" 12.5"

Step 7: Final Cleanup

TRIM(...)

The TRIM function strips away all leading, trailing, and duplicate spaces, leaving us with a perfectly clean, extracted decimal:
"12.5"

Handling Multiple Decimal Numbers in a Single Cell

One of the hidden superpowers of the formula above is its ability to handle strings that contain multiple decimal values. Because the formula replaces non-numeric characters with spaces, TRIM will automatically reduce any block of multiple consecutive spaces down to a single space.

If cell A2 contains:
"Specs: Width 10.5cm, Height 20.75cm"

The formula will output:
"10.5 20.75"

This makes it exceptionally easy to split those extracted values into separate columns afterward using Excel's built-in Text to Columns wizard or the modern TEXTSPLIT function.

The Modern Microsoft 365 Method (Using LET and SEQUENCE)

If you are using Microsoft 365, you can avoid the older, volatile INDIRECT function and write a much cleaner, faster, and easier-to-read formula using LET, SEQUENCE, and MAP.

=LET(
    text, A2,
    chars, MID(text, SEQUENCE(LEN(text)), 1),
    valid_chars, "0123456789.",
    cleaned, MAP(chars, LAMBDA(c, IF(ISNUMBER(FIND(c, valid_chars)), c, " "))),
    TRIM(CONCAT(cleaned))
)

Why the Modern Formula is Better:

• No INDIRECT: INDIRECT is a volatile function that recalculates every time any change is made to the workbook, which can slow down large spreadsheets. SEQUENCE runs natively and quickly.
• Highly Readable: By assigning variables (like text, chars, and valid_chars), anyone reviewing your sheet can easily understand the formula's structural flow.
• Easy Customization: If your region uses commas as decimal points, you only need to change "0123456789." to "0123456789," in one obvious place.

Managing Edge Cases & Regional Formatting

Real-world data is messy, and no single formula fits every scenario without minor adjustments. Here is how to adapt your decimal extraction formula for common edge cases:

1. European & South American Decimal Commas

If your version of Excel is configured to use a comma (,) as a decimal separator (e.g., 12,50 €), simply swap out the period in the validation string of either formula:

• Change "0123456789." to "0123456789,"

2. Handling Trailing Periods (Sentences)

If your string ends with a grammatical period (e.g., "The price is 15.99."), the formula will extract "15.99." because it treats the final period as a decimal.

To fix this, you can run a pre-cleanup step using SUBSTITUTE to remove grammatical periods at the very end of sentences, or wrap the extraction output inside a cleaning function that drops trailing non-digits.

3. Retaining Negative Decimals

If your financial data contains negative decimals (e.g., "Adjustment: -4.50"), you must allow the minus sign (-) to pass through your filter. Simply add the minus sign to your allowed character string:

• Change "0123456789." to "0123456789.-"

Summary of Extraction Logic

Conclusion

By leveraging TEXTJOIN, ISNUMBER, and character mapping, you can easily pull complex decimal numbers out of raw, unformatted text strings. Whether you stick with the classic array formula for backward compatibility or transition to the streamlined Microsoft 365 LET structure, you now have a reliable toolkit to sanitize and normalize your Excel datasets with confidence.