How to extract number from string in Excel

How to extract number from string in Excel

Learn how to extract a number from a text string in Excel (from the left, right, or any position) using custom formulas and functions.

In Excel, you can use specific substring functions (LEFT, RIGHT, and MID) to extract a part of text from a text string. However, now we have modern Excel functions to write effective formulas and extract numbers from strings containing letters and numbers.

How to extract numbers from a text string?

Here are the steps to extract only numerical values from string Excel:

  1. Select cell D3.
  2. Type =TEXTJOIN(“, “, TRUE, TOROW(TEXTSPLIT(B3, {” “,”,”})+0, 2))
  3. Press Enter.
  4. The formula returns a list of numbers.

Example

In this example, we have a dataset in column B containing mixed text and numerical values separated by commas, spaces, and text elements. The goal is to extract only numerical values from string Excel and present them as a clean, comma-separated list in column D.

Formula:

=TEXTJOIN(“, “, TRUE, TOROW(TEXTSPLIT(B3, {” “,”,”})+0, 2))

How to extract number from string in Excel

The formula in cell D3 uses the TEXTSPLIT function to split the text into components, converts potential numbers to numeric values, and filters out any non-numeric elements. Finally, it extracts all numeric values from the text in B3, ignores non-numeric values, and combines the numeric values into a single comma-separated string.

Explanation

Evaluate the formula:

TEXTSPLIT(B3, {” “,”,”}): This part splits the text in cell B3 into multiple pieces based on delimiters. The delimiters are {” “, “,”}, meaning it splits wherever there is a space (” “) or a comma (,). You can easily append the array if you have another delimiters in the cell. The formula returns an array of the parts of the text in B3, like {34; 54; 555; ADS; abc; 23}.

TEXTSPLIT(B3, {” “,”,”}) + 0: Adds 0 to each item in the array from TEXTSPLIT. This is a trick to coerce numeric text values (e.g., “34”) into actual numbers, while non-numeric values (e.g., “ADS”) are converted to errors. The array becomes {34; 54; 555; #VALUE!; #VALUE!; 23}. Numbers remain as numbers, while non-numeric values are replaced by #VALUE!.

TOROW(…, 2): Converts the array into a single row or column. The second argument (2) ensures errors (like #VALUE!) are ignored. The formula returns a cleaned array containing only the numeric values: {34, 54, 555, 23}.

TEXTJOIN(“, “, TRUE, …) joins the elements of the array into a single text string. Delimiter: “, “. This places a comma and a space between each number. The TRUE argument ensures blank cells (if any) are ignored. The final result is “34, 54, 555, 23”.

Extract all numbers from a text using GETNUMBERS

If you like advanced Excel formulas, we will show you how to extract all numbers from a text string. The GETNUMBERS function is a VBA script designed to extract all numeric sequences from a given text string and return them as a single string, separated by a specified delimiter.

  1. Click cell B6
  2. Type the formula: GETNUMBERS(B3)
  3. Press Enter
  4. The formula returns all numbers using a comma-separated list from the entire text string.

Example:

Extract all numbers from a text using GETNUMBERS

Regardless of their position, the function efficiently scrapes all numeric sequences from the provided text. The result is comma-separated so that you can work with the output easily.

Download the practice file.