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.
Table of contents:
- Extract a number from the left of a string
- Extract a number from the end of a text string
- Get the nth number from a text string
- Extract all numbers from a cell that contains text
Sometimes you need to extract a number part of a text string which is almost impossible using the built-in Excel text-related functions. Furthermore, if you need to get a number from a cell and a given string contains a number, not a joyride.
How to extract a number from the beginning of a text string
- Use the GetFirstNumber function.
- Add the input cell that contains the string.
- The function will return the first number (numeric value) from the left.
The GetFirstNumber function uses a regular expression to search for sequences of numeric characters in the string and returns the first one found.
Example:
In the example, cell B3 contains a text string. To extract the first number, use the formula below:
=GetFirstNumber(B3)The result is 234; the function can extract a number even if it is part of a text value.
If you want to extract a number from the beginning of a text string using built-in Excel functions:
You can implement the code below by inserting the code into a new module. To do that, press the Alt + F11 keys to open the VBA editor. Paste the code and save the Workbook in an xlsm format.
Function GetFirstNumber(str As String, Optional delim As String = ", ")
Dim n As Long, nums() As Variant
Static ob1 As Object, ob2 As Object
If ob1 Is Nothing Then
Set ob1 = CreateObject("VBScript.RegExp")
End If
With ob1
.Global = True
.MultiLine = False
.Pattern = "[0-9]{1,50}"
If .Test(str) Then
Set ob2 = .Execute(str)
ReDim nums(ob2.count - 1)
For n = LBound(nums) To UBound(nums)
nums(n) = ob2.Item(n)
Next n
If UBound(nums) >= 0 Then
GetFirstNumber = nums(0)
End If
End If
End With
End FunctionHow to extract number from the end of a text string
- Use the GetLastNumber function.
- Add the input cell that contains the string.
- The function will return the last number (numeric value) from the right of the string.
The GetLastNumber function finds all numbers in the input string and returns the last one.
Example:
In this case, you want to extract the last number from cell B3.
Formula:
=GetLastNumber(B3)The formula returns 555, which is the last number in the text.
Get the nth number from a text string
Here are the steps to using the GetNthNumber function to extract the nth number from a given cell.
- Type the GetNthNumber function.
- Add the input cell (first argument) that contains the string.
- Enter an integer value (nth) as the second argument.
- The function will return the nth number from the entire text string.
Example:
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:
=GetNthNumber(B3, 2)The second argument is 2; the formula will return 54.
Extract all numbers from a text string
If you want to extract all numbers from a text string, use the GETNUMBERS function. The function extracts all numbers from a text string and writes the numbers in a comma-separated list.
Usage:
- Type the GETNUMBERS function.
- Add the input cell (first argument) that contains the string.
- Choose your preferred separator as the second argument.
- The function will return all numbers using a comma-separated list from the entire text string.
Example:
Final words
We strongly recommend you use UDFs when you face complex tasks in Excel. Regular functions have some frustrating limitations.
Additional resources:
