How to extract numbers from a string in Excel

Excel sheets with records that have data involving codes, bank accounts, and postal addresses may have cells with a mixture of letters and numbers. The mixture of letters and numbers is what we refer to as a string in programming. In the case where we would like to place the numbers in a different cell other than the original one. We need to extract them from the string value.

Extraction or extracting is the process or activity of separating or pulling out values from a given data set. To extract a number from a string is, therefore, the general activity of removing the number from the string holding it.

There are a couple of methods and ways to achieve the extraction, some include the use of built-in formulas in excel while others include the use of manual extraction. All methods work well only the manual method may be more inaccurate and error-prone than the use of the built-in formulas.

To extract numbers from a given string using built-in formulas we follow some of the steps which are discussed below in length.

Step 1

Browse for the excel sheet with the string data and open it. In case you do not have the data already set up, create a new excel sheet, and insert some string values into it. You can have your data contain a cell without a string to check the effectiveness of the formula.

Step 2

To separate the numbers from the string value, we will use a formula. As said earlier, this method will include the use of the formula. The formula will be written in the formula bar or result bar.

The formula is; =RIGHT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},"")))).

There are four functions used in this formula, we have RIGHT, SUM, LEN, and SUBSTITUTE functions. The LEN function deals with the length of the string, SUBSTITUTE functions separate the numbers from the string, and finally, the RIGHT function specifies the side to separate.

From the above scenario, cell A5 is made of letters alone without the numbers, therefore, it will return an empty row when subjected to the formula.

Example 2

1. Assuming you have numbers at the beginning like the data below

2. Open Microsoft visual basic editor by pressing Alt+f11

Click on insert >> new module and copy paste the following code

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby Extendoffice
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
xStr = VBA.Mid(pWorkRng.Value, i, 1)
If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
SplitText = SplitText + xStr
End If
Next
End Function

3. Save the code to the sheet by pressing ctrl+s. Or by clicking file then save

4. Enter the following formula in a blank cell to split the text from numbers =SplitText(A3,FALSE). Make sure to press the enter key.

5. Drag the fill handle to the rest of the cells and they will be separated automatically

6. Enter the following formula in another blank cell to separate the numbers

7. Drag the formula to the remaining cells and they will be separated too