Get a number from any position in a string

Get a number from any position in a string

To get a number from any position in a string, use a formula based on the TEXTJOIN, TRUE, IFERROR, MID, ROW, INDIRECT and LEN functions.

This article is a part of our definitive guide on Excel formulas.

How to extract numbers from specific position in a string

Steps to extract all numbers from any position in a string:

  1. Select cell E3.
  2. Type =TEXTJOIN(“”,TRUE,IFERROR((MID(C3,ROW(INDIRECT(“1:”&LEN(C3))),1)*1),””))
  3. Press Enter.
  4. The formula returns all numbers from any position in a string.

Example

In this example, we have a dataset in column C containing text strings that include phone numbers embedded within other text, such as names, notes, or additional characters. The goal is to extract only the numeric digits of the phone numbers from these strings, effectively removing any non-numeric characters.

Data cleaning is useful in scenarios where raw data from unstructured sources (e.g., logs, messages, or notes) needs to be cleaned and standardized for further processing, such as feeding into a system that requires uniform phone number formats.

Formula:

=TEXTJOIN(“”,TRUE,IFERROR((MID(C3,ROW(INDIRECT(“1:”&LEN(C3))),1)*1),””))

Get a number from any position in a string

The formula in cell E3 pulls the TEXTJOIN function along with MID, ROW, INDIRECT, and IFERROR to isolate and concatenate the numeric digits, ensuring structured output in column E.

Explanation

Now, evaluate the formula from the inside out:

LEN(C3): The LEN function calculates the total number of characters in the text string located in cell C3. For example, if C3 contains “John (383) #call 254-2392”, LEN(C3) returns 27 since there are 27 characters in the string (including spaces, parentheses, and special symbols).

ROW(INDIRECT(“1:”&LEN(C3))): ROW with INDIRECT generates an array of row numbers corresponding to the length of the string in C3. INDIRECT(“1:”&LEN(C3)) creates a text string “1:27”, which represents the range of numbers from 1 to 27. ROW converts this range into an array: {1; 2; 3; …; 27}. This array represents the position of each character in C3.

MID(C3, ROW(…), 1): The MID function extracts individual characters from C3 based on their positions (as provided by the array from the ROW function). For example, if C3 is “John (383) #call 254-2392”, MID(C3, ROW(…), 1) returns an array like this: {“J”; “o”; “h”; “n”; ” “; “(“; “3”; “8”; “3”; “)”; ” “; “#”; “c”; …}.

MID(C3, ROW(…), 1)*1 multiplying the characters by 1 attempts to convert them into numeric values. If the character is numeric (“3”, “8”, etc.), it successfully converts to numbers. If the character is non-numeric (e.g., “J”, “(“, “#”, etc.), it throws a #VALUE! error.

IFERROR(…, “”): IFERROR traps the #VALUE! errors resulting from non-numeric characters and replaces them with an empty string (“”). This step ensures that only numeric values remain in the array.

TEXTJOIN(“”, TRUE, …): TEXTJOIN combines all the numeric values in the array into a single string without any separators (“”). The TRUE argument tells TEXTJOIN to ignore any empty strings (produced by non-numeric characters).

The formula extracts all numeric characters from the text in C3 and concatenates them into a single string. For example, if C3 = “John (383) #call 254-2392”, the steps would result in: “3832542392”.

Using the SEQUENCE function

In this formula, we use the double negative (–) method, which may be slightly clearer. The main advantage is that it directly handles numeric validation with ISNUMBER, ensuring non-numeric characters are explicitly ignored.

Formula:

=TEXTJOIN(“”, TRUE, FILTER(MID(C3, SEQUENCE(LEN(C3)), 1), ISNUMBER(–MID(C3, SEQUENCE(LEN(C3)), 1))))

The result is the same as the previous example.

SEQUENCE generates a sequence of numbers from 1 to the length of the string in C3. For C3 = “A1B2C3”, this produces {1; 2; 3; 4; 5; 6}, represent the position of each character in the string.

Using the SEQUENCE function to extract numbers

MID(C3, SEQUENCE(LEN(C3)), 1) extracts one character at a time from C3 based on the sequence of positions.

–MID(C3, SEQUENCE(LEN(C3)), 1) converts the extracted characters to numbers where possible. The double negative method (–) forces Excel to interpret text as numeric if it represents a number. Non-numeric characters are coerced into #VALUE!.

To find all numbers from a list and create a comma-separated list, you can use the GETNUMBER function. GETNUMBER is a part of our function library.

Download the Workbook.