String Manipulation using Microsoft Excel Text Functions

     Last month I had an interesting Twitter exchange with Microsoft Excel’s Social Media team. @MSExcel asked it’s followers to share an Excel function or formula that they felt most proud of mastering. Many responses included rather complex formulas involving a clever combination of different functions. Yet, my example simply listed Excel’s MID function. When asked why I used such a basic example I reiterated a personal belief that Excel’s text manipulation functions are greatly underused and consequently underrated. Surely, VLOOKUP function is probably the most in-demand Excel function, soon to be replaced with a much anticipated upgrade: XLOOKUP ; yet many Excel users simply overlook or don’t realize the power and usefulness of Excel’s text manipulation functions. Whether you need to combine multiple strings together, or reformat your case-sensitive text, or perhaps parse out text without using Text to Columns tool , or even remove unwanted text, or find precise position of a specific letter or word, or replace old values with the new ones, or calculate number of characters in your string; all of these tasks can easily be accomplished with the help of mighty Excel text functions. An icing on the cake is the fact that these functions are very easy to use as long as you’re aware of their existence. In this post we will cover following text functions: UPPER, LOWER, PROPER, LEN, LEFT, RIGHT, CONCATENATE and already mentioned MID Excel functions.
Continue reading