extract number from string excel

Guri Bee

3 min read

·

19-10-2024

1

0

Share

Extracting Numbers from Strings in Excel: A Comprehensive Guide

Extracting numbers from strings in Excel is a common task, especially when working with data imported from other sources. This process can be cumbersome if done manually, but luckily, Excel offers several powerful functions and formulas to automate this task. This article explores various methods for extracting numbers from strings in Excel, providing clear explanations, practical examples, and additional tips for efficient data manipulation.

Understanding the Challenge

Imagine you have a list of data in an Excel column that looks like this:

You need to extract the numbers (12345, 98765, 54321) from these strings for further calculations or analysis. Manually typing these numbers is not feasible for large datasets, hence the need for automated solutions.

Methods for Extracting Numbers

Here are some common methods for extracting numbers from strings in Excel:

1. Using the TEXTJOIN Function (Excel 2016 and later)

This method is efficient and versatile. It allows you to extract numbers by concatenating only numeric characters from the string.

Example:

Suppose your data is in cell A1: "Order #12345". You can use the following formula:

=TEXTJOIN("",TRUE,IF(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))

Explanation:

• TEXTJOIN combines the extracted numeric characters into a single string.
• ISNUMBER(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))) checks if each character in the string is a number.
• MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) extracts each character from the string.
• If the character is a number, it is included in the TEXTJOIN function; otherwise, an empty string is included.

2. Using the FIND and REPLACE Functions

This method is useful when you know the specific characters or patterns surrounding the numbers in your strings.

Example:

If your data is in cell A1: "Product Code: 98765", you can use this formula:

=REPLACE(A1,1,FIND(":",A1),"")

Explanation:

• FIND(":",A1) finds the position of the colon (":") in the string.
• REPLACE(A1,1,FIND(":",A1),"") replaces everything before the colon with an empty string, leaving only the numeric part.

3. Using the FILTERXML Function (Excel 2013 and later)

This method allows you to extract numbers from strings with a specific format using XML-like syntax.

Example:

If your data is in cell A1: "Item 54321 (SKU)", you can use this formula:

=FILTERXML("<t><s>" & SUBSTITUTE(A1," ","</s><s>") & "</s></t>","//s[not(contains(.,''))]")

Explanation:

• SUBSTITUTE(A1," ","</s><s>") replaces spaces in the string with "" to create a structure resembling XML.
• FILTERXML("<t><s>" & ... & "</s></t>","//s[not(contains(.,''))]") extracts text nodes that don't contain any characters.

Function ExtractNumbers(str As String) As String
    Dim i As Long
    For i = 1 To Len(str)
        If IsNumeric(Mid(str, i, 1)) Then
            ExtractNumbers = ExtractNumbers & Mid(str, i, 1)
        End If
    Next i
End Function