Using the REGEXEXTRACT and REGEXREPLACE functions #2 Some of these variations may be more intuitive to you and more flexible to work with than others… and so as you begin to understand how the formulas operate you can begin to customize them yourself. #1 You may run across multiple variations of these formulas on the internet, and you'll want to be familiar with them so you don't get confused. I have also included extra formulas that perform the exact same task, but are written differently. There are lots of examples included… and with many of the examples I have included several variations of the formulas that perform similar tasks with important differences. If you are wanting to learn each of these methods, take your time… as it may take multiple sessions to master this lesson on extracting.
If you are searching for a formula that performs a specific task, you might want to look for the one that does what you want and avoid the others, to avoid confusion. This article is very extensive, as there are many different ways to extract in Google Sheets. A note on formula versions in this article: If you expect to experience some of these error situations with your own data, where you may have a few rows/entries that do not have any matching data to extract, then you may choose how you would like to handle those errors for your specific needs... whether you decide to ignore them, or to handle them with the IFERROR function, or to cleanup your data so that the errors do not occur. This is why in the examples you will see some cells that display errors when some formulas are applied to certain strings/cells.īecause the same source data is used in each example there will be some formulas which are unable to extract the requested data because it is simply not present in that certain cell.ĭO NOT WORRY about these errors in the examples, simply use them as another opportunity to learn how the formulas react, and use the situation to better understand what type of data/string that the particular formula is meant to deal with. (If you are applying these formulas to an entire range or column, you can also use the ARRAYFORMULA function to apply these formulas across a whole range.) Again, this is so that you can see these formulas reacting in a variety of situations. Here are the raw data strings that we will be extracting from in many different ways during this lesson, in case you want to copy/paste this source data into your own sheet so that you can follow along with the examples and apply each formula on your own to see the result: 98g?3:74><89?!#$%^&67457įor EVERY formula discussed in the examples in this article, the formula is initially entered into cell C3, and is then copied/filled down through C12, so that the formulas are applied to the range C3:C12. Some of the strings contain only text… some contain only numbers… many of them contain a variety of punctuation, and some contain spaces. Because the same source data is used every time, this data contains a wide variety of character combinations in each row/entry, to assure that the many formulas used in this article can be understood/applied with the very same set of data. In this article I have used the exact same source data in every example, so that you can see how each of these extraction formulas reacts in a variety of situations, and also so that you can easily compare the subtle differences between similar formulas without the source/raw data changing each time. Watch the video below to walk through an example of each formula type.
#Hey google pick a number between 1 and 3 full
There are MANY MORE formulas that you will learn to use throughout this article, which you can find listed in full on your extraction cheat sheet.
N characters starting at the Nth CharacterĮxtract remaining characters starting at Nth character.The formulas for extracting in Google Sheets: