Practical Text Manipulation using MID LEFT RIGHT FIND Functions in MS Excel
Text manipulation is one of the great features of MS Excel. It has a lot of built-in functions to extract desired values from a text. By using different combinations of these functions we can create a complex formula. 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 I will use the LEFT and FIND functions to extract the Door number of an address. =LEFT(B4,FIND(" ",B4)) Here I am using the delimiter space (" "). The formula first finds a space character which appears after the door number. Then extracts the value whatever if at the left side of the first space. Extracting a ZIP code from an address Generally, the ZIP code will at the end of the address. Using the RIGHT function, we can extract the ZIP code =RIGHT(B9,5) ...