Cleaning up text in Excel sheets isn't always as simple as it should be. Especially, when all you want is a normal-looking sentence and not a mix of uppercase and lowercase chaos. Although there is no built-in function for sentence case in Excel, a properly written formula or macro can take care of it in seconds. Continue reading
Excel XLOOKUP and XMATCH are strong functions on their own, and regex match makes them even more versatile. Instead of searching for exact text, you can now use full-blown regular expressions to define what you are after. Continue reading
At first sight, Excel has everything you could ever need for text string manipulations. Hmm… what about regular expressions? Oops, there are no built-in Regex functions in Excel. But no one says we cannot create our own ones :) Continue reading
Whichever input data you are using, you'll hardly encounter a dataset without spaces. In most cases, whitespace is good. In some situations, however, it may become evil - extra spaces can mess up your formulas and make your worksheets almost unmanageable. Continue reading
Microsoft Excel provides a number of functions to extract text from cells. Those functions can cope with most of string extraction challenges in your worksheets. Most, but not all. When the Text functions stumble, regular expressions come to rescue. Continue reading
Have you ever thought how powerful Excel would be if someone could enrich its toolbox with regular expressions? We have not only thought but worked on it :) And now, you can add this wonderful RegEx function to your own workbooks and wipe out substrings matching a pattern in no time! Continue reading
Microsoft Excel provides a number of options to replace one piece of text with another. Why would one want to complicate things with regexes? Because Excel's standard features can only process an exact string that you specify. Continue reading
When it comes to restricting user input in Excel worksheets, Data Validation is indispensable. It can do anything you can possibly imagine. What if I want to allow entering only valid email addresses or strings that match a specific pattern? Alas, that's not possible. Regex you say? Hmm… that might work! Continue reading
When you need to find a certain value in a range of cells, you use the Excel MATCH function. When looking for a specific string in a cell, the FIND and SEARCH functions come in handy. And how do you know if a cell contains information that matches a given pattern? Obviously, by using regular expressions. Continue reading