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.

This article will show you the real power of formulas based on user-defined functions. Instead of using complex formulas that contain built-in functions, you’ll use simple but effective solutions. In Excel, you can use specific substring functions (LEFT, RIGHT, and MID) to extract a part of text from a text string. However, there is no built-in function to extract numbers from strings containing letters and numbers.

When you want to get a number from a cell and a given string contains a number, not a joyride. Sometimes, you need to extract a number part of a text string, which is almost impossible using the built-in Excel text-related functions. But do not worry; Excel has some tricks to make this easier. This step-by-step tutorial will show straightforward formulas to extract numbers from the end of texts, making your data work simpler and faster. Let’s get started!

Table of contents:

  1. Extract a number from the right of a string
  2. Extract the last number from a cell using the GETNUMBERS function
  3. Get numbers using regular Excel functions
  4. Extract a number from the beginning of a text string
  5. Get a number from any position in a string
  6. Extract the nth number from a text string
  7. Extract all numbers from a text string

How to extract number from the end of text string

In the first example, you want to extract a number from the end of a text string. Suppose you have a cell containing a string like “Example123” and want to extract the “123”.

To perform the task, combine the RIGHT and LEN functions. The Excel RIGHT function extracts a specified number of characters from the right end of a string. The LEN function provides the length of the string.

How to extract numbers from the end of a string?

  1. Select cell B6
  2. Type the formula: =RIGHT(B3, 3)
  3. Press Enter.
  4. Cell B6 should now display “123”, extracted from “Example123”.
using RIGHT function

This formula takes the content of cell B3 and retrieves the three characters from the end of that string.

Explanation: The RIGHT function extracts a certain number of characters from the end of a string. The syntax is simple: specify the cell containing the text and the number of characters you want to extract.

Note: In this basic example, we do not use the LEN function, but it can calculate the total number of characters in a string. The LEN function deals with varying amounts of text or numbers or when you need dynamic calculations based on the content length. This method works great when you know the exact number of characters you want to extract from your text. However, additional functions are necessary for more complex scenarios to extract the numbers regardless of text length or number size.

Extract the last number from a cell using the GETNUMBERS function

The GETNUMBERS function finds all numbers in the input string and returns the last one. It is good to know that the function is not available in Excel. Install the DataFX function library and speed up your work.

Steps to extract the last number from a cell in Excel:

  1. Select cell D3
  2. Type the =GETNUMBERS(B3)
  3. Press Enter
  4. This part will return with a comma-separated list containing numbers only.
  5. Apply the =INDEX(TEXTSPLIT(D3,”,”),COUNTA(TEXTSPLIT(D3,”,”))) formula for the range.
  6. The formula will return the last number (numeric value) from the right of the string.
Extract the last number from a cell using the GETNUMBERS function

Explanation:

  1. GETNUMBERS extracts only the numbers from cell B3. In this case, B3 contains the text “ASQ, 34,54,555,#-space”. The function removes any non-numeric characters and produces a comma-separated list of numbers.
  2. INDEX(TEXTSPLIT(D3, “,”), COUNTA(TEXTSPLIT(D3, “,”))): This formula is used to extract the last number from the comma-separated list in D3.
  3. COUNTA(TEXTSPLIT(D3, “,”)): COUNTA counts the number of non-empty elements in the array produced by TEXTSPLIT. In this case, it counts three items (“34”, “54”, and “555”), so it returns 3.
  4. INDEX(TEXTSPLIT(D3, “,”), COUNTA(TEXTSPLIT(D3, “,”))): INDEX returns a specific item from the array. Since COUNTA gives 3, INDEX returns the third item in the array, “555”.

Get numbers from the end of the text string

In the example, the text in cell B3 is “10-ABC-341”, and you want to extract the number from the right. We write a formula that dynamically adjusts to each unique string and handles various text inputs. Extract numbers from a cell no matter how many text characters are in the middle.

How to get numbers from the end of a text?

  1. Select cell C3
  2. Type the formula: =RIGHT(B3, LEN(B3) – MAX(IF(ISNUMBER(MID(B3, ROW(INDIRECT(“1:”&LEN(B3))), 1) * 1) = FALSE, ROW(INDIRECT(“1:”&LEN(B3))), 0)))
  3. Press Enter
  4. The formula returns numbers from the end of a text.
Extract-numbers-from-the-end-of-the-text-string-Advanced

Evaluate the formula from the inside out:

  1. =RIGHT(B3, …): This part of the formula uses the RIGHT function to extract a certain number of characters from the end (right side) of the text in B3.
  2. =LEN(B3): The LEN function returns the total number of characters in B3.
  3. =MID(B3, ROW(INDIRECT(“1:”&LEN(B3))), 1): The MID function extracts characters from the middle of the string based on a specified position and length. However, in this formula, we use an array context to extract each character of B3. The ROW(INDIRECT(“1:”&LEN(B3))) creates an array of numbers from 1 to the length of B2 (like {1;2;3;…}), creating a number for each character position in the string. Finally, MID extracts each character in B2.
  4. =ISNUMBER(… * 1) = FALSE: Each character extracted by MID is multiplied by 1 (* 1). If the character is a number, the multiplication will be successful, and ISNUMBER return TRUE. If the character is not a number, the multiplication will fail, resulting in an error, and ISNUMBER will return FALSE.
  5. =IF(ISNUMBER(…) = FALSE, ROW(INDIRECT(“1:”&LEN(B3))), 0): The IF function checks whether each character is not a number. If the character is not a number (FALSE), it returns the position of that character within B3. This way, it creates an array of positions for non-numeric characters. If the character is a number (TRUE), it returns 0.
  6. MAX(IF(…)): The function takes the array and returns the largest value, corresponding to the position of the last non-numeric character in B3.
  7. LEN(B3) – MAX(IF(…)): This part subtracts the position of the last non-numeric character from the total length of the string. The result is the number of numeric characters at the end of the string in B3.
  8. RIGHT(B3, LEN(B3) – MAX(IF(…))): Finally, the expression tells RIGHT how many characters to extract from the end of the string in B3.

