How to extract numbers from string in excel?
This article will help you with the approach and give you a clarity of logic that you can use in excel function to extract numbers.
Let us understand different types of data that we can have. Predominantly, there are three different scenarios.
a) Numbers at the end (right side) of the string. For example, “Score is 537”
b) Numbers in the beginning (left side) of the string. For example, “1234 is the code”
c) Numbers anywhere in the string. For example, “You scored 85 in the last match”
If you are using the MS excel 2016 or later version then you can use the TEXTJOIN function. But in the earlier versions TEXTJOIN function was not available. So we have to use a combined function including SUBSTITUTE, LEFT, MIN, IFERROR, FIND, and MAX functions.
a) Formula to extract numbers when it is at the end (right side) of the string.
=SUBSTITUTE(A2,LEFT(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),””))-1),””)
Press CTRL + Shift + Enter after typing the formula.
Let’s understand the logic behind the formula.
FIND({0,1,2,3,4,5,6,7,8,9},A2)
It finds the presence of numeric values and prints the position if a number is present. If the value at a given position is text the it returns #VALUE!
FIND({0,1,2,3,4,5,6,7,8,9},A2) will return
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 6 7 8 9
Here,
#VALUE! indicates the presence of Text data at 1st, 2nd, 3rd, 4th, and 5th position.
6 7 8 9 indicates the presence of numeric values at 6th, 7th, 8th, and 9th position
IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),””)
IFERROR function will replace #VALUE! Errors with blank i.e. “”
So the output of this function will be 6 7 8 9. From this output it gets that numbers are available from 6th to 9th position.
MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),””))
Using the MIN function here will find the starting position of numeric values. So it will return the position 6.
LEFT(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),””))-1)
With the LEFT function we can extract “apple” from A2. If you decode this function it will process like this
LEFT(A2,5)
We are getting this 5 from the MIN function in the above step and then we have a -1 at the end to find the last position of the string. That in turn results in the length of the string.
=SUBSTITUTE(A2,LEFT(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),””))-1),””)
SUBSTITUTE function will replace the string (text values) with blanks and only numbers will remain as an output.
So, if you have understood the formula then you can try it to extract the numbers if they are at the end of a string.
b) Formula to extract numbers in the beginning (left side) of the string.
=SUBSTITUTE(A2,RIGHT(A2,LEN(A2)-MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),””))),””)
Press CTRL + Shift + Enter after typing the formula.
Let’s understand the logic behind the formula
FIND({0,1,2,3,4,5,6,7,8,9},A2)
This will return 1 2 3 4 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
Here 1 2 3 4 are the positions of numeric values and #VALUE! is the value error wherever text values are there. In this example the text value is “apple” that has 5 characters and hence there are five #VALUE!
IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),””)
IFERROR function will replace #VALUE! Errors with blanks.
MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),””))
MAX function will find the position of the last numeric value. So it will return the position 4 in this case.
LEN(A2)-MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),””)))
Subtracting the position of the last number (that we got 4) from the length of the string (i.e. 9) we will get the number of characters in the text data. So it will return 5 (that is the length of apple in this example).
=SUBSTITUTE(A2,RIGHT(A2,LEN(A2)-MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),””))),””)
SUBSTITUTE function will replace the string (texts) with blanks. And only numbers will remain as a final output,
c) Formula to extract number that is available anywhere in the string
TEXTJOIN function was introduced in excel 2016.Hence you can use this function in excel 2016 and later version only.
