Data cleaning is one of the most common tasks when working with Excel. One such task is separating numbers from text. In this tutorial, we will learn how to do just that.
Consider the following example where we need to separate numbers from text when the number is at the end of text in column D. We will extract the text in column E using an array formula.
To start, type the formula “=FIND({0,1,2,3,4,5,6,7,8,9},C3)” in cell D3. This will find the digits in this cell from the cells C3.
Next, to avoid errors, cover this formula on the left by using the “IFERROR” function. Finally, enter “LEFT” on the left side of the string and type “-1” on the right side. Press Shift + Ctrl + Enter as this is an array formula.
Once done, it is easy to fetch the number. In column II, type the formula “=SUBSTITUTE(C3,D3,””)” in cell III. This will give you the result as a number.
You can now drag the formula in columns C and D till the end of the table to see the results. And that’s it! Separating numbers from text in Excel is that easy.
We hope you have found this tutorial helpful. If yes, please do leave us a like, share, and comment. For more interesting tutorials, subscribe to DB Tutorials. Thank you for watching.