How to extract numbers from a string in Excel

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 that manual method may be 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 the 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.