The formula will return “341” from “10-ABC-341” because it dynamically identifies “341” as the numeric character at the end of the specific string.

How to extract a number from the beginning of a text string

To extract a number (or numbers) from the beginning of a text string in Excel, we need a formula to determine where numbers end and text begins within a string. In the example, you have a string in cell B2, such as “1234ExampleText.” Let us see how to extract the “1234” from the beginning.

Steps to extract number from the beginning of a text:

  1. Select cell B6
  2. Type the formula: =LEFT(B3, MATCH(FALSE, ISNUMBER(MID(B3, ROW(INDIRECT(“1:” & LEN(B3)+1)),1)*1), 0)–1)
  3. Press Enter
  4. The formula returns numbers from the end of a text.
How-to-extract-number-from-the-beginning-of-a-text-string

Evaluate the formula:

  1. LEFT(B3, …) grabs a portion of the text from the left side of the string in B3. The result of the formula within the parentheses determines the number of characters it retrieves.
  2. MATCH(FALSE, … , 0) is looking for the first instance of FALSE within an array that the ISNUMBER(MID(…)) function creates. This part identifies where the sequence of numbers ends by locating the first non-numeric character.
  3. ISNUMBER(MID(B3, ROW(INDIRECT(“1:” & LEN(B3) + 1)), 1) * 1) generates an array of values from the string, converting characters to numbers where possible and causing errors for non-number characters. The ISNUMBER function creates a TRUE/FALSE array depending on whether elements are numbers.
  4. ROW(INDIRECT(“1:” & LEN(B3) + 1)) is a sequence of numbers from 1 to one more than the length of your string, and check each character in the text individually.
  5. The “* 1” operation forces any text characters to be represented as errors because they can not be multiplied as numbers, helping ISNUMBER create the needed TRUE/FALSE array.
  6. The – 1 at the end adjusts the character count returned by MATCH since MATCH would otherwise include the first non-number character.

Using this method, we can see it extracts all numeric characters found at the start of the string before reaching any text.

How to get a number from any position in a string

Retrieving numbers from any position within a text string in Excel requires a formula that can sift through each character, identify numbers, and then extract them, regardless of their position. This process involves a combination of several functions. Below is a step-by-step guide on how to accomplish this task.

In the example, you have a mixed string like “123abc456def” in cell B2 and want to extract all the numbers (123456) from it, regardless of their position.

Steps to extract a number from any position of a string:

  1. Select cell B6
  2. Type the formula: =TEXTJOIN(“”, TRUE, IFERROR(MID(B3, ROW(INDIRECT(“1:” & LEN(B3))), 1) * 1, “”))
  3. Press Enter (or Ctrl + Shift + Enter if required)
  4. The formula returns numbers from any position in a string.

Note: Ctrl + Shift + Enter is required if you are not using Microsoft 365 or Excel 2019

any positions

Explanation:

  • TEXTJOIN(“”, TRUE, …): This function joins each element of the resulting array into one continuous text string without a delimiter.
  • IFERROR(…, “”): It checks if any error arises from the operations (which occurs when the operation encounters text instead of numbers). If there is an error (a text character), it replaces the error with an empty string (“”).
  • MID(B3, ROW(INDIRECT(“1:” & LEN(B3))), 1): This part of the formula creates an array that holds each character from the original string. It splits the text from B3 into separate characters.
  • *1: The multiplication tries to convert text to numbers. Non-numeric characters result in an error, which triggers the IFERROR function.
  • ROW(INDIRECT(“1:” & LEN(B3))) creates an array of numbers from 1 to the length of the string. We use it to parse each character in the string sequentially.

Excel extracts numbers from any part of the text, effectively ignoring non-numeric characters. It is an advanced method for mixed-format data strings.

Get the nth number from a text string

Our goal is to find the second number in a string! Using regular Excel functions, the formula can be complex. So, let us see an elegant way to get the nth number from a text.

Here are the steps to using the GETNTHNUMBER function to extract the nth number from a given cell.

  1. Type the GETNTHNUMBER function.
  2. Add the input cell (first argument) that contains the string.
  3. Enter an integer value (nth) as the second argument.
  4. The function will return the nth number from the entire text string.
Get the nth number from a text string

Extract all numbers from a text string

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.

How to extract all numbers from a text string?

  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-comma-separated-list

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.

Final words

In summary, learning to extract numbers from a text in Excel makes working with data much easier. This skill is helpful in many different situations. With simple functions like LEFT, RIGHT, MID, custom formulas, or UDFs, anyone can quickly extract the numbers they need from any text. As you can see, regular functions have some frustrating limitations. So, using these tips, anyone can get ready to handle string manipulation tasks without trouble.

Download the practice file.