The applications of this formula are infinite when you think that you can combine it with the functions you use every day. One additional thing to note is that Regex differentiates for capitalised letters, which can be quite handy sometimes. The formula tells Regex to look for the word blue or green or red and so on.Īgain, it doesn’t matter where the word is within the string. Here you probably noticed the use of pipe “|” which stands for “or”.
=REGEXEXTRACT(A3,”.*(blue|green|red|purple|blonde).*”) This is a Regex example of what the formula would become if you are looking for multiple values: So “great” could be anywhere in the URL, Regex will find it: * before and after the word “great” tells Regex not to worry what precedes or follows the string “great”. The “text” part is the cell where you have something to look for, and the “regular_expression” is the expression telling Regex what to look for. When you type in “=REGEXEXTRACT” in Google Sheets this is what the function requires: Let’s say I needed to extract the word “great” from the link “”. Let me get you started with a really simple Regex example. I don’t have time for this and nor should you. Without Regexextract, I’d have to apply multiple filters and custom formula for each URL, extracting the word based on its position. However, I had many words I was looking for, and each URL could have included any of them and in a different location within the URL. For example, from the list of URLs below let’s suppose I had to extract the location: For one of my clients, I had a list of URLs and I had to extract certain words within each. This is the function you’d want to use when you are digging out a certain string within a longer one.
Regex in custom formatting excel upper case letter how to#
There are already plenty of comprehensive guides and resources out there so if you want to learn more Regex, here is a useful Regex guide and this is a really good debugging tool.īelow I’m going to dive right into Regex formulas for Google Sheets and examples on how to use them and what you can achieve with each one. Since my purpose here is to demonstrate how helpful these functions are, I won’t go too much in deep into all the Regex syntax rules. They perform exactly what they say: extract, replace, and match. The 3 main Regex formulas you can use on Google Sheets are:
That’s right, I’m all about learning tools to make my life easier and lately Regex is my favourite. The good news is that you can use Regex on Google Sheets to work with your data, making your life beyond easy. You may be thinking “oh no, another complex programming language I don’t understand that complicates things even more”. It can be used on many platforms, such as Google Analytics, Python, Java, and more, to perform searches of specific strings. Regex is a matching expression tool returning specific values, for example, true or false, whether it finds the expression or not. Thanks to a recent outstanding presentation delivered by Robin Lord during a digital marketing Meetup event at Brainlabs, I was introduced to the world of Regex. They’ll do the work, but the time it takes to set them up makes me question whether I am actually working faster. Not only are they fundamental for my survival as an SEO analyst, But sometimes, these functions are just not good enough. On a regular basis, either in Google Sheets or Excel, I use formulas such as vlookup, index, iferror, and the list goes on, to sort quickly through endless data. Handling big sets of data to analyse is quite normal if you work in SEO.
0 kommentar(er)
