I have listed a few practical examples which are very useful.
Extracting a Door number from an address text.
|8314 W. Galvin Lane West Des Moines, IA 50265|
|68 4th Ave. Neptune, NJ 07753|
|8 Oklahoma St. Ladson, SC 29456|
Extracting a specific number from anywhere in the text
Suppose, from the below text I need to extract the dollar amount Which may be present anywhere in the text. For this, I will use multiple functions. For better understanding and for clear visibility, I have separated the functions. If you understand them clearly then you can create your own complex formula.
I had $100 in my pocket
bought 2 pens for $1 each
Spent $50 for snacks.
Now I have $48 left
Then identify the next
Extracting the names from a text
The below data has names mixed with some other text. The left part does not have any unwanted text. So we don't have to worry about the left part. But the right portion has other text and we want to remove that. The text marked in red are to be removed.
John Smit (IT Dept)
Krishh J (Sales)
Priore Priore (Software)
Lucy S (Sales)
It is very clear that we need to extract the text up to the first "(" symbol. This is can be done using a FIND and MID functions. Note the -1 at the end of the formula. This is to exclude the "(" character. Because the FIND function will return the position of the "(". But we don't need it.