How to extract text and number separately - Part I

It is quite irritating when we get non-formatted data. In absence of proper knowledge of Excel functions, it takes hell lot of time in formatting such data.
This video explains the concept of each functions used in the video.
FORMULA USED TO EXTRACT NUMBER AND TEXT FROM
TO EXTRACT NUMBER AND TEXT FROM TEXT+NUMBER
1. FIND THE POSITION OF DIGITS IN THE TEXT USING FIND() FUNCTION. We will search 0-9 in the text using Array under { bracket
2. The Lowest position shown is the position of the first digit
3. Using MIN() function will find the position of the first digit in the text
4. Using LEFT() function, we will extract the Text from the Number
5. Using RIGHT function we will extract Number from text.
=LEFT(C4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C4&"0123456789"))-1) =RIGHT(C4,LEN(C4)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},C4&"0123456789"))+1